SoftwareSideHustles
Concepts

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

sql
-- 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

sql
SELECT c.name, o.id, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;
nameidtotal
Ada1050
Ada1170
Linus1230

Grace disappears (no orders) and so does order 13 (no customer). JOIN on its own means INNER JOIN.

LEFT JOIN: everything from the left

sql
SELECT c.name, o.id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id;
nameidtotal
Ada1050
Ada1170
Linus1230
GraceNULLNULL

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 JOIN keeps every row from the right table. customers RIGHT JOIN orders includes order 13 with a NULL name. Most teams rewrite it as a LEFT JOIN with the tables swapped, which reads more naturally.
  • FULL OUTER JOIN keeps 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 a LEFT JOIN UNION a RIGHT JOIN.
  • CROSS JOIN pairs 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 missing ON condition.
  • 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":

sql
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.total > 40; -- Grace and Linus vanish

For 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:

sql
SELECT c.name, o.id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.total > 40;
-- Ada 10, Ada 11, Grace NULL, Linus NULL

Rule 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":

sql
SELECT c.name, COUNT(*) AS orders -- Grace: 1 (wrong)
FROM customers c LEFT JOIN orders o ON o.customer_id = c.id
GROUP 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.id
GROUP 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:

sql
-- Each customer has several orders AND several support tickets
SELECT c.name, SUM(o.total), COUNT(t.id)
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
LEFT JOIN tickets t ON t.customer_id = c.id
GROUP BY c.name; -- totals multiplied by the number of tickets

Aggregate each child table in a subquery or CTE first, then join the results:

sql
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 c
LEFT JOIN order_totals ot ON ot.customer_id = c.id
LEFT 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":

sql
SELECT name FROM customers
WHERE 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:

sql
SELECT name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id); -- Grace

The "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.

Cookies on this site

We use essential cookies to run the questionnaire, practice labs and sign-in. With your permission we'd also like to use analytics to understand which pages help people, and advertising cookies to measure our Google Ads. Rejecting never limits any feature. Cookie policy