Subqueries, CTEs and Views

Subqueries

Some questions need the answer to another question first. "Which products cost more than the average?" requires the average before you can compare. A subquery (or nested query) is a SELECT statement in parentheses inside another statement. The database evaluates it and uses its result in the outer query.

Scalar subqueries: one value

A subquery that returns exactly one row with one column is a scalar subquery. You can use it wherever a single value is allowed, such as on one side of a comparison:

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;

name                      price
------------------------  -----
27-inch monitor           249
Noise-cancelling headset  129
Mechanical keyboard       89

The inner query computes the average price (65.9 euros); the outer query keeps the products above it. You cannot write WHERE price > AVG(price) directly, because WHERE works on single rows and an aggregate needs all of them (see WHERE versus HAVING).

If a scalar subquery returns no row, its value is NULL. If it returns more than one row, PostgreSQL, MySQL and SQL Server raise an error, while SQLite quietly uses the first row — another case where a query that runs in SQLite is not necessarily correct.

Subqueries with IN and EXISTS

A subquery that returns one column with several rows is a list. IN tests whether a value is in that list. Customers who ordered at least one product from the Peripherals category:

SELECT customer_id, first_name
FROM customers
WHERE customer_id IN (
    SELECT o.customer_id
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    JOIN products    AS p  ON p.product_id = oi.product_id
    WHERE p.category = 'Peripherals'
)
ORDER BY customer_id;

customer_id  first_name
-----------  ----------
1            Sanne
3            Mei
5            Lena

Sanne bought two peripherals, but she appears once: IN only asks whether she is in the list, not how often. The same query written as a join returns her twice (see duplicate rows).

EXISTS asks the same question differently: it is true when the subquery returns at least one row. The subquery refers to the outer table (c.customer_id), so it is checked for each customer:

SELECT c.customer_id, c.first_name
FROM customers AS c
WHERE EXISTS (
    SELECT 1
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    JOIN products    AS p  ON p.product_id = oi.product_id
    WHERE o.customer_id = c.customer_id
      AND p.category = 'Peripherals'
)
ORDER BY c.customer_id;

customer_id  first_name
-----------  ----------
1            Sanne
3            Mei
5            Lena

What the subquery selects does not matter for EXISTS, only whether it finds a row; SELECT 1 is the usual way to say so. Both forms are called semi-joins: they filter the outer table by the existence of a partner without repeating rows.

NOT EXISTS: rows without a match

Negating the test finds rows that have no partner. Products that were never ordered:

SELECT p.product_id, p.name
FROM products AS p
WHERE NOT EXISTS (
    SELECT 1 FROM order_items AS oi WHERE oi.product_id = p.product_id
);

product_id  name
----------  ------------
9           Webcam 1080p

This gives the same result as the left-join anti-join. Many people find NOT EXISTS easier to read because it says literally what is meant.

SELECT name
FROM employees
WHERE employee_id NOT IN (SELECT manager_id FROM employees);

SELECT e.name
FROM employees AS e
WHERE NOT EXISTS (SELECT 1 FROM employees AS r WHERE r.manager_id = e.employee_id)
ORDER BY e.employee_id;

name
----

name
------------
Daan Visser
Chloé Martin
Mateus Costa

4 NOT IN (1, 2, NULL) means 4 <> 1 AND 4 <> 2 AND 4 <> NULL. The last comparison is unknown, so the whole condition is never true (see three-valued logic). NOT EXISTS does not have this problem. If you do use NOT IN, add WHERE manager_id IS NOT NULL to the subquery.

Correlated subqueries

The EXISTS examples above use a correlated subquery: a subquery that refers to a column of the outer query, so its result is different for each outer row. A classic use is "the latest order of each customer": for every order, compare its date with the latest date of that same customer:

SELECT o.customer_id, o.order_id, o.order_date
FROM orders AS o
WHERE o.order_date = (
    SELECT MAX(o2.order_date)
    FROM orders AS o2
    WHERE o2.customer_id = o.customer_id
)
ORDER BY o.customer_id;

customer_id  order_id  order_date
-----------  --------  ----------
1            103       2026-06-18
2            107       2026-08-03
3            109       2026-09-02
4            105       2026-07-09
5            106       2026-07-21
6            108       2026-08-15
7            110       2026-09-20

