3.3Joins and subqueries

Joins and subqueries can often express the same result in more than one way, and judging which formulation is correct and which is faster is core practical skill for a database specialist. The handling of NULL in particular is an area where beginners commonly go wrong, because conditional expressions evaluate under three-valued logic—true, false, or "unknown"—rather than just true/false. This section covers choosing a join type, subquery varieties, set operations, and NULL pitfalls.

3.3.1Choosing a join type

• If you want to see only customers who have orders, an inner join suffices, but if you want to see every customer regardless of whether they have orders—showing NULL when they do not—a left outer join is required. Choosing the wrong join type is a common practical bug: rows that genuinely exist end up silently missing from the result.
• If you attach a condition on the joined table's column in the WHERE clause after an outer join, that condition can implicitly exclude NULLs and silently revert the result to what an inner join would give (e.g., LEFT JOIN orders ON ... WHERE orders.status = 'shipped' excludes customers with no orders at all). In that case, the condition should instead be placed in the ON clause, or the WHERE clause should also allow for IS NULL.

3.3.2Correlated and uncorrelated subqueries

• An uncorrelated (non-correlated) subquery does not reference the outer query's values and can be executed independently just once. Its result is a single value or a fixed list, making it easy to combine with IN or a comparison operator.
• A correlated subquery references the outer query's per-row values, so the subquery is conceptually re-evaluated once for every outer row. It is typically paired with EXISTS/NOT EXISTS to test "does a related row exist or not"; the double NOT EXISTS idiom for relational division discussed earlier is one instance of this pattern.
• Although a correlated subquery is conceptually re-run once per outer row, many optimizers rewrite correlated subqueries into a join (a semi-join or anti-semi-join) for efficiency, so performance does not necessarily degrade linearly with a naive per-row execution. However, for sufficiently complex subqueries the optimizer may fail to rewrite them, causing genuinely repeated execution and real performance degradation—so checking the execution plan is essential.

3.3.3Set operations and the three-valued logic of NULL

• UNION combines two query results and removes duplicates (UNION ALL keeps duplicates). INTERSECT keeps only rows common to both. EXCEPT keeps rows present in one but not the other. All three require matching column count and compatible data types (union-compatibility).
• SQL conditional expressions are evaluated under three-valued logic, which has UNKNOWN in addition to TRUE and FALSE. Any comparison involving NULL (e.g., = NULL) always evaluates to UNKNOWN, and a WHERE clause excludes UNKNOWN rows from the result (only TRUE rows are kept).
• Testing for NULL requires IS NULL/IS NOT NULL, not = NULL. A well-known trap is that if even a single NULL appears in the result list of a NOT IN subquery, the comparison becomes UNKNOWN and the entire outer result unexpectedly comes back empty (using NOT EXISTS instead avoids this problem).

Suppose an application developer writes a query for "all customers except those who canceled an order" as customer_id NOT IN (SELECT customer_id FROM cancellations), and then reports a bug: no customers show up at all. The first step in diagnosing this is to check whether the cancellations.customer_id column contains any NULL. If even one row in cancellations was inserted with customer_id left unset (NULL) for some systemic reason, NULL ends up in the NOT IN subquery's result list, and under three-valued logic, the comparison "customer_id NOT IN (..., NULL, ...)" evaluates to UNKNOWN for every row, so the WHERE clause keeps none of them. There are two possible fixes: (1) add WHERE customer_id IS NOT NULL to the subquery to exclude the NULL, or (2) rewrite NOT IN as NOT EXISTS (a correlated subquery)—NOT EXISTS (SELECT 1 FROM cancellations c WHERE c.customer_id = customers.customer_id) performs an individual equality check each time and is unaffected by a stray NULL. In practice, from a recurrence-prevention standpoint, rewriting to NOT EXISTS (option 2) should be the standard, with option 1 treated as a stopgap fix. That is because another developer could write a similarly structured NOT IN subquery in the future and fall into the same trap; establishing a team coding convention that "exclusion-condition subqueries should generally use NOT EXISTS" is the more fundamental countermeasure.

3.3.4Section summary

• Attaching a WHERE condition on the joined table's column after an outer join risks silently reverting it to an inner join; put the condition in ON or make WHERE NULL-tolerant
• A NOT IN subquery whose result contains NULL can yield an unexpectedly empty result; NOT EXISTS avoids this trap
• Comparisons with NULL evaluate to UNKNOWN under three-valued logic, so testing for NULL requires IS NULL, not =