SQL Joins Explained: INNER, LEFT, RIGHT and FULL — Plus the Mistakes That Give Wrong Results
What each SQL join returns, with one small dataset used throughout — and the silent bugs that cause wrong reports: WHERE clauses that cancel a LEFT JOIN, COUNT(*) counting missing rows, fan-out inflating SUMs and NOT IN with NULLs.
By Software Side Hustles Editorial Team · Published · Updated · 5 min read
Quick answer: INNER JOIN returns only rows that match in both tables. LEFT JOIN returns every row from the left table, with NULLs where the right table has no match. RIGHT JOIN is the mirror image, and FULL OUTER JOIN keeps unmatched rows from both sides. Most join bugs come not from choosing the wrong join but from what happens next — a WHERE on the right table, COUNT(*), or duplicated rows.
The example data
-- customers -- orders-- id | name -- id | customer_id | total-- 1 | Ada -- 10 | 1 | 50-- 2 | Linus -- 11 | 1 | 70-- 3 | Grace -- 12 | 2 | 30-- -- 13 | NULL | 90 (guest checkout)Grace has no orders. Order 13 has no customer.
INNER JOIN: only matches
SELECT c.name, o.id, o.totalFROM customers cJOIN orders o ON o.customer_id = c.id;| name | id | total |
|---|---|---|
| Ada | 10 | 50 |
| Ada | 11 | 70 |
| Linus | 12 | 30 |
Grace disappears (no orders) and so does order 13 (no customer). JOIN on its own means INNER JOIN.
LEFT JOIN: everything from the left
SELECT c.name, o.id, o.totalFROM customers cLEFT JOIN orders o ON o.customer_id = c.id;| name | id | total |
|---|---|---|
| Ada | 10 | 50 |
| Ada | 11 | 70 |
| Linus | 12 | 30 |
| Grace | NULL | NULL |
Use LEFT JOIN whenever the question includes "…including those with none": customers including those with zero orders, products including those never sold.
RIGHT, FULL OUTER and CROSS JOIN
RIGHT JOINkeeps every row from the right table.customers RIGHT JOIN ordersincludes order 13 with aNULLname. Most teams rewrite it as aLEFT JOINwith the tables swapped, which reads more naturally.FULL OUTER JOINkeeps unmatched rows from both: Grace and order 13. PostgreSQL, SQL Server, Oracle and SQLite (3.39+) support it; MySQL does not, so you emulate it with aLEFT JOINUNIONaRIGHT JOIN.CROSS JOINpairs every row with every row (3 customers × 4 orders = 12 rows). Useful for generating combinations such as every product for every month; disastrous when it happens accidentally through a missingONcondition.- Self join joins a table to itself, e.g. employees to their managers:
FROM employees e LEFT JOIN employees m ON m.id = e.manager_id.
Mistake 1: a WHERE clause that cancels the LEFT JOIN
"All customers, with their orders over 40":
SELECT c.name, o.idFROM customers cLEFT JOIN orders o ON o.customer_id = c.idWHERE o.total > 40; -- Grace and Linus vanishFor Grace, o.total is NULL, and NULL > 40 is not true, so WHERE filters her out. Linus's only order is 30, so he goes too. The LEFT JOIN has quietly become an INNER JOIN. Put conditions on the right-hand table in the ON clause:
SELECT c.name, o.idFROM customers cLEFT JOIN orders o ON o.customer_id = c.id AND o.total > 40;-- Ada 10, Ada 11, Grace NULL, Linus NULLRule of thumb: conditions about which right rows to attach go in ON; conditions about which final rows to keep go in WHERE.
Mistake 2: COUNT(*) counts the missing row
"Orders per customer, including zero":
SELECT c.name, COUNT(*) AS orders -- Grace: 1 (wrong)FROM customers c LEFT JOIN orders o ON o.customer_id = c.idGROUP BY c.name;
SELECT c.name, COUNT(o.id) AS orders -- Grace: 0 (right)FROM customers c LEFT JOIN orders o ON o.customer_id = c.idGROUP BY c.name;COUNT(*) counts rows, and Grace has one row (full of NULLs). COUNT(column) ignores NULLs. Similarly, SUM(o.total) returns NULL, not 0, for Grace — wrap it: COALESCE(SUM(o.total), 0).
Mistake 3: fan-out inflates totals
Joining a one-to-many relationship repeats the "one" side for each match. Aggregating after joining two separate one-to-many tables multiplies rows:
-- Each customer has several orders AND several support ticketsSELECT c.name, SUM(o.total), COUNT(t.id)FROM customers cLEFT JOIN orders o ON o.customer_id = c.idLEFT JOIN tickets t ON t.customer_id = c.idGROUP BY c.name; -- totals multiplied by the number of ticketsAggregate each child table in a subquery or CTE first, then join the results:
WITH order_totals AS ( SELECT customer_id, SUM(total) AS revenue FROM orders GROUP BY customer_id), ticket_counts AS ( SELECT customer_id, COUNT(*) AS tickets FROM tickets GROUP BY customer_id)SELECT c.name, COALESCE(ot.revenue, 0), COALESCE(tc.tickets, 0)FROM customers cLEFT JOIN order_totals ot ON ot.customer_id = c.idLEFT JOIN ticket_counts tc ON tc.customer_id = c.id;If adding DISTINCT "fixes" a report, suspect fan-out — DISTINCT usually hides the problem rather than solving it.
Mistake 4: NOT IN with NULLs returns nothing
"Customers with no orders":
SELECT name FROM customersWHERE id NOT IN (SELECT customer_id FROM orders); -- returns no rows at all!Order 13 has customer_id = NULL. x NOT IN (1, 2, NULL) can never be true, because comparing with NULL is unknown. Use NOT EXISTS, which handles NULLs correctly:
SELECT name FROM customers cWHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id); -- GraceThe "anti-join" form LEFT JOIN … WHERE o.id IS NULL also works.
Joins and performance
Index the columns you join on, especially foreign keys (orders.customer_id); PostgreSQL does not create those indexes automatically. Use EXPLAIN (or EXPLAIN ANALYZE) to see whether the database uses a nested loop, hash or merge join. And avoid issuing one query per row from application code — the "N+1 queries" problem — when a single join would do. See Big O notation explained.
Frequently asked questions
What is the difference between JOIN and INNER JOIN?
None. INNER is optional.
Is LEFT JOIN the same as LEFT OUTER JOIN?
Yes. OUTER is optional.
Which is faster, a JOIN or a subquery?
Modern optimisers often produce the same plan for both. Write the clearest query, then check EXPLAIN if it is slow.
Why does my join return more rows than the table has?
The join condition matches several rows on the other side (one-to-many), or the ON condition is incomplete and creates a partial cross join.
Where this skill pays off
Our response evaluation lab includes "count orders per customer, including zero" — a request where AI answers regularly reach for COUNT(*). Our free VIP customers report review tests similar reasoning. Catching wrong-but-plausible SQL is valuable paid evaluation work; see how to evaluate AI-generated code.