Development

SQL Joins Explained with Real Questions

INNER, LEFT and anti-joins explained through the question each one answers, plus the WHERE-versus-ON mistake that silently drops rows.

SmartCampus Buddy TeamSeptember 9, 20266 min read

A join combines rows from two tables based on a condition. The easiest way to choose the right one is to decide which question you are asking. The examples use customers(id, name) and orders(id, customer_id, total).

INNER JOIN: only the matches

"Which customers have placed orders, and what were they?" An INNER JOIN returns only rows that match on both sides. A customer without orders does not appear.

LEFT JOIN: everything on the left

"List every customer and their orders, if any." A LEFT JOIN keeps every row from the left table. Where no order matches, the order columns are NULL.

The anti-join: finding what is missing

"Which customers have never ordered?" Use a LEFT JOIN and keep only the rows where the right side is missing:

SELECT c.*
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

WHERE versus ON in a LEFT JOIN

Putting a condition on the right table in WHERE removes the NULL rows, which quietly turns your LEFT JOIN into an INNER JOIN. If you want to keep every customer but only join large orders, put the condition in the ON clause:

LEFT JOIN orders o ON o.customer_id = c.id AND o.total > 100

Joins and aggregates

Aggregate functions ignore NULLs, except COUNT(*). After a LEFT JOIN, COUNT(o.id) gives the number of orders per customer (zero for customers with none), while COUNT(*) would give at least 1.

Key takeaways

  • Pick the join by the question you are asking.
  • Missing data shows up as NULL after outer joins.
  • Filter the right table in ON, not WHERE, to preserve unmatched rows.
  • Index the columns you join on.

Related articles