Scalar, Correlated, EXISTS, IN, and Derived-Table Subqueries

Learning outcomes

Subqueries are useful when a question naturally contains another question: “orders whose priority is above the customer’s average,” “customers that have at least one open order,” or “technicians who have no open order.” But different subquery forms impose different cardinality and NULL rules. A scalar subquery that unexpectedly returns two rows is an error; NOT IN can turn a single NULL in the inner result into an unknown predicate for every candidate row.

Verify the schema assumptions that subqueries depend on

Subquery behavior often depends on nullability and uniqueness. Before reasoning about membership or scalar cardinality, inspect the actual schema rather than assuming yesterday’s DDL is still in effect.

SHOW CREATE TABLE work_orders;SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_KEYFROM INFORMATION_SCHEMA.COLUMNSWHERE TABLE_SCHEMA='servicehub_query_lab'  AND TABLE_NAME='work_orders'  AND COLUMN_NAME IN ('work_order_id','customer_id','technician_id','priority')ORDER BY ORDINAL_POSITION;

The nullable technician_id and priority columns are intentional. They are exactly why anti-membership and aggregate examples must account for NULL. If a migration changes nullability or uniqueness later, rerun the semantic tests; valid SQL can acquire different business meaning when schema guarantees change.

Scalar subqueries promise at most one row

A scalar subquery is used where one value is expected. Zero rows produce a NULL scalar result; more than one row violates the contract and MySQL raises error 1242, “Subquery returns more than 1 row.” That failure is useful evidence: the query’s assumed cardinality is false.

USE servicehub_query_lab;SELECT work_order_id, priorityFROM work_ordersWHERE priority = (  SELECT MIN(priority)  FROM work_orders  WHERE priority IS NOT NULL)ORDER BY work_order_id;

-- Wrong: this inner query returns multiple priority values.SELECT work_order_idFROM work_ordersWHERE priority = (  SELECT priority  FROM work_orders  WHERE status='open');-- Expected: ERROR 1242 (21000): Subquery returns more than 1 row

Do not “fix” this by adding an arbitrary LIMIT 1 unless the business rule truly defines which one row should win. If the intended logic is membership, use IN; if it is an extremum, aggregate; if it is a one-to-one relationship, enforce that relationship in the schema and still handle unexpected data carefully.

Correlated subqueries express an outer-row dependency

A correlated subquery refers to columns from an outer query block. Logically, its result depends on the current outer row. MySQL can sometimes transform eligible correlated scalar subqueries or other subquery predicates, so the physical plan need not be a naive repeated execution.

SELECT w.work_order_id,       w.customer_id,       w.priority,       (         SELECT AVG(w2.priority)         FROM work_orders AS w2         WHERE w2.customer_id = w.customer_id       ) AS customer_avg_priorityFROM work_orders AS wORDER BY w.work_order_id;

For customer 1, the inner aggregate considers work orders 1001 and 1002; for customer 3, it considers 1004 and 1005. Because AVG() ignores NULL inputs, customer 2’s average is based on work order 1007 while 1003’s NULL priority does not contribute. Make those data rules explicit when they matter.

EXISTS asks whether any qualifying row exists

EXISTS is an existence predicate. The selected expression inside the subquery is not the point; the question is whether at least one row qualifies. This maps naturally to “customers with an open order” or “technicians with no open order.”

SELECT c.customer_id, c.customer_nameFROM customers AS cWHERE EXISTS (  SELECT 1  FROM work_orders AS w  WHERE w.customer_id = c.customer_id    AND w.status='open')ORDER BY c.customer_id;SELECT t.technician_id, t.technician_nameFROM technicians AS tWHERE NOT EXISTS (  SELECT 1  FROM work_orders AS w  WHERE w.technician_id = t.technician_id    AND w.status='open')ORDER BY t.technician_id;

The optimizer may implement eligible IN/EXISTS predicates using semijoin strategies, materialization, or an EXISTS-oriented strategy. Use EXPLAIN to see the current choice instead of assuming syntax maps one-to-one to a physical algorithm.

IN and NOT IN: membership plus three-valued logic

IN is convenient when the inner query produces a set of candidate values. The dangerous case is NOT IN when the inner result can contain NULL. If the comparison against a NULL candidate remains unknown and no equal value is found, the overall predicate can be unknown rather than true, causing rows you expected to keep to disappear.

