SELECT, WHERE & JOIN

Description

Most of what you'll ever ask a database comes down to three moves: pick the rows you want (WHERE), pick the columns you want (SELECT), and bring related tables together (JOIN). They're the first SQL anyone learns, and they're also where most real SQL bugs live - because a wrong query rarely errors. It runs, returns rows that look plausible, and quietly answers a different question.

Here's a small example. "Which customers have never placed an order?" Both of these look reasonable:

-- Attempt 1
SELECT c.name
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- Attempt 2
SELECT name
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

On the sample data below, both return zero rows, with no error. The right answer is Ben and Dana. By the end of this lesson you'll be able to say exactly why each attempt fails, and write the version that works.

We'll start simple and get more precise as we go:

1. The order a query really runs in - the one idea that explains most surprises.
2. SELECT and WHERE, and what NULL (a missing value) does to a filter.
3. Joins: inner, left, and the "has a match" / "has no match" questions.
4. GROUP BY, and how a join can silently double your totals.
5. The common mistakes, and which phrasing to prefer.

Every result quoted below was run in PostgreSQL, on one small dataset used throughout:

customers

orders (order 14 is a guest checkout, with no customer)

tickets (support tickets): Asha has three (ids 100-102), Chen has one (103).

The pattern: read a query in the order it runs

A query is written SELECT-first but evaluated in a different logical order. Each step receives the rows the previous step produced:

1. FROM / JOIN - build the combined rows
2. WHERE - keep the rows whose condition is true
3. GROUP BY - collapse rows into groups
4. HAVING - keep the groups whose condition is true
5. SELECT - compute the output columns and their aliases
6. DISTINCT - remove duplicate output rows
7. ORDER BY - sort
8. LIMIT / OFFSET - cut

Rules you can derive instead of memorizing:

• WHERE can't use a SELECT alias - it doesn't exist yet (ERROR: column "gross" does not exist). ORDER BY can, because it runs after SELECT.
• WHERE can't use aggregates (ERROR: aggregate functions are not allowed in WHERE) - there are no groups yet. Conditions on groups go in HAVING.
• Attempt 1: the JOIN step already removed the customers without orders, so WHERE o.id IS NULL has nothing left to find.

SELECT and WHERE

SELECT id, total * 1.18 AS gross
FROM orders
WHERE status IN ('paid', 'shipped')
  AND created_at >= '2026-01-01' AND created_at < '2026-02-01'
ORDER BY gross DESC
LIMIT 10;                       -- 12 (472.00), 10 (295.00), 14 (88.50)

