The Project | About Us | Contribute | Donations | License

HOME

This lesson teaches no new syntax. It teaches you to decide. After four lessons you have two overlapping toolboxes: almost every question you can answer with a JOIN also admits a subquery, and the other way round. And the problem with having two ways of writing the same thing is that nobody tells you which one to use.

There's a base rule that settles 80 % of cases in five seconds, four great equivalences worth knowing in both their forms, a real difference —not a stylistic one— between IN and an INNER JOIN, and a performance criterion to be handled with humility: the optimizer rewrites many of these queries, and guessing which is faster without measuring is the most common way of wasting your time.

The base rule
Equivalence 1: IN against INNER JOIN (and the real difference)
Equivalence 2: the four ways of answering "what doesn't match"
Equivalence 3: scalar in the SELECT against LEFT JOIN + GROUP BY
Equivalence 4: correlated against derived table
What the optimizer does underneath
Readability and maintenance, a first-class criterion
Guide table: I want X → write Y
Common Mistakes and Tips
Exercises
Module conclusion

The base rule

If you need columns from the other table in the result, it's a JOIN. If you only need to filter or compute a value, it's usually a subquery.

It's surprisingly reliable. Read it as a question about the SELECT, not about the WHERE: look at which columns you want to display and where they come from.

The question	What you want to display	Tool
"Orders with their customer's name"	Columns from `orders` and from `customers`	`JOIN`
"Customers who have bought at some point"	Only columns from `customers`	Subquery (`EXISTS`)
"Products above the average price"	Only columns from `products`	Scalar subquery
"Order lines with product and category"	Three tables in the result	`JOIN`
"Categories billing more than the average"	`categories` + an aggregate of their own	`GROUP BY` + subquery in the `HAVING`

And its corollary, which is just as useful: if you catch yourself writing a JOIN followed by a DISTINCT, you almost always wanted an EXISTS. The DISTINCT is the symptom that you've brought in rows you didn't need to answer a yes-or-no question.

Equivalence 1: `IN` against `INNER JOIN` (and the real difference)

The question: customers who have placed at least one order.

-- Version A: subquery
SELECT c.id, c.name || ' ' || c.last_name AS customer, c.country
FROM customers AS c
WHERE c.id IN (SELECT customer_id FROM orders)
ORDER BY c.id;

-- Version B: INNER JOIN
SELECT c.id, c.name || ' ' || c.last_name AS customer, c.country
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id;

They look like the same query. They aren't. Version A returns one row per customer:

id	customer	country
1	Lucía Martínez Soler	Spain
2	Carlos Ferrer Ibáñez	Spain
3	Marta Sanchis Gil	Spain
4	Javier Ortega Ruiz	Spain

(First 4 of 12 rows.)

	Version A (`IN`)	Version B (`JOIN`)
Rows returned	12	20
Distinct customers	12	12
Lucía Martínez Soler appears…	1 time	3 times

Version B returns the 20 rows of orders with the customer's data repeated:

id	customer	country
1	Lucía Martínez Soler	Spain
1	Lucía Martínez Soler	Spain
1	Lucía Martínez Soler	Spain
2	Carlos Ferrer Ibáñez	Spain
2	Carlos Ferrer Ibáñez	Spain
3	Marta Sanchis Gil	Spain

(First 6 of 20 rows.) This is the underlying difference between the two tools, and it isn't a matter of taste:

IN is a membership test: it asks "is this value in the list?" and answers once per outer row. It can't multiply.
JOIN is a filtered product: it generates one row for every pair that matches. If the right-hand table has three matches, three rows come out.

To make them equal you have to add SELECT DISTINCT to version B — and that's where the DISTINCT gives the problem away. On top of that, DISTINCT forces a sort or the building of a hash table over the 20 rows already generated: you've done extra work only to undo it.

The case where the JOIN is the right answer: if you wanted "customers with the date of each order", the 20 rows are the result you're after, because order_date is a column of the other table. The base rule in action.

Equivalence 2: the four ways of answering "what doesn't match"