The two aliases o and o2 refer to the same table in two roles: the outer order being checked, and all orders of its customer. If a customer had two orders on the same latest date, both would be returned. A correlated subquery can also sit in the SELECT list, producing one value per row:

SELECT c.first_name,
       (SELECT COUNT(*) FROM orders AS o
        WHERE o.customer_id = c.customer_id) AS orders
FROM customers AS c
ORDER BY c.customer_id;

first_name  orders
----------  ------
Sanne       2
Kwame       2
Mei         2
Omar        1
Lena        1
Priya       1
Tom         1
Ana         0

What they cost

Logically, a correlated subquery is evaluated once for every row of the outer query. For eight customers that is nothing; for two million orders, each scanning the orders table again, it can be very slow. Two things keep it reasonable in practice:

• an index on the column the subquery filters on (here orders.customer_id), so each evaluation is a quick lookup instead of a scan;
• the query optimizer, which often rewrites EXISTS and IN into a join internally. Check with the query plan rather than assuming.

When performance matters, the same results can usually be written without correlation: join to a grouped derived table (one row per customer with MAX(order_date)), or use a window function such as ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) and keep row number 1.

Derived tables: subqueries in FROM

A subquery that returns rows and columns can be used as if it were a table. Such a subquery in the FROM clause is a derived table. It is the way to aggregate twice: first per order, then over the orders. The average order value, ignoring cancelled orders:

SELECT COUNT(*)              AS orders,
       SUM(total)            AS revenue,
       ROUND(AVG(total), 2)  AS avg_order_value
FROM (
    SELECT o.order_id, SUM(oi.quantity * oi.unit_price) AS total
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY o.order_id
) AS order_totals;

orders  revenue  avg_order_value
------  -------  ---------------
9       1263     140.33

AVG(oi.quantity * oi.unit_price) directly on the joined rows would give the average line value, not the average order value; the derived table first turns lines into orders. Give a derived table an alias (AS order_totals): MySQL and SQL Server require one, PostgreSQL did until version 16, and it makes the query easier to read.

Common table expressions (WITH)

Nested subqueries are read from the inside out, which gets hard after two levels. A common table expression (CTE) moves each step to the front of the query and gives it a name with WITH name AS (...). The main query, and later CTEs, then use that name like a table. The query reads top to bottom, one step at a time.

Ranking customers by revenue in three steps: total per order, total per customer, then the ranking with names:

WITH order_totals AS (
    SELECT o.order_id, o.customer_id,
           SUM(oi.quantity * oi.unit_price) AS total
    FROM orders AS o
    JOIN order_items AS oi ON oi.order_id = o.order_id
    WHERE o.status <> 'cancelled'
    GROUP BY o.order_id, o.customer_id
),
customer_totals AS (
    SELECT customer_id, COUNT(*) AS orders, SUM(total) AS revenue
    FROM order_totals
    GROUP BY customer_id
)
SELECT RANK() OVER (ORDER BY ct.revenue DESC) AS revenue_rank,
       c.first_name,
       ct.orders,
       ct.revenue
FROM customer_totals AS ct
JOIN customers AS c ON c.customer_id = ct.customer_id
ORDER BY revenue_rank;

revenue_rank  first_name  orders  revenue
------------  ----------  ------  -------
1             Priya       1       498
2             Kwame       2       296
3             Mei         2       205
4             Sanne       2       132
5             Lena        1       123
6             Tom         1       9

Each CTE can be tested on its own: replace the final SELECT with SELECT * FROM order_totals and check the intermediate result. CTEs exist only for the duration of the one statement. They are supported by SQLite (since 3.8.3), PostgreSQL, MySQL 8.0, MariaDB 10.2, SQL Server and Oracle. Whether a database computes a CTE once or merges it into the main query is an optimizer decision; in current versions a CTE is usually not slower than the equivalent subquery. Building analytical queries step by step with CTEs: Analytical SQL.

Recursive CTEs

A recursive CTE refers to itself. It has two parts joined by UNION ALL: an anchor query that produces the starting rows, and a recursive query that produces the next rows from the rows found so far. The database repeats the recursive part until it adds no new rows. This is the standard way to walk a hierarchy of unknown depth, such as the management chain in employees:

WITH RECURSIVE chain (employee_id, name, role, manager_id, level) AS (
    -- anchor: the starting employee
    SELECT employee_id, name, role, manager_id, 0
    FROM employees
    WHERE name = 'Mateus Costa'
    UNION ALL
    -- recursive step: the manager of the previous row
    SELECT e.employee_id, e.name, e.role, e.manager_id, chain.level + 1
    FROM employees AS e
    JOIN chain ON e.employee_id = chain.manager_id
)
SELECT level, name, role
FROM chain
ORDER BY level;

level  name              role
-----  ----------------  -----------------
0      Mateus Costa      Warehouse Worker
1      Yuki Tanaka       Head of Warehouse
2      Fatima El Amrani  Director

Step by step: the anchor finds Mateus (level 0). The recursive step finds the employee whose id equals Mateus's manager_id: Yuki (level 1). Then Yuki's manager, Fatima (level 2). Fatima's manager_id is NULL, which matches nothing, so the recursion stops. Turning the join around (e.manager_id = chain.employee_id) walks downward instead and lists everyone below a given person.

PostgreSQL, MySQL and SQLite write WITH RECURSIVE; SQL Server and Oracle write plain WITH and recognize the recursion themselves. If the data contains a cycle (A manages B, B manages A), a recursive CTE never ends: SQL Server stops with an error after 100 levels by default and MySQL after 1,000; elsewhere, add a depth limit in the query such as WHERE chain.level < 20 (PostgreSQL 14+ can also detect cycles with a CYCLE clause).

Views

A view is a named query stored in the database. Once created, it can be queried like a table, so a complex query is written once and reused by everyone:

CREATE VIEW order_totals AS
SELECT order_id,
       SUM(quantity * unit_price) AS total
FROM order_items
GROUP BY order_id;

SELECT order_id, total
FROM order_totals
WHERE total > 150
ORDER BY total DESC;

order_id  total
--------  -----
108       498
102       261
104       156

A view stores no data

A view stores the query, not the result. Every time you select from it, the database runs the underlying query against the current data, so a view is never out of date. When order 110 gets two more USB-C cables, the view's total changes with it:

CREATE VIEW order_totals AS
SELECT order_id, SUM(quantity * unit_price) AS total
FROM order_items
GROUP BY order_id;

SELECT order_id, total FROM order_totals WHERE order_id = 110;

UPDATE order_items SET quantity = 3 WHERE order_id = 110 AND product_id = 1;

SELECT order_id, total FROM order_totals WHERE order_id = 110;

order_id  total
--------  -----
110       9

order_id  total
--------  -----
110       27

The flip side is that a view is exactly as fast as its query: a slow query stays slow behind a short name. Some systems also offer a materialized view, which does store the result. In PostgreSQL (REFRESH MATERIALIZED VIEW) and usually in Oracle it must be refreshed, trading freshness for speed; SQL Server's indexed views are instead kept up to date automatically on every change, trading write speed for read speed. Materialized views are common in reporting. SQLite has no materialized views. A view used as the read side of a system is close to what architects call a read model; see CQRS.

Whether you can write through a view depends on the system. Simple single-table views are updatable in PostgreSQL, MySQL and SQL Server; in SQLite every view is read-only unless you add a trigger:

CREATE VIEW order_totals AS
SELECT order_id, SUM(quantity * unit_price) AS total
FROM order_items
GROUP BY order_id;

DELETE FROM order_totals WHERE order_id = 110;

Error: cannot modify order_totals because it is a view

Views for security

Because a view can select only some columns and rows, it is also an access-control tool. Support staff may need to look up a customer's city, but not their email address. Create a view with only the allowed columns and give the support role access to the view, not to the table:

CREATE VIEW customer_directory AS
SELECT customer_id, first_name, city, country
FROM customers;

SELECT * FROM customer_directory WHERE country = 'NL' ORDER BY customer_id;

customer_id  first_name  city       country
-----------  ----------  ---------  -------
1            Sanne       Utrecht    NL
2            Kwame       Rotterdam  NL
3            Mei         Amsterdam  NL
6            Priya       NULL       NL
7            Tom         Utrecht    NL

SQLite has no users or permissions, so the second half of this pattern looks like this in PostgreSQL (not run here):

-- PostgreSQL: support staff can read the view, but not the customers table
GRANT SELECT ON customer_directory TO support_staff;
REVOKE ALL ON customers FROM support_staff;

Roles, GRANT and row-level security are covered on Database Security.