Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 75 ยท Querying with MySQL

Subqueries, Derived Tables and LATERAL

Learn MySQL subqueries: scalar, row and table subqueries, IN vs EXISTS, correlated subqueries, derived table merging and LATERAL derived tables from 8.0.14.

A subquery is a SELECT inside another statement. It lets you answer a question in two steps without creating a temporary table: first find the average price, then list the books above it; first find the customers who ordered, then list those who did not. When the inner query sits in the FROM clause it is called a derived table, and since MySQL 8.0.14 a derived table can even refer to the tables before it, with LATERAL.

This lesson covers the kinds of subqueries, IN versus EXISTS, correlated subqueries and why they can be slow or fast, how MySQL merges derived tables, and LATERAL. Examples run live in SQLite where the SQL is portable. LATERAL is shown as read-only code with a runnable equivalent.

Where a subquery can live

Scalar Returns one value. Use it like a number.
Table Returns rows. Use with IN, EXISTS or FROM.
Row Returns one row. Compare it as a tuple.

The place you put it decides what shape it may return.

1 / 2

Scalar, row and table subqueries

A scalar subquery returns exactly one row with one column and can appear wherever a single value fits: in the select list, in WHERE, even in ORDER BY. If it returns more than one row, MySQL raises error 1242 ("Subquery returns more than 1 row"). If it returns no rows, the value is NULL.

A row subquery returns one row with several columns and is compared to a row constructor: WHERE (a, b) = (SELECT x, y ...). A table subquery returns any number of rows and columns and is used with IN, ANY, ALL, EXISTS, or as a derived table in FROM.

SQL Scalar and table subqueries ยท database: bookstore

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

The subquery in the first two statements runs independently of the outer row. MySQL evaluates it once (or converts it into a join, see below) and reuses the answer.

IN, EXISTS and the NOT IN trap

IN (subquery) and EXISTS (subquery) often express the same question: for each outer row, is there a matching inner row?

SQL
-- these two return the same rows
SELECT name FROM customers WHERE id IN     (SELECT customer_id FROM orders);
SELECT name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

EXISTS only cares whether at least one row comes back, so what you select inside does not matter (SELECT 1 is the convention). Which one is faster? Modern MySQL turns both into a semi-join and picks a strategy itself, so for positive checks write whichever reads better and measure if it matters. Before 5.6, IN (subquery) was run as a dependent subquery and could be very slow, which is where the old advice "always use EXISTS" came from. That advice is out of date.

NOT IN and NOT EXISTS are where the two differ, because of NULL. If the subquery returns even one NULL, x NOT IN (...) can never be true: comparing with NULL gives unknown, and the whole condition returns no rows. NOT EXISTS does not have that problem:

SQL NOT IN versus NOT EXISTS with a NULL ยท database: bookstore

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

The first query returns nothing at all. The second lists every customer who is not in referrals. When the inner column can be NULL, prefer NOT EXISTS, or filter the subquery with WHERE customer_id IS NOT NULL. A LEFT JOIN ... WHERE right.id IS NULL is a third correct spelling (Joins in MySQL).

ANY and ALL

ANY and ALL put a comparison operator in front of a subquery that returns one column. x > ANY (set) is true when x beats at least one value in the set; x > ALL (set) is true when x beats every value. SOME is a synonym for ANY. = ANY is the same as IN, and <> ALL is the same as NOT IN, with the same NULL trap described above.

Given five books where Poetry costs 12.00 and 15.50 and Fiction costs 14.00, 16.00 and 9.00, MySQL 8.4 returns:

SQL
SELECT id, price FROM b WHERE genre = 'Fiction'
  AND price > ALL (SELECT price FROM b WHERE genre = 'Poetry');   -- id 4 (16.00 beats 12.00 and 15.50)

SELECT id, price FROM b WHERE genre = 'Fiction'
  AND price > ANY (SELECT price FROM b WHERE genre = 'Poetry');   -- ids 3 and 4 (14.00 and 16.00 beat 12.00)

> ALL is the same as comparing with MAX, and > ANY the same as comparing with MIN. The in-browser SQLite engine has no ANY or ALL, so this example uses that rewrite:

SQL ANY and ALL written with MIN and MAX

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

You get id 4 for the ALL test, and ids 3 and 4 for the ANY test. One difference matters when the subquery returns no rows: > ALL (empty set) is true for every row, while > ANY (empty set) is false. The MAX/MIN rewrite behaves differently there, because those return NULL for an empty set and the comparison becomes unknown, so it matches nothing. If the subquery can be empty, NOT EXISTS or EXISTS says what you mean most clearly.

Correlated subqueries

A subquery is correlated when it refers to a column of the outer query. Conceptually it runs once per outer row:

SQL
-- books that cost more than the average for their own genre
SELECT b.title, b.genre, b.price
FROM books AS b
WHERE b.price > (
  SELECT AVG(b2.price) FROM books AS b2 WHERE b2.genre = b.genre
);

Correlated subqueries are expressive but can be slow on large tables: if the inner query is not indexed, it scans the table once for every outer row. Two remedies are an index on the correlating column (genre here), or rewriting as a join to an aggregated derived table, which computes the averages once:

SQL A correlated subquery and its derived-table rewrite ยท database: bookstore

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

Both queries return the same rows. On a big table the second form is often faster and, in MySQL 8, the optimizer sometimes performs this transformation itself.

Derived tables

A derived table is a subquery in the FROM clause. It must have an alias, which MySQL enforces (error 1248, "Every derived table must have its own alias"). Derived tables let you aggregate in one step and join or filter in the next:

SQL
SELECT g.genre, g.titles
FROM (SELECT genre, COUNT(*) AS titles FROM books GROUP BY genre) AS g
WHERE g.titles >= 2;

How MySQL runs it matters. By default the optimizer tries to merge the derived table into the outer query, exactly as it does for a view with the MERGE algorithm (Views). When it can merge, there is no temporary table and the base-table indexes remain usable. A derived table cannot be merged if it contains GROUP BY, DISTINCT, LIMIT, aggregates, window functions or UNION. In that case MySQL materializes it into a temporary table, and since 8.0 it may add an index to that temporary table to speed up the outer join, and can push suitable conditions down into it.

You can see the choice in EXPLAIN: a materialized derived table appears as a <derived2> row. A common-table expression (WITH) is treated the same way (Common Table Expressions and Recursive CTEs), and a CTE that you reference twice is materialized once and reused.

LATERAL derived tables

An ordinary derived table is built on its own, so it cannot see the tables that come before it in the FROM clause. That blocks a common pattern: "for each customer, show their three most recent orders". Since MySQL 8.0.14 you can mark a derived table LATERAL, which lets it refer to earlier tables, like a correlated subquery that returns multiple rows and columns:

SQL
SELECT c.name, recent.id, recent.ordered_on
FROM customers AS c
JOIN LATERAL (
  SELECT o.id, o.ordered_on
  FROM orders AS o
  WHERE o.customer_id = c.id        -- refers to the table on the left
  ORDER BY o.ordered_on DESC
  LIMIT 3
) AS recent ON TRUE;

JOIN LATERAL ... ON TRUE keeps customers that have orders. Use LEFT JOIN LATERAL ... ON TRUE to keep customers with none, with NULL in the order columns. A lateral derived table is always re-evaluated for each row from the left, so it is flexible but not free; an index on orders (customer_id, ordered_on) makes each evaluation cheap.

SQLite has no LATERAL, so the query above is read-only here. The same "top N per group" result can be had with a window function, which also runs in MySQL 8 and is often easier to optimize (Window Functions):

SQL Top 2 books per genre without LATERAL ยท database: bookstore

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

The result lists the two most expensive books of each genre (one row for genres with a single book). Reach for LATERAL when the inner query needs the outer row's values in places a window function cannot reach, such as a different table with its own LIMIT.

Practical advice

  • A subquery in WHERE ... IN (...) is usually fine. Check EXPLAIN for a <subquery2> or MATERIALIZED note if it is slow (EXPLAIN and EXPLAIN ANALYZE).
  • Use NOT EXISTS instead of NOT IN when the inner column can be NULL.
  • A scalar subquery in the select list runs per row. If you use the same one many times, move the logic into a join.
  • Name your derived tables clearly. AS t three times in a query makes it hard to read.
  • Subqueries in UPDATE and DELETE cannot read the table you are modifying in MySQL (error 1093). Wrap the inner query in a derived table to work around it.

What ran live here

All subquery, derived-table and window-function examples ran in SQLite with the bookstore data. LATERAL is a MySQL 8.0.14 feature that SQLite does not have, so it is read-only; the window-function version produces a comparable result.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What happens if a scalar subquery returns two rows?

    A scalar subquery must return at most one row. Use LIMIT 1, an aggregate, or IN if you expect several.
  2. 2. Why can "x NOT IN (SELECT col FROM t)" return no rows even when many values are missing?

    Comparing to NULL yields unknown, so no outer row can satisfy NOT IN. NOT EXISTS or filtering out the NULLs avoids it.
  3. 3. What must every derived table in MySQL have?

    MySQL raises error 1248 if a derived table has no alias.
  4. 4. What does LATERAL (MySQL 8.0.14) allow in a derived table?

    A LATERAL derived table can use columns of earlier tables, which makes "top N per row" queries possible.
  5. 5. Which derived table can the optimizer NOT merge into the outer query?

    Grouping and aggregation prevent merging, so MySQL materializes the result into a temporary table.

Summary

  • Subqueries can be scalar (one value), row (one row) or table (many rows). A scalar subquery with several rows is an error.
  • IN and EXISTS are usually equivalent for positive checks because MySQL turns them into semi-joins. NOT IN breaks when the list has NULL; use NOT EXISTS.
  • Correlated subqueries run per outer row. Index the correlating column or rewrite as a join to a derived table.
  • Derived tables need an alias, are merged when possible and materialized when they group, limit or aggregate.
  • LATERAL (8.0.14) lets a derived table use earlier tables in FROM, which solves "top N per row".

Next: UNION, INTERSECT and EXCEPT.