Here the thread opened in 03-07 and developed in 07-03 is finally closed. The question —customers who have never bought— has four forms, all correct, all identifying the same three customers (Núria, Hugo and Inés):

WHERE NOT EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id)   -- 1
WHERE c.id NOT IN (SELECT customer_id FROM orders)                        -- 2
LEFT JOIN orders AS o ON o.customer_id = c.id WHERE o.id IS NULL          -- 3
SELECT id FROM customers EXCEPT SELECT customer_id FROM orders            -- 4

	`NOT EXISTS`	`NOT IN`	Anti-join	`EXCEPT`
Safety with `NULL`	✅ Total	❌ 0 rows if there are nulls	✅ Total	✅ Total
Columns in the result	All the outer ones	All the outer ones	All the outer ones	❌ Only the compared ones
Duplicates	It creates none	It creates none	It creates none	It always removes them
Readability	✅ It reads like the question	✅ Very high… until it fails	⚠️ You have to know the idiom	✅ High
Typical plan in PostgreSQL	Anti-join	Worse: a non-anti-joinable filter	Anti-join	Sort + dedup
Portability	✅ Universal	✅ Universal	✅ Universal	⚠️ `MINUS` in Oracle
Verdict	By default	Only with a guaranteed `NOT NULL`	Good if you were already joining	Comparing sets

The recommendation, in one line: NOT EXISTS by default; anti-join if you were already joining those tables for another reason; EXCEPT to compare sets; NOT IN, never out of habit.

The reason for ruling out NOT IN as the default option isn't that it's worse today: it's that its correctness depends on a property of the schema —that the column is NOT NULL— which can change without anyone reviewing your queries. A migration allowing nulls in employee_id will raise no error and your reports will start returning zero rows.

Equivalence 3: scalar in the `SELECT` against `LEFT JOIN` + `GROUP BY`

You already saw both forms in 07-04. What matters here is the criterion:

Number of metrics	Preferable form	Why
1	Subquery in the `SELECT`	It reads at a glance; it doesn't touch the `FROM`
2	Either	A technical draw
3 or more	`LEFT JOIN` + `GROUP BY`	One pass instead of N; a single definition of the `FROM`

The single-metric case, head to head:

-- Version A: scalar in the SELECT
SELECT c.id, c.name,
       (SELECT COUNT(*) FROM orders AS o WHERE o.customer_id = c.id) AS orders
FROM customers AS c
ORDER BY c.id;

-- Version B: LEFT JOIN + GROUP BY
SELECT c.id, c.name, COUNT(o.id) AS orders
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;

id	name	orders
1	Lucía	3
2	Carlos	2
3	Marta	1
13	Núria	0

(4 rows out of 15; identical in both versions, including the zeros for customers 13, 14 and 15.) With one metric version A wins on clarity: it doesn't touch the FROM, it needs no GROUP BY and it can't inflate anything. And two nuances that usually decide the choice when there are more:

The subquery is immune to row multiplication. The LEFT JOIN with order_lines forces a COUNT(DISTINCT o.id) so as not to count 9 orders where there are 3.
The GROUP BY is immune to repetition. Adding a new metric is one line; with subqueries it's copying a four-line block and changing the aggregate.

There's a third case where the subquery clearly wins: when the metric can't be expressed as an aggregate over the JOIN. "The amount of each customer's last order" is no SUM or MAX of the joined columns: it demands sorting and cutting. That calls for a correlated subquery, LATERAL (07-04) or a window function (10-03).

Equivalence 4: correlated against derived table

The question: products above their category's average (07-02's 8 rows).

-- Version A: correlated
SELECT p.id, p.name, p.price
FROM products AS p
WHERE p.price > (SELECT AVG(p2.price) FROM products AS p2
                 WHERE p2.category_id = p.category_id);

-- Version B: aggregated derived table
SELECT p.id, p.name, p.price, ROUND(m.avg_price, 2) AS category_avg
FROM products AS p
JOIN (SELECT category_id, AVG(price) AS avg_price
      FROM products GROUP BY category_id) AS m ON m.category_id = p.category_id
WHERE p.price > m.avg_price;

