2.4 UNION, Subqueries, ANY, and ALL

A core concept in relational databases is: every query result is logically still a two-dimensional table (an entity / result set). Because it is a table, a query result can participate directly as an input to a larger, outer query. This section explores set operations (UNION), different types and executions of nested subqueries, and the often confusing quantified comparison operators (ANY and ALL).

UNION and UNION ALL: Combining Result Sets

When we want to merge two structurally identical result sets vertically rather than join them horizontally (JOIN), we use set consolidation operations.

SELECT title FROM online_courses
UNION
SELECT title FROM classroom_courses;

1. UNION (Distinct Merge)

UNION calculates the uniqueness union of the two result sets:

• Structural Alignment: The number of columns requested by both SELECT statements must be identical, and the data types at corresponding positions must be compatible or implicitly convertible.
• Higher Execution Cost: To filter out potential duplicate rows, the database must perform a Sort-Distinct operation in memory (or overflow to temporary files on disk). This sort-uniqueness processing can become a major performance bottleneck for large datasets.

2. UNION ALL (Direct Append)

SELECT title FROM online_courses
UNION ALL
SELECT title FROM classroom_courses;

• No Deduplication: It directly stacks rows from both sides like cards, keeping duplicates even if identical rows exist in both query results.
• Low Execution Cost: Since there is no need to scan for and eliminate redundant rows, the database pipes raw rows on the fly without sorting. Therefore, always prefer UNION ALL when duplicate values are theoretically impossible, or when displaying duplicate items is desirable for business logic.

The complete Classifications of Subqueries

A SELECT statement embedded inside another query (such as in SELECT, FROM, WHERE, or HAVING clauses) is called a subquery or nested query.

Based on data shape and evaluation style, subqueries are classified as follows:

A. Classification by Data Shape

1. Scalar Subqueries

A scalar subquery guarantees to return exactly one row and one column (a single cell value, like an average number, a maximum score, or a specific student's birthplace).

SELECT name, score
FROM students
WHERE score > (
  SELECT AVG(score)
  FROM students
);

*Here, (SELECT AVG(score) FROM students) behaves as a scalar value. It can be used anywhere an literal or static expression is allowed.*

2. Multiple-Row (List) Subqueries

Returns a single column with multiple rows (an array or list of values), typically evaluated using operators like IN or NOT IN.

SELECT title FROM courses
WHERE id IN (
  SELECT DISTINCT course_id FROM enrollments
);

3. Table Subqueries (Derived Tables)

Returns multiple rows and multiple columns, usually utilized in the FROM clause as a virtual table. An alias configured with AS is typically mandatory.

SELECT t.country, AVG(t.score)
FROM (
  SELECT * FROM students WHERE status = 'active'
) AS t
GROUP BY t.country;

B. Classification by Correlation

1. Self-contained (Independent) Subqueries

An independent subquery does not depend on any columns from the outer query. It can run in isolation.

• Execution Pattern: The database engine runs the inner query once, caches or holds its result in memory, and then evaluates the outer query by scanning and filtering rows against this precomputed helper.
• The average score query AVG(score) shown above is a select independent subquery.

2. Correlated Subqueries

A correlated subquery references columns from the outer query table, binding the inner execution loop tightly to the outer stream.

-- Find students who scored higher than the average score of their own class
SELECT s1.name, s1.score, s1.class_id
FROM students AS s1
WHERE s1.score > (
  SELECT AVG(s2.score)
  FROM students AS s2
  WHERE s2.class_id = s1.class_id -- References outer alias s1.class_id
);

• Execution Pattern: Mechanically, as the outer query slides row-by-row, it passes each row's active class_id to the inner query as parameters, re-evaluates the inner block, and compares the scalar result. This behaves as an $O(N \times M)$ nested loop. Be extremely mindful of performance costs with large datasets!

ANY, ALL, and the "NULL Trap"

ANY and ALL combined with comparison operators allow mathematical set comparisons:

• score > ANY (80, 90, 95): Since 80 is the minimum value in the set, this translates to score > 80. It evaluates to true if the left operand is greater than at least one value in the list.
• score > ALL (80, 90, 95): Since 95 is the maximum value in the set, this translates directly to score > 95. It evaluated to true only if the left operand is greater than every value in the target list.

> Note: Even though ANY and ALL are mathematically concise, they are rarely used in modern corporate codebases because rewriting using JOIN or standard aggregation (MIN / MAX) is much more readable.

🚨 Crucial Warning: NOT IN with NULL (The Infinite Silence)

When using set elements, combining NOT IN or != ALL with an inner query that returns one or more NULL values produces a logical disaster due to SQL's three-value logic (True, False, Unknown).

Imagine we execute:

SELECT * FROM students
WHERE student_id NOT IN (1, 2, NULL);

One might expect this to return everyone except students 1 and 2. However, this query returns an absolute empty set (zero rows)!

• Logical breakdown:
• t NOT IN (1, 2, NULL) is compiled into:

t != 1 AND t != 2 AND t != NULL

• In SQL theory, any comparison with a NULL evaluates to UNKNOWN (neither True nor False).
• t != 1 AND t != 2 AND UNKNOWN can never evaluate to True. As a result, every single record is blocked!
• Golden Rule: Never use NOT IN against a subquery that might yield NULL values! Filter out NULLs explicitly in the subquery using WHERE column IS NOT NULL, or rewrite using NOT EXISTS which uses true/false semi-join checks and is immune to NULL traps.

Optimization: When to replace or discard subqueries

While writing subqueries is highly intuitive, they historically suffered from performance issues because older query planners struggled to flatten them, creating massive in-memory temporary tables.

Modern design strategies:

1. Refactor to JOINs: When you map fields across queries, using explicit INNER JOIN or LEFT JOIN allows the query optimizer to choose efficient index-scan merge trees.

2. Utilize Common Table Expressions (CTE): WITH blocks decompose layers of nested queries into linear, readable steps, drastically improving maintainability. They can also act as optimization fences (preventing correlated subqueries from triggering repeated iterations).

-- RECOMMENDED: Clean, beautifully readable CTE
WITH class_averages AS (
  SELECT class_id, AVG(score) AS avg_score
  FROM students
  GROUP BY class_id
)
SELECT s.name, s.score
FROM students AS s
JOIN class_averages AS ca
  ON s.class_id = ca.class_id
WHERE s.score > ca.avg_score;