Lesson 2: Inner and Left Joins, Counting Rows

Why your row count is a clue

In Level 1 you learned to ask "what does one row mean?" for a table and for a result. Joins are where the answer changes without warning. A join can drop rows, keep rows and fill the gaps with NULLs, or multiply rows. Experienced analysts don't guess which of these happened. They count.

Think of a join as a guest list checked against the bookings. An inner join only lets in people who are on both lists. A left join lets in everyone on the left-hand list. If someone has no booking, they still get in, but their booking details are left blank (NULL).

The dataset

Run this in SQLite (a browser tool works fine), DB Fiddle or PostgreSQL:

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,  -- grain: one row per customer
  name        TEXT NOT NULL
);

CREATE TABLE orders (
  order_id    INTEGER PRIMARY KEY,  -- grain: one row per order
  customer_id INTEGER,              -- which customer placed it
  amount      NUMERIC(8,2)
);

INSERT INTO customers VALUES (1,'Ana'), (2,'Ben'), (3,'Cara'), (4,'Dev');

INSERT INTO orders VALUES
  (101, 1, 50.00),
  (102, 1, 20.00),
  (103, 2, 35.00),
  (104, 4, 15.00),
  (105, 9, 40.00);  -- customer 9 does not exist: an orphan order

Notice two things before you join anything. Cara has no orders. Order 105 points to a customer who isn't in customers. Real data has both problems all the time.

Step 1: Count the inputs

SELECT COUNT(*) FROM customers;  -- 4
SELECT COUNT(*) FROM orders;     -- 5

Write these numbers down. Every row count after a join should be explained using them.

Step 2: Inner join, then count

SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers AS c
JOIN orders    AS o            -- plain JOIN means INNER JOIN
  ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

4 rows. An inner join returns only the pairs where the ON condition is true. Cara has no matching order, so she disappears. Order 105 has no matching customer, so it disappears too. The grain of this result is one row per matched order. Ana shows up twice because she has two orders.

Now account for every row:

• From customers' side: 4 customers, minus Cara (no match), leaves 3 distinct customers. Ana has 2 orders, so that's 4 rows.
• From orders' side: 5 orders, minus order 105 (orphan), leaves 4 rows. ✔ Both sides agree.

-- Quick check: how many distinct customers survived?
SELECT COUNT(DISTINCT c.customer_id)   -- 3, so one customer was dropped
FROM customers c JOIN orders o ON o.customer_id = c.customer_id;

Gotcha: The inner join raised no error and printed no warning. It just quietly dropped a customer and £40 of orders. When an inner-join report shows fewer customers than your CRM, this is usually why.

Step 3: Left join, then count

SELECT c.customer_id, c.name, o.order_id, o.amount
FROM customers AS c          -- the LEFT table: every row is kept
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

5 rows. A left join first finds the same matches as the inner join (4 rows). Then it adds one row for each left-hand row that matched nothing, with NULL in every column from the right table. Here that's Cara.

The arithmetic always works out:

left join rows = inner join rows + unmatched left rows
             5 = 4               + 1 (Cara)

If your numbers don't add up this way, stop and find out why before you write any more SQL.

Note: Order 105 is still missing. A left join only protects rows from the left table. To keep orders, put orders on the left.

Step 4: Find the unmatched rows

After a left join, an unmatched row has NULL in the right table's columns. Filter for NULL in the right table's key:

-- Customers with no orders
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;      -- returns Cara

-- Orders with no valid customer: flip which table is on the left
SELECT o.order_id, o.customer_id, o.amount
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;   -- returns order 105

Why test the key? A primary key is never NULL in a real row. So a NULL there can only mean "no match". A column like amount might be NULL even on a real order, so testing it would mix real orders in with the gaps.

Gotcha: Write IS NULL, never = NULL. In SQL, comparing anything to NULL with = gives "unknown" rather than true, so WHERE o.order_id = NULL returns no rows and suggests every customer matched.

Dialect notes

• JOIN and INNER JOIN mean the same thing. LEFT JOIN and LEFT OUTER JOIN also mean the same thing. This holds in SQLite, PostgreSQL, MySQL, SQL Server and BigQuery, because the words INNER and OUTER are optional.
• In practice, write LEFT JOIN explicitly and always alias your tables. When someone else reads the query, the join type is the first thing they need to see.

Key takeaways

• Count each input table before you join and the output after. Then account for every difference.
• An inner join keeps only matched pairs. Customers with no orders and orders with no customer both disappear without any warning.
• A left join keeps every left-hand row. Unmatched rows get NULLs in the right table's columns, so left join rows = inner join rows + unmatched left rows.
• To find unmatched rows, left join and filter WHERE right_table.key IS NULL. Use the key, and use IS NULL, never = NULL.
• A left join only protects the table on the left. To find orphans in the other table, swap which table is on the left.