The same 8 rows, and version B shows the threshold as well:

id	name	price	category_avg
1	Extra virgin olive oil 500 ml	12.50	6.18
3	Raw orange blossom honey 500 g	9.75	6.18
6	Aloe vera face cream 50 ml	18.90	11.54
8	Almond body oil 200 ml	14.25	11.54

(First 4 of 8 rows; products 10, 13, 15 and 19 close the list.) The differences:

	Correlated	Derived table
Times the average is computed	20 (conceptually)	6, one per category
Can you display the average?	Yes, by repeating the subquery	Yes, for free: it's a column
Duplicated expression	Yes, `SELECT` and `WHERE`	No: named once
Reads like the question	Yes, almost literally	Less direct
Scales to large tables	Worse	Better

With few rows and a single comparison, the correlated one is more readable. As soon as you want to display the reference value, reuse it or apply it over millions of rows, the derived table is better — and with three steps, 10-02's CTE beats both.

What the optimizer does underneath

Before deciding on performance, it's worth knowing that the engine doesn't run what you write, but an equivalent plan of its own choosing. PostgreSQL applies several automatic transformations:

What you write	What it usually turns it into	Does performance change?
`IN (SELECT ...)`	Semi-join (hash or merge)	No: it ends up like a deduplicated `JOIN`
`EXISTS (...)`	Semi-join	No
`NOT EXISTS (...)`	Anti-join	No
`NOT IN (SELECT ...)`	It can't: it has to preserve null semantics	Yes, for the worse
Simple derived table	It flattens it (subquery pull-up) into the outer query	No
Correlated scalar in the `SELECT`	Almost never transformed	Yes, for the worse with many rows
Correlated scalar in the `WHERE`	Sometimes yes, sometimes no	It depends

Three practical conclusions for the module follow from this:

IN and EXISTS against a JOIN are, performance-wise, practically the same thing in modern PostgreSQL. Choose on readability, not on speed.
NOT IN is slower, as well as dangerous, because the optimizer's hands are tied by null semantics.
Correlated subqueries in the SELECT are the construct with the most real risk: they're the ones most often executed literally, once per row.

Don't optimize blind. "Subqueries are slow" is a myth that's been circulating since MySQL 5.5, where they genuinely were. With PostgreSQL 16 it's almost never true, and with other engines it depends on the version. The only honest way to know is to measure: EXPLAIN ANALYZE shows you the real plan and the timings, and that's lesson 08-05. Until then, write the most readable version.

Readability and maintenance, a first-class criterion

When two forms have the same performance —which is the usual case—, readability isn't a secondary criterion: it's the criterion. A query is written once and read, debugged and modified dozens of times.

Compare. The nested version of "customers whose average order value beats the overall average", with three levels:

SELECT c.name, t.avg_order_value
FROM customers AS c
JOIN (SELECT o.customer_id, AVG(p.total) AS avg_order_value
      FROM (SELECT o2.id, o2.customer_id,
                   SUM(ol.quantity * ol.unit_price * (1 - ol.discount)) AS total
            FROM orders AS o2 JOIN order_lines AS ol ON ol.order_id = o2.id
            GROUP BY o2.id, o2.customer_id) AS p
      JOIN orders AS o ON o.id = p.id
      GROUP BY o.customer_id) AS t ON t.customer_id = c.id
WHERE t.avg_order_value > (SELECT AVG(x.total) FROM (SELECT SUM(...) AS total ...) AS x);

It reads from the inside outwards, the indentation eats half the screen and the order-total calculation is written twice. The same logic with CTEs:

WITH order_totals     AS (...),        -- each order's total, only once
     global_average   AS (...),        -- €36.40
     customer_average AS (...)         -- average per customer
SELECT ... FROM customer_average WHERE avg_order_value > (SELECT * FROM global_average);

Each step has a name, it reads top to bottom like a procedure, and order_totals is defined once and used twice. That's 10-02, and it's the reason this module keeps pointing there: subqueries solve the problem, CTEs leave it readable.

Three signs that a query is asking to be rewritten:

