Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 79 ยท SQL for Database Courses

Nested Queries, Views and Materialization

Correlated subqueries, EXISTS, IN, ANY and ALL, views as virtual tables, updatable views and INSTEAD OF triggers, and materialized views explained as a concept.

Some questions cannot be answered with a single flat SELECT. "Which employees earn more than the average of their own department?" needs a second query whose answer feeds the first. That is a nested query (a subquery). When a query becomes useful enough to reuse, you give it a name and store it as a view: a virtual table defined by a query. A view that is also stored on disk is a materialized view. This lesson covers all three, with the semantics a database course expects you to know.

For syntax details, see Subqueries, Correlated Subqueries, EXISTS and Views in the SQL tutorial. Here the emphasis is on what each form means and how the forms relate.

Subqueries by shape

Scalar Returns one value.
Column Returns a list, used with IN, ANY or ALL.
Table Used in FROM like a temporary table.
Correlated Refers to the outer row, so it re-runs per row.
1 / 2

IN, ANY, ALL and EXISTS

Four predicates connect an outer row to a subquery's rows:

  • x IN (subquery) is true if x equals some value returned.
  • x > ANY (subquery) is true if x is greater than at least one returned value. = ANY is the same as IN.
  • x > ALL (subquery) is true if x is greater than every returned value.
  • EXISTS (subquery) is true if the subquery returns at least one row. The select list does not matter.

SQLite supports IN and EXISTS, but not ANY and ALL. The equivalents are easy: x > ANY (S) is x > (SELECT min(...) FROM S) and x > ALL (S) is x > (SELECT max(...) FROM S), with extra care when the subquery can return NULL or no rows (an empty ALL is true, but x > max(...) is then NULL). Server engines accept the original forms:

SQL
-- Read-only: ANY / ALL are not available in SQLite
SELECT name FROM employee
WHERE salary > ALL (SELECT salary FROM employee WHERE dept_id = 2);

Correlated subqueries

A subquery is correlated when it refers to a column of the outer query. Conceptually it is re-evaluated for every outer row: the outer row is "passed in". The question about department averages is the classic case:

SQL Above the average of your own department

Press Run (or Ctrl + Enter) to see the result here.

Research averages about 5,133, so Ana (5,200) and Chen are above it; Sales averages 4,100, so only Eli is; Legal has just Fay, who equals her own average and is not above it. Both of the first two queries return Ana, Chen and Eli, which is the point: the same question has a correlated and an uncorrelated formulation. The third query returns Fay, Chen and Eli, the top earner of each department. Optimizers often rewrite one into the other (called decorrelation). The third query uses NOT EXISTS for "maximum within a group", a pattern that also appears in the division query.

IN versus EXISTS, and the NOT IN trap

IN and EXISTS usually give the same rows, but NOT IN and NOT EXISTS do not when a NULL is involved. x NOT IN (1, NULL) is never true, because comparing with NULL is unknown (three-valued logic). NOT EXISTS has no such problem. Prefer NOT EXISTS for "has no matching row" questions.

Views

A view stores a query, not data. Every time you select from the view, the database substitutes its definition, which is why a view is always up to date. Views serve three purposes: simplifying repeated queries, hiding columns and rows from users (see Authentication, Authorization and Access Control) and giving applications a stable interface when the underlying tables change, which is logical data independence.

SQL A view as an external schema

Press Run (or Ctrl + Enter) to see the result here.

After Dina's raise, the Sales average moves from 4,100 to 4,600 without touching the view. The last query lists employees who earn below their department average, using the view like a table.

Updating through views

Can you INSERT, UPDATE or DELETE on a view? Only if the database can unambiguously map the change to one base table row. In the SQL standard a view is updatable if it is based on a single table, has no aggregates, DISTINCT, GROUP BY or set operators, and includes the needed columns. WITH CHECK OPTION (not supported by SQLite) additionally refuses changes that would make a row disappear from the view.

SQLite is stricter: views are read-only. A direct update fails with cannot modify dept_summary because it is a view. The workaround is an INSTEAD OF trigger, which replaces the write with statements of your choosing:

SQL Making a view writable with INSTEAD OF

Press Run (or Ctrl + Enter) to see the result here.

Materialized views

A materialized view stores the query result on disk, like a table that remembers how it was computed. Reading it is as fast as reading a table, but the data can be stale and has to be refreshed. The trade-off is classic: spend storage and refresh work to make expensive aggregates (daily sales totals, say) cheap to read. This is central to data warehousing.

SQLite has no materialized views, so the following is read-only syntax from PostgreSQL and Oracle-style engines:

SQL
-- Read-only: PostgreSQL syntax, does not run in SQLite
CREATE MATERIALIZED VIEW daily_sales AS
SELECT order_date, sum(total) AS revenue FROM orders GROUP BY order_date;

REFRESH MATERIALIZED VIEW daily_sales;

You can imitate one in SQLite with a plain table that you rebuild yourself (DELETE then INSERT ... SELECT, inside a transaction), or keep it current with triggers. Engines differ in refresh strategy: complete refresh, incremental (only changes) or on every commit.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What makes a subquery correlated?

    A correlated subquery depends on the current outer row, so conceptually it is evaluated once per outer row.
  2. 2. Why can NOT IN return no rows when the subquery result contains a NULL?

    The test x NOT IN (1, NULL) expands to x <> 1 AND x <> NULL. The second part is unknown, so the whole condition is never true. NOT EXISTS avoids this.
  3. 3. What is stored when you create an ordinary view?

    An ordinary view stores only the defining query. Each read runs it against the current base tables.
  4. 4. What is the main drawback of a materialized view?

    A materialized view trades freshness for speed. Reading is fast but you must refresh it, which costs time.
  5. 5. How can you make a read-only SQLite view accept updates?

    An INSTEAD OF trigger intercepts the write and runs your own statements against the base tables.

Summary

  • Subqueries can be scalar, column, table or correlated; IN, ANY, ALL and EXISTS link them to the outer query (SQLite lacks ANY and ALL).
  • A correlated subquery depends on the outer row; many can be rewritten as joins or derived tables.
  • Prefer NOT EXISTS over NOT IN when NULLs are possible.
  • A view stores a query, always reflects current data and works as an external schema.
  • Standard SQL allows updates through simple single-table views; SQLite needs INSTEAD OF triggers.
  • A materialized view stores the result for speed and must be refreshed; SQLite does not have them.

Next: Triggers, Stored Procedures and Cursors.