SQL JOIN vs LEFT JOIN with examples

JOIN (an inner join) keeps only rows that have a match in both tables. LEFT JOIN keeps every row from the first table and fills the other table's columns with NULL when there is no match.

Both joins on the same data

CREATE TABLE customers (customer_id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY, customer_id INTEGER, total REAL);

INSERT INTO customers VALUES (1,'Ana Silva'),(2,'Tom Reyes'),(3,'Grace Kim');
INSERT INTO orders VALUES (101,1,40.0),(102,1,25.5),(103,3,18.0);

-- Inner join
SELECT c.name, o.order_id, o.total
FROM customers AS c
JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

-- Left join
SELECT c.name, o.order_id, o.total
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

Output of the first query:

name       order_id  total
---------  --------  -----
Ana Silva  101       40.0
Ana Silva  102       25.5
Grace Kim  103       18.0

Output of the second query (SQLite shell with .nullvalue NULL, so missing values are visible):

name       order_id  total
---------  --------  -----
Ana Silva  101       40.0
Ana Silva  102       25.5
Tom Reyes  NULL      NULL
Grace Kim  103       18.0

What changed

Tom Reyes has no orders. The inner join drops him because there is nothing to pair him with. The left join keeps him, because customers is written first, after FROM. That is the "left" table.

Ana appears twice in both results because she has two orders: a join returns one row per matching pair.

To find only the customers with no orders, add a filter on a right-table column that can never be NULL in a real match, such as the primary key:

SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

This returns just Tom Reyes.

Common mistake

Putting a condition on the right table in WHERE:

SELECT c.name, o.order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.total > 20;

Tom's total is NULL, so WHERE removes his row and your left join behaves like an inner join. Move the condition into the ON to keep every customer:

LEFT JOIN orders AS o ON o.customer_id = c.customer_id AND o.total > 20

Also, when counting orders per customer after a left join, use COUNT(o.order_id), not COUNT(*). Tom would otherwise count as 1.

Use JOIN when you only want matched rows, and LEFT JOIN when you need every row from the first table, with or without a match.