More than two levels of nesting. Count consecutive opening parentheses.
The same expression written two or more times. A SUM(...) repeated in the SELECT and in the HAVING is technical debt.
Three or more almost identical subqueries in the SELECT. That's a GROUP BY waiting to be born.

Guide table: I want X → write Y

I want…	Write
Columns from two related tables	`INNER JOIN`
Columns from A, and B's if they exist	`LEFT JOIN`
Rows of A that have a match in B, without duplicating	`EXISTS` (or `IN`)
Rows of A that have no match in B	`NOT EXISTS`
Compare each row with a global value	Scalar subquery in the `WHERE`
Compare each row with a value from its group	Correlated, or derived table + `JOIN`
Filter groups by a global value	Scalar subquery in the `HAVING`
One metric computed per row	Scalar in the `SELECT`
Three or more metrics per row	`LEFT JOIN` + `GROUP BY`
Aggregate over an aggregate	Derived table in the `FROM`
Metrics of different granularity	Two joined derived tables
The "top N" of each group	`LATERAL`, or a window function (10-03)
Reuse a calculation or chain 3+ steps	CTE with `WITH` (10-02)
A ranking, a position, a running total	Window function (10-03)

Common Mistakes and Tips

Using a JOIN for an existence question. It multiplies rows and forces you into a DISTINCT that hides the problem: 20 rows where you wanted 12.
Believing that "subqueries are slow". In PostgreSQL 16, IN and EXISTS become semi-joins. The myth comes from old engines and versions.
Rewriting for performance without measuring. Swapping a readable query for a cryptic one on a hunch is the worst possible trade: you lose readability and you may gain nothing (08-05).
Keeping NOT IN because "it works today". Its correctness depends on a property of the schema that can change without warning.
Nesting three levels when a CTE exists. It works, but nobody —you included— will be able to modify it six months from now.
Repeating the same expression in SELECT, WHERE and HAVING. Every copy is a chance for one to be updated and the others not.
Tip: write the version that looks like the question first. If the question says "customers who have not bought", write NOT EXISTS. The query that reads like the statement is the one hiding the fewest mistakes.
Tip: count the rows of each version before signing one off. If two "equivalent" forms return 12 and 20 rows, they weren't equivalent.
Tip: keep both versions when in doubt. Leave the alternative commented out with a note on why you chose the other. It's the cheapest documentation there is.

Exercises

Exercise 1

For each of these five questions, decide JOIN or subquery by applying the base rule, and justify it in one sentence. There's no need to write the full SQL.

Products with their category and their supplier.
Products that have received some review.
Orders whose amount beats the global average order value.
Customers with the number of orders they've placed.
Employees who have never handled an order.

Exercise 2

A colleague has written this for "the products that have been sold at some point":

-- ⚠️ Suspicious
SELECT DISTINCT p.id, p.name, p.price
FROM products AS p
JOIN order_lines AS ol ON ol.product_id = p.id
ORDER BY p.id;

Is the result correct? How many rows does it return before and after the DISTINCT?
Rewrite it with EXISTS and explain what you gain.
In what case would the JOIN be the correct form for a similar question?

Exercise 3

Take this query, which answers "customers with more than one order and their total revenue":

SELECT c.id, c.name,
       (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) AS orders,
       (SELECT ROUND(SUM(ol.quantity * ol.unit_price * (1 - ol.discount)), 2)
        FROM orders o JOIN order_lines ol ON ol.order_id = o.id
        WHERE o.customer_id = c.id) AS revenue
FROM customers c
WHERE (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) > 1
ORDER BY revenue DESC;

How many subquery runs does it involve?
Rewrite it with JOIN and GROUP BY, taking care with the count.
Give the result and say which version you'd defend in a code review.

Solutions

Solution 1

