Subqueries, Derived Tables, and Set Operations

Subqueries express multi-step questions; set operations combine and compare result sets (data reconciliation, migration testing).

Concept

Subquery: a SELECT inside another statement — scalar, row, column (IN/ANY/ALL), table (derived table), correlated, or EXISTS.

Derived table: a subquery in FROM (must have an alias in MySQL).

LATERAL derived table (8.0.14+): a derived table that can reference columns of preceding tables.

Set operations: UNION, UNION ALL, INTERSECT, EXCEPT (INTERSECT/EXCEPT since 8.0.31).

Why It Matters

Subqueries express multi-step questions; set operations combine and compare result sets (data reconciliation, migration testing).

Syntax

WHERE col = (SELECT ...)                         -- scalar
WHERE col IN (SELECT ...)                         -- column
WHERE (a, b) IN (SELECT x, y ...)                 -- row
WHERE EXISTS (SELECT 1 FROM ... WHERE correlated) -- existence
FROM (SELECT ...) AS alias                        -- derived table
FROM t, LATERAL (SELECT ... WHERE u.k = t.k LIMIT 1) AS x
SELECT ... UNION [ALL | DISTINCT] SELECT ...
SELECT ... INTERSECT SELECT ...
SELECT ... EXCEPT SELECT ...

Example

13.1 Scalar and column subqueries 13.2 NOT IN vs NOT EXISTS MySQL 8 executes IN/EXISTS subqueries efficiently with semi-join strategies and NOT EXISTS with anti-join (8.0.17+).

13.3 Correlated subquery 13.4 Row subquery 13.5 ANY / ALL 13.6 Derived tables and LATERAL

Limitation: MySQL does not allow LIMIT inside IN (subquery):

13.7 UNION and UNION ALL 13.8 INTERSECT and EXCEPT (8.0.31+) On older versions:

SELECT first_name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)      -- 117500
ORDER BY salary DESC;                                    -- Asha, Vikram, Rahul

SELECT name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);   -- Ananya, Ben, Chen, Diya

-- Employees who manage nobody
SELECT first_name FROM employees
WHERE emp_id NOT IN (SELECT manager_id FROM employees);          -- 0 rows! (a manager_id is NULL)

SELECT e.first_name FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.emp_id);
-- Neha, Priya, Kiran, Meera

SELECT e.first_name, e.dept_id, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(e2.salary) FROM employees e2 WHERE e2.dept_id = e.dept_id)
ORDER BY e.dept_id, e.salary DESC;
-- Asha (1), Vikram (1), Rahul (2)

SELECT first_name, dept_id, salary
FROM employees
WHERE (dept_id, salary) IN (SELECT dept_id, MAX(salary) FROM employees GROUP BY dept_id)
ORDER BY dept_id;
-- Asha (1, 250000), Rahul (2, 120000), Arjun (3, 90000)

SELECT first_name FROM employees
WHERE salary > ALL (SELECT salary FROM employees WHERE dept_id = 2);   -- Asha, Vikram

SELECT ROUND(AVG(t.spent), 2) AS avg_customer_value
FROM (SELECT customer_id, SUM(total_amount) AS spent
      FROM orders WHERE status <> 'CANCELLED'
      GROUP BY customer_id) AS t;                      -- 66833.33

-- Latest order per customer with LATERAL (8.0.14+)
SELECT c.name, lo.order_id, lo.order_date
FROM customers c
JOIN LATERAL (
    SELECT o.order_id, o.order_date
    FROM orders o
    WHERE o.customer_id = c.customer_id
    ORDER BY o.order_date DESC
    LIMIT 1
) AS lo ON TRUE
ORDER BY c.customer_id;

+--------------+----------+------------+
| name         | order_id | order_date |
+--------------+----------+------------+
| Ananya Gupta |     1003 | 2026-02-02 |
| Ben Carter   |     1006 | 2026-03-10 |
| Chen Wei     |     1004 | 2026-02-14 |
| Diya Patel   |     1005 | 2026-03-01 |
+--------------+----------+------------+

SELECT * FROM products WHERE product_id IN (SELECT product_id FROM order_items LIMIT 3);
-- ERROR 1235: This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'
-- Workaround: wrap in a derived table: IN (SELECT product_id FROM (SELECT ... LIMIT 3) x)

SELECT city AS place FROM customers
UNION
SELECT location FROM departments
ORDER BY place;
-- Bangalore, Delhi, London, Milan, Mumbai, Pune, Singapore   (7 rows; UNION ALL gives 9)

SELECT city FROM customers INTERSECT SELECT location FROM departments;   -- Mumbai, Pune
SELECT city FROM customers EXCEPT    SELECT location FROM departments;   -- London, Milan, Singapore

Expected Result

Products never ordered:

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

+---------------+
| product_name  |
+---------------+
| Standing Desk |
+---------------+

Common Mistakes

NOT IN with nullable subquery columns.

Missing derived-table alias (ERROR 1248).

LIMIT inside IN subqueries (ERROR 1235).

UNION where UNION ALL was correct.

INTERSECT/EXCEPT on MySQL < 8.0.31.

Best Practices

Prefer NOT EXISTS for anti-joins.

Use CTEs (Module 14) instead of deep nesting.

Use LATERAL for "top-N per row" lookups with good indexes.

Interview Relevance

High: second-highest salary, NOT IN vs NOT EXISTS, correlated subqueries, UNION vs UNION ALL, INTERSECT support in MySQL.

Practice Questions

Find the second-highest salary with a subquery.

Find customers who ordered both a Laptop and a Monitor.

Find departments whose average salary is above the company average.

Compare customers with a copy table using EXCEPT in both directions.