Subqueries in SELECT, FROM and WHERE: Join vs Subquery vs EXISTS

Concept

Most questions can be written as a join, a subquery or an EXISTS check. They are not interchangeable in every case, and the differences that matter are row multiplication, NULL safety and cost.

Subqueries in each position

-- In WHERE: filter the outer rows
SELECT name FROM customers
WHERE  id IN (SELECT customer_id FROM orders WHERE status = 'shipped');

-- In SELECT: add a computed column, one value per outer row
SELECT c.name,
       (SELECT MAX(o.order_date) FROM orders o WHERE o.customer_id = c.id) AS last_order
FROM   customers c;

-- In FROM: treat a query result as a table (a derived table)
SELECT t.country, t.customers, t.avg_orders
FROM  (SELECT c.country,
              COUNT(DISTINCT c.id)                     AS customers,
              ROUND(COUNT(o.id) * 1.0 / COUNT(DISTINCT c.id), 2) AS avg_orders
       FROM   customers c
       LEFT JOIN orders o ON o.customer_id = c.id
       GROUP BY c.country) t
WHERE  t.customers > 1;

A subquery in FROM is a derived table and must have an alias in MySQL, MariaDB and PostgreSQL - omitting it is a syntax error.

The same question, four ways

Which customers have placed at least one shipped order?

-- 1. JOIN with DISTINCT
SELECT DISTINCT c.name
FROM   customers c
JOIN   orders o ON o.customer_id = c.id AND o.status = 'shipped';

-- 2. IN with a subquery
SELECT name FROM customers
WHERE  id IN (SELECT customer_id FROM orders WHERE status = 'shipped');

-- 3. EXISTS
SELECT c.name FROM customers c
WHERE  EXISTS (SELECT 1 FROM orders o
               WHERE o.customer_id = c.id AND o.status = 'shipped');

-- 4. Pre aggregated join
SELECT c.name
FROM   customers c
JOIN  (SELECT DISTINCT customer_id FROM orders WHERE status = 'shipped') s
       ON s.customer_id = c.id;

Explanation

The deciding question is simple: do you need columns from the other table?

• Yes, and one row each - use a JOIN.
• Yes, but the other table has many rows per parent - aggregate it in a derived table or CTE, then join.
• No, you only need to know whether a match exists - use EXISTS or NOT EXISTS.

Performance rarely decides it any more. On modern optimisers IN, EXISTS and a semi join are frequently compiled to the same plan. Where they differ, EXISTS tends to win on large child tables because it stops at the first match, and the pre aggregated join wins when the child aggregate is needed anyway.

Important rules

• A derived table needs an alias in MySQL, MariaDB and PostgreSQL.
• A subquery in SELECT must return one row and one column.
• A join to the many side multiplies parent rows; IN and EXISTS never do.
• NOT IN is unsafe against nullable columns; NOT EXISTS and LEFT JOIN ... IS NULL are not.
• A CTE (WITH) is a named derived table and is almost always more readable than nesting.

Common mistakes

• Joining to check existence, then sprinkling DISTINCT to clean up the duplicates.
• Using NOT IN on a nullable column.
• Nesting derived tables three deep instead of using CTEs.
• Assuming a subquery is always slower than a join - measure before rewriting.

Best practices

• Choose by intent: JOIN to combine data, EXISTS to test, derived table or CTE to reshape.
• Prefer a CTE to a nested derived table for anything more than one level deep.
• Read the execution plan before optimising - see the note on EXPLAIN.
• Keep the anti join pattern consistent across the codebase so reviewers recognise it instantly.

Practice

1. Answer which products have been ordered at least once in all four styles above.
2. Return each country with its customer count and total revenue, using a derived table.
3. For each style, say whether it can also return the order date, and why.