#	Question	Choice	Why
1	Products with category and supplier	`JOIN` (two of them)	The result displays columns from all three tables
2	Products with some review	Subquery (`EXISTS`)	Only columns from `products` are displayed; the `JOIN` would duplicate the oil, the rice and the cream
3	Orders above the average order value	Scalar subquery + derived table	The threshold is a computed value, not a table to join
4	Customers with their number of orders	Either	One metric: scalar in the `SELECT` or `LEFT JOIN` + `GROUP BY`. If three metrics were needed, `GROUP BY` without hesitation
5	Employees with no orders at all	Subquery (`NOT EXISTS`)	An absence question, and `employee_id` is nullable: `NOT IN` would give 0 rows

Solution 2

1. The result is correct, but by accident of the DISTINCT. Without it, the JOIN returns 47 rows —one per order line—, with the olive oil repeated 5 times and the rice 4. With DISTINCT there are 17 rows left, the 17 products sold. In other words: the engine generates 47 rows, sorts them or puts them into a hash table, and throws away 30. Work done just to undo it.

2. With EXISTS:

-- ✅ CORRECT
SELECT p.id, p.name, p.price
FROM products AS p
WHERE EXISTS (SELECT 1 FROM order_lines AS ol WHERE ol.product_id = p.id)
ORDER BY p.id;

17 rows straight away, without generating the 47 or deduplicating. What you gain: the result can't be duplicated by construction, the intent is explicit ("products that have some sale"), the EXISTS short-circuits at the first line found, and the DISTINCT disappears —along with it, the risk that somebody adds an order_lines column to the SELECT tomorrow and the DISTINCT stops deduplicating without anyone noticing.

3. The JOIN would be correct as soon as the question asked for something from order_lines: "products sold with the units of each sale" (47 rows, and they are the result), or "products sold with the total units" (17 rows, with a GROUP BY). As soon as you need data from the other table, the base rule rules.

Solution 3

