15. INNER JOIN and LEFT JOIN (The Two You Need First)

15.3 How to Write JOIN … ON Correctly (Aliases, Filters, Never Without ON)

By Ravindra Bagale Last updated: 31 October 2026

Why. Small mistakes in how a join is written can give far too many rows, or quietly remove the rows a LEFT JOIN was meant to keep.

What. An alias is a short name for a table, like s for shipments, and it makes clear which table each column comes from. Without an ON condition, SQLite pairs every row with every row: 10 shipments and 5 hubs give 50 rows. With a LEFT JOIN, a condition on the right table placed in ON only decides what matches, so all left rows stay. The same condition in WHERE runs after the join and removes left rows whose right columns are NULL. If both tables have a column with the same name, give each one a different alias in SELECT.

How. See what happens without ON, then put the same date filter in ON and in WHERE and compare the results.

Steps

Run the first example and see 50 rows instead of about 10.
Run the second example with the date filter inside ON.
Run the third example with the same filter in WHERE.
Compare: the ON version keeps all 6 customers in 8 rows, the WHERE version shows only 4 customers in 6 rows.

Examples

Example 1 · dataset: swiftship_logistics

SELECT COUNT(*) AS rows_without_on
FROM shipments, hubs;

Output

rows_without_on
50

Meaning. 50 rows: every shipment was paired with every hub. These pairs mean nothing, so always write the join condition.

Example 2 · dataset: swiftship_logistics

SELECT c.customer_name, s.shipment_id, s.shipped_on
FROM customers AS c
LEFT JOIN shipments AS s
  ON s.customer_id = c.customer_id
 AND s.shipped_on >= '2026-10-05'
ORDER BY c.customer_id, s.shipment_id;

Output

customer_name	shipment_id	shipped_on
Kavya Traders	NULL	NULL
Desai Pharma	1008	2026-10-08
Al Noor Foods	1005	2026-10-06
Thames Books	1006	2026-10-05
Thames Books	1009	2026-10-09
Lotus Crafts	1007	2026-10-07
Lotus Crafts	1010	2026-10-10
Marina Spices	NULL	NULL

Meaning. 8 rows and all 6 customers. Kavya Traders has no shipment on or after 5 October, and Marina Spices has none at all, so both show NULL shipment columns.

Example 3 · dataset: swiftship_logistics

SELECT c.customer_name, s.shipment_id, s.shipped_on
FROM customers AS c
LEFT JOIN shipments AS s
  ON s.customer_id = c.customer_id
WHERE s.shipped_on >= '2026-10-05'
ORDER BY c.customer_id, s.shipment_id;

Output

customer_name	shipment_id	shipped_on
Desai Pharma	1008	2026-10-08
Al Noor Foods	1005	2026-10-06
Thames Books	1006	2026-10-05
Thames Books	1009	2026-10-09
Lotus Crafts	1007	2026-10-07
Lotus Crafts	1010	2026-10-10

Meaning. Only 6 rows. Kavya Traders and Marina Spices disappear, because their NULL dates fail the WHERE test. This LEFT JOIN now behaves like an INNER JOIN.

Ravindra Bagale's Tip

With a LEFT JOIN, put conditions on the right-hand table in ON when you want to keep every left row. Use WHERE only when you really want to remove rows.