DROP TABLE IF EXISTS technician_blacklist;CREATE TABLE technician_blacklist (  technician_id BIGINT UNSIGNED NULL) ENGINE=InnoDB;INSERT INTO technician_blacklist VALUES (13), (NULL);-- Surprising: NULL in the subquery can make NOT IN unknown.SELECT technician_id, technician_nameFROM techniciansWHERE technician_id NOT IN (  SELECT technician_id FROM technician_blacklist)ORDER BY technician_id;-- Repair 1: exclude NULL explicitly if that matches the domain rule.SELECT technician_id, technician_nameFROM techniciansWHERE technician_id NOT IN (  SELECT technician_id  FROM technician_blacklist  WHERE technician_id IS NOT NULL)ORDER BY technician_id;-- Repair 2: NOT EXISTS expresses anti-membership directly.SELECT t.technician_id, t.technician_nameFROM technicians AS tWHERE NOT EXISTS (  SELECT 1  FROM technician_blacklist AS b  WHERE b.technician_id = t.technician_id)ORDER BY t.technician_id;

Derived tables: a query result used as a table

A subquery in the FROM clause is a derived table and must have a table alias. It is useful for naming an intermediate relational result. MySQL can sometimes merge a derived table into the outer query or materialize it into an internal temporary table. The SQL text defines the result; optimizer evidence tells you how the current server chose to realize it.

SELECT c.customer_name,       x.open_ordersFROM customers AS cJOIN (  SELECT customer_id, COUNT(*) AS open_orders  FROM work_orders  WHERE status='open'  GROUP BY customer_id) AS x  ON x.customer_id = c.customer_idORDER BY c.customer_id;EXPLAIN FORMAT=TREESELECT c.customer_name, x.open_ordersFROM customers AS cJOIN (  SELECT customer_id, COUNT(*) AS open_orders  FROM work_orders  WHERE status='open'  GROUP BY customer_id) AS x  ON x.customer_id = c.customer_id;

The aggregation in this derived table creates one row per customer with open work. That can make its cardinality easier to reason about before the join. Later chapters will go deeper into optimizer traces and indexing; here the objective is semantic clarity plus basic plan observation.

Equivalent-looking formulations can receive different plans

EXPLAIN FORMAT=TREESELECT c.customer_idFROM customers AS cWHERE EXISTS (  SELECT 1  FROM work_orders AS w  WHERE w.customer_id=c.customer_id    AND w.status='open');EXPLAIN FORMAT=TREESELECT DISTINCT c.customer_idFROM customers AS cJOIN work_orders AS w  ON w.customer_id=c.customer_idWHERE w.status='open';

Both statements can express “customers with at least one open order,” but the join form naturally produces one row per matching work order before DISTINCT, while the existence form directly states the membership question. MySQL may transform either internally. Choose syntax for correct meaning and maintainability first, then inspect performance evidence.

Hands-on lab: five subquery contracts

1. Use a scalar aggregate subquery to find work orders at the minimum non-NULL priority.
2. Run the deliberately multi-row scalar subquery and record error 1242.
3. Use a correlated subquery to display each order beside its customer’s average priority.
4. Find customers with open orders using EXISTS and technicians with no open orders using NOT EXISTS.
5. Create the blacklist containing a NULL and reproduce the NOT IN trap; repair it in two ways.
6. Build the derived-table open-order summary and compare its EXPLAIN FORMAT=TREE output with the existence formulation.
7. Drop technician_blacklist after the lab.

Production judgment and next bridge

Use subqueries when they make the business question clearer, not because they are assumed to be faster or slower than joins. Enforce cardinality where possible, audit NULL behavior in membership tests, and treat the current plan as evidence rather than syntax folklore. For performance incidents, capture the exact SQL, parameter values or representative selectivity, schema/index definitions, statistics context, and plan.

Lesson 4 gives a name to intermediate queries with common table expressions and then adds recursion. That is powerful for hierarchies—but recursion introduces termination, type-width, and cycle risks that deserve explicit safety controls.

Authoritative references

• MySQL 8.4 Reference Manual — Subqueries
• MySQL 8.4 Reference Manual — Subquery Errors
• MySQL 8.4 Reference Manual — Correlated Subqueries
• MySQL 8.4 Reference Manual — Derived Tables
• MySQL 8.4 Reference Manual — Optimizing Subqueries, Derived Tables, Views, and CTEs