SQL JOINs explained: INNER, LEFT and anti-joins
A JOIN lines up rows from two tables using a condition, usually a foreign key matching a primary key. The type of join decides what happens to rows that have no match.
INNER JOIN: only rows that match
INNER JOIN (or just JOIN) keeps a row only when the ON condition finds a partner in the other table. A customer with no orders disappears from the result.
SELECT c.name, o.amount FROM customers c JOIN orders o ON o.customer_id = c.id;
LEFT JOIN: keep every row on the left
LEFT JOIN keeps every row from the first table. When there's no match, the columns from the second table come back as NULL. Use it whenever 'every customer' or 'every user' appears in the question.
SELECT u.username, s.plan FROM users u LEFT JOIN subscriptions s ON s.user_id = u.id;
Anti-join: rows with no match
To find users who never subscribed, LEFT JOIN and keep the rows where the right side is NULL. NOT EXISTS does the same thing and reads well in interviews.
SELECT u.username FROM users u LEFT JOIN subscriptions s ON s.user_id = u.id WHERE s.id IS NULL;
Filters in ON vs WHERE
With a LEFT JOIN, a condition on the right table belongs in ON. Put it in WHERE and the NULL rows fail the test, which quietly turns your LEFT JOIN back into an INNER JOIN.
Try it
This runs real PostgreSQL in your browser on the practice tables. Change it and run it again.
Starting Postgres in your browser…
Real Postgres in your browser. Edit the query and run it, or open it in the full playground.
Common mistakes
- Using INNER JOIN when the question says 'every' or 'including those with none'.
- Filtering the right table in WHERE after a LEFT JOIN (move it into ON).
- COUNT(*) after a LEFT JOIN: it counts the NULL row as 1. Count a right-table column instead, like COUNT(o.id).
- Joining on the wrong key (s.id = u.id instead of s.user_id = u.id), which still runs but pairs the wrong rows.
Practice drills
Reading it isn't enough. Solve these and it'll still be there in an interview.
- Orders with customer namesINNER JOINFree
- Every user and their planINNER JOIN vs LEFT JOINFree
- Users who never subscribedLEFT JOIN · IS NULLFree
- Revenue by countryJOIN + GROUP BYFree
- Plan behind each purchaseThree-table JOINFree
- Order count, including zeroLEFT JOIN · COUNT(col) vs COUNT(*)Free
- Customers with a big orderIN (subquery) · EXISTSFree
- Purchase date for every userLEFT JOIN · filter in ON vs WHEREFree
- Total touchpoints per userUNION vs UNION ALLFree
- Earning more than their managerSelf-joinPro
- Fast convertersJOIN + date differencePro
- Daily active users, zero days includedgenerate_series · LEFT JOIN · filling date gapsPro
- Year-over-year growth by quarterSelf-join on period · YoY %Pro
- Plan at time of purchaseAs-of join · JOIN LATERALPro
- Month-1 retention by signup cohortCohorts · date_trunc · LEFT JOINPro
- Monthly churn rateAnti-join across months · churnPro
- Pricing to checkout within an hourFunnel with a time window · range joinPro