1. The runs. Three subqueries per customer —two in the SELECT and one repeated in the WHERE— × 15 customers = 45, of which 15 are a COUNT computed twice per customer. That duplicated COUNT is the classic symptom: the WHERE doesn't see the SELECT's aliases (module 2's logical order), so the whole expression has to be repeated.

2. The rewrite:

-- ✅ A single pass
SELECT c.id,
       c.name,
       COUNT(DISTINCT o.id) AS orders,
       ROUND(SUM(ol.quantity * ol.unit_price * (1 - ol.discount)), 2) AS revenue
FROM customers AS c
JOIN orders      AS o  ON o.customer_id = c.id
JOIN order_lines AS ol ON ol.order_id   = o.id
GROUP BY c.id, c.name
HAVING COUNT(DISTINCT o.id) > 1
ORDER BY revenue DESC;

id	name	orders	revenue
7	Sofia	2	111.88
1	Lucía	3	107.60
9	Camille	2	70.87
4	Javier	2	62.93
2	Carlos	2	59.46
6	Pau	2	57.33
5	Ana	2	54.85

7 customers, the same ones that came out in 04-06 with HAVING COUNT(*) > 1. Two mandatory details: COUNT(DISTINCT o.id), because the second JOIN multiplies each order by its lines (Lucía would have 9 orders instead of 3); and INNER JOIN instead of LEFT, which is correct here because the > 1 condition already excludes anyone with 0.

3. Which one to defend. The GROUP BY version, without hesitation: one pass instead of 45, the amount expression written only once and the cut-off criterion visible in the HAVING next to the column it displays. The subquery version has a single real advantage —it needs no COUNT(DISTINCT), because it doesn't multiply rows— and that advantage doesn't make up for repeating the COUNT in two places. If the query grew to five metrics, the difference would stop being debatable.

(And the ideal version, with the order total computed once and named, arrives in 10-02.)

Module conclusion

You close module 7 with a criterion, not just with syntax:

The base rule: if you need columns from the other table, JOIN; if you only filter or compute a value, subquery. And if you write JOIN ... DISTINCT, you wanted EXISTS.
IN against INNER JOIN isn't a matter of style: it's 12 rows against 20. IN tests membership and answers once per row; the JOIN produces one row per pair.
The four ways of answering "what doesn't match" are settled: NOT EXISTS by default, anti-join if you were already joining those tables, EXCEPT to compare sets, and NOT IN never out of habit — because its correctness depends on a property of the schema that can change without warning.
One metric per row fits nicely in a scalar in the SELECT; three or more call for LEFT JOIN + GROUP BY, taking care with the COUNT(DISTINCT).
Correlated or derived table: the first reads like the question, the second computes six averages instead of twenty and throws in the reference column for free.
The optimizer rewrites IN, EXISTS and NOT EXISTS as semi-joins and anti-joins, so their performance is usually equivalent to the JOIN's; NOT IN and correlated subqueries in the SELECT are the ones you really pay for. And none of this is assumed: it's measured with EXPLAIN ANALYZE (08-05).
Readability is a first-class criterion: three levels of nesting or a repeated expression are signs that a CTE is due (10-02).

And with that module 7 closes. Look back at what you've gained in five lessons: you can tell a non-correlated subquery from a correlated one and you know the second is evaluated once per row; you recognise scalar, row and table subqueries, and the operators each one admits; you've finally solved the question 04-06 left pending —Julien, Sofia and Tiago beat the €36.40 average order value—; you've mastered EXISTS and NOT EXISTS, including relational division's double negation; you place subqueries in the SELECT, in the FROM, in the WHERE, in the HAVING and in module 5's statements; and with derived tables you've finally settled the shipping problem you'd been dragging since module 3: €727.95 + €118.25 = €846.20, squared to the cent.

With the seven modules you've covered, you can write almost any query GreenStore needs. And precisely for that reason, the important question changes. Up to now it's always been the same one: does this return what I want?. From here on it's another: how long does it take?. With 20 orders, 47 lines and 15 customers, everything you've written answers in milliseconds, and it makes absolutely no difference whether a subquery runs twenty times or once. With 20 million orders, that same query can take minutes, freeze an admin screen or bring down a nightly report. In module 8, Indexes and Performance, you'll learn what an index is and how it turns a full table scan into a targeted lookup, how to create and manage them, what types exist and —just as important— when not to index, the query optimization techniques, and EXPLAIN, the tool that will finally tell you, with data instead of hunches, what the engine is really doing with the SQL you write.

Subquery or JOIN: Which One to Choose

Contents

The base rule

Equivalence 1: `IN` against `INNER JOIN` (and the real difference)

Equivalence 2: the four ways of answering "what doesn't match"

Equivalence 3: scalar in the `SELECT` against `LEFT JOIN` + `GROUP BY`

Equivalence 4: correlated against derived table

What the optimizer does underneath

Readability and maintenance, a first-class criterion

Guide table: I want X → write Y

Common Mistakes and Tips

Exercises

Exercise 1

Exercise 2

Exercise 3

Solutions

Module conclusion

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL queries

Module 3: Working with multiple tables

Module 4: Advanced data filtering

Module 5: Data manipulation

Module 6: Advanced SQL functions

Module 7: Subqueries and nested queries

Module 8: Indexes and performance tuning

Module 9: Transactions and concurrency

Module 10: Advanced topics

Module 11: SQL in practice

Module 12: Final project

Subquery or JOIN: Which One to Choose

Contents

The base rule

Equivalence 1: IN against INNER JOIN (and the real difference)

Equivalence 2: the four ways of answering "what doesn't match"

Equivalence 3: scalar in the SELECT against LEFT JOIN + GROUP BY

Equivalence 4: correlated against derived table

What the optimizer does underneath

Readability and maintenance, a first-class criterion

Guide table: I want X → write Y

Common Mistakes and Tips

Exercises

Exercise 1

Exercise 2

Exercise 3

Solutions

Module conclusion

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL queries

Module 3: Working with multiple tables

Module 4: Advanced data filtering

Module 5: Data manipulation

Module 6: Advanced SQL functions

Module 7: Subqueries and nested queries

Module 8: Indexes and performance tuning

Module 9: Transactions and concurrency

Module 10: Advanced topics

Module 11: SQL in practice

Module 12: Final project

Equivalence 1: `IN` against `INNER JOIN` (and the real difference)

Equivalence 3: scalar in the `SELECT` against `LEFT JOIN` + `GROUP BY`