SQL Subqueries Practice Questions

Both can often give the same result. Choose by readability and by which columns you need.

• JOIN: when you need columns from both tables in the result.
• Subquery: when you only filter by, or compute one value from, the other table.
• EXISTS usually reads best for "has at least one" checks (shown here).
• Modern optimizers often produce the same plan, so check EXPLAIN before rewriting for speed.

A correlated subquery references a column from the outer query, so it is logically re-evaluated for every outer row.

• Typical use: compare each row to a value for its own group, like each salary against its department average (shown here).
• It can be slow on large tables if the database cannot optimize it away.
• Often rewritable as a JOIN to a pre-aggregated subquery, or as a window function.

If the subquery returns even one NULL, NOT IN returns no rows at all.

• x NOT IN (1, NULL) evaluates to unknown, never true.
• Use NOT EXISTS instead (shown here); it is NULL-safe.
• Or add WHERE col IS NOT NULL inside the subquery.