• AND binds tighter than OR. status = 'paid' OR status = 'shipped' AND total > 100 means paid OR (shipped AND total > 100) - every paid order, even the 75 one: 10, 12 and 14. Use parentheses, or IN.
• BETWEEN a AND b includes both ends. On a timestamp, BETWEEN '2026-01-01' AND '2026-01-31' stops at midnight starting January 31 and misses orders 11 and 14; widening it to '2026-02-01' wrongly takes order 13, placed at exactly midnight. Use a half-open range: >= '2026-01-01' AND < '2026-02-01'.
• LIKE: % matches any run of characters, _ exactly one. It is case-sensitive in PostgreSQL (ILIKE isn't): name LIKE '_a%' matches only Dana.
• No ORDER BY, no order. Rows come back in whatever order the plan produces, so LIMIT 10 without ORDER BY returns some 10 rows - which can change between runs.
• DISTINCT applies to the whole selected row, not the first column.

NULL: the third truth value

NULL means "unknown", and a comparison with an unknown value is unknown - not true and not false. WHERE keeps only the rows where the condition is true.

id NOT IN (1, NULL) expands to id <> 1 AND id <> NULL; the second part is unknown, so the whole condition can't be true. That's Attempt 2's bug: SELECT customer_id FROM orders contains a NULL. The fix is NOT EXISTS, below.

NULLs are not treated as unknown in three places: DISTINCT and GROUP BY put all NULLs into one group, and ORDER BY sorts them together - last in ascending order in PostgreSQL, first in descending (MySQL and SQL Server put them first in ascending order; write NULLS LAST when it matters).

JOINs

SELECT c.name, o.id AS order_id
FROM customers c
JOIN orders o ON o.customer_id = c.id;          -- INNER: only matching pairs

That returns (Asha, 10), (Asha, 11), (Chen, 12), (Chen, 13). Ben and Dana have no match and vanish; guest order 14 has no customer and vanishes too. A customer appears once per matching order - joins repeat the "one" side of a one-to-many.

SELECT c.name, o.id AS order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;     -- every customer; NULLs where no order

That returns 6 rows: Asha twice, Ben with NULL, Chen twice, Dana with NULL. Side by side, on the rows themselves (not Venn circles - a join pairs rows, it doesn't intersect sets):

The two correct answers to "never ordered" follow from it:

-- anti-join with LEFT JOIN: keep the rows the join had to fill with NULLs
SELECT c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- NOT EXISTS: NULL-safe, and reads like the question
SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Both return Ben and Dana. Test a column that can't be NULL in a real match, such as the primary key.

ON vs. WHERE in an outer join. "Every customer, with their paid orders":

... LEFT JOIN orders o ON o.customer_id = c.id AND o.status = 'paid'   -- 4 rows: Ben and Dana kept, with NULLs
... LEFT JOIN orders o ON o.customer_id = c.id WHERE o.status = 'paid' -- 2 rows: the NULL-filled rows fail WHERE

A WHERE condition on the right-hand table of a LEFT JOIN removes exactly the rows the LEFT JOIN added, turning it back into an inner join.

Which join do I need?

RIGHT JOIN exists too; it's a LEFT JOIN with the tables swapped, and most teams write the LEFT form so every query reads in the same direction.

Second flavor: GROUP BY and the fan-out trap

SELECT c.name, COUNT(o.id) AS orders, COALESCE(SUM(o.total), 0) AS spent
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY spent DESC;            -- Chen 2 / 450, Asha 2 / 350, then Ben and Dana 0 / 0

• Every selected column must be grouped or aggregated. SELECT customer_id, status, COUNT(*) FROM orders GROUP BY customer_id fails: which of a customer's statuses would it show? (PostgreSQL does allow c.name when grouping by c.id, because id is the primary key and so determines the name. MySQL with ONLY_FULL_GROUP_BY turned off returns an arbitrary value instead of an error.)
• COUNT(*) counts rows; COUNT(col) counts non-NULL values. After the LEFT JOIN, Ben has one row with a NULL order, so COUNT(*) says 1 while COUNT(o.id) correctly says 0. On customers: COUNT(*) is 4, COUNT(city) is 3, COUNT(DISTINCT city) is 2.
• Aggregates over no rows: SUM, AVG, MIN and MAX of nothing are NULL, not 0 (COUNT is 0) - hence the COALESCE.
• WHERE filters rows before grouping, HAVING filters groups after: WHERE o.status = 'paid' ... GROUP BY c.id HAVING SUM(o.total) > 300 returns Chen (400).

Fan-out. Join two independent one-to-many tables to the same parent, and they multiply:

SELECT c.name, SUM(o.total) AS spent, COUNT(t.id) AS tickets
FROM customers c
JOIN orders  o ON o.customer_id = c.id
JOIN tickets t ON t.customer_id = c.id
GROUP BY c.id, c.name;          -- Asha: 1050 and 6 (really 350 and 3); Chen: 450 and 2

Asha's 2 orders × 3 tickets make 6 joined rows, so each order total is counted three times and each ticket twice. Aggregate each child table on its own first, then join the one-row-per-customer results.

Where it breaks

• NOT IN with a NULL anywhere in the list - no rows, silently.
• A WHERE condition on the optional side of a LEFT JOIN - the "including those with none" rows vanish.
• COUNT(*) after a LEFT JOIN - customers with nothing count as 1.
• Joining two one-to-many children - sums and counts multiply.
• OR without parentheses; BETWEEN on timestamps.
• = NULL matching nothing, and <> value quietly dropping NULL rows.
• A join on the wrong column - plausible pairs, wrong data.

Tradeoffs

Going further. The deep dive (next tab) leaves the logical model and opens up the engine: the three algorithms a database uses to run a join, how it chooses between them from row estimates, why NOT EXISTS and NOT IN get different plans, and what goes wrong at scale.

Check yourself

Try to answer before opening each one - recalling beats re-reading.

1. SELECT c.name, COUNT(*) FROM customers c LEFT JOIN orders o ON o.customer_id = c.id GROUP BY c.id, c.name reports Dana with 1 order. She has none. Why, and what's the fix?

2. "Every customer with their orders, excluding refunds" is written as a LEFT JOIN plus WHERE o.status <> 'refunded'. Which customers disappear, and why?

3. SELECT customer_id, SUM(total) FROM orders GROUP BY customer_id returns three rows, not two. Where does the third come from?

Key takeaways

• Read a query in its logical order - FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, LIMIT - and most surprises explain themselves.
• NULL is unknown: use IS NULL, remember <> drops NULLs, and never use NOT IN on a column that can be NULL - use NOT EXISTS.
• Inner joins drop unmatched rows and repeat matched ones; LEFT JOIN keeps every left row, and conditions on the right table belong in ON.
• COUNT(col) rather than COUNT(*) after a LEFT JOIN; COALESCE sums that may cover no rows.
• Two one-to-many children multiply each other - aggregate before joining.
• No ORDER BY, no order - and SQL bugs rarely raise errors, so check results against a case you can work out by hand.