SQLite LEFT JOIN: Keep Every Row from the Left Table

LEFT JOIN Keeps Everything on the Left

INNER JOIN only returns rows where both sides match. That's often what you want — but not always. Sometimes "no match" is itself the answer you're looking for: users who haven't placed an order, products that have never sold, posts with zero comments. For those, you need LEFT JOIN.

A LEFT JOIN returns every row from the left table. If the right table has a matching row, you get the matched columns. If not, you still get the left row, and the right-side columns come back as NULL.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),
    total REAL
);

INSERT INTO users (id, name) VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders (user_id, total) VALUES (1, 49.99), (1, 12.50), (2, 99.00);

SELECT u.name, o.total
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id;

Cleo has no orders, but she still appears — with NULL in the total column. Swap LEFT JOIN for INNER JOIN and Cleo disappears entirely.

The Mental Model

Read the query top to bottom and think of the left table as the anchor. Every row in users is going to appear in the output, no matter what. The LEFT JOIN then asks, for each user: "is there a matching row in orders?"

• Match found → glue the matched columns onto the user row.
• Multiple matches → produce one output row per match (Ada has two orders, so she appears twice).
• No match → produce one row with NULL for every column from the right table.

That last case is the whole reason LEFT JOIN exists. NULL here doesn't mean "we don't know" — it means "there is nothing on the right to glue on."

LEFT OUTER JOIN is the same operation. The OUTER keyword is optional in SQLite, and most people leave it off.

Finding Rows With No Match

The classic LEFT JOIN use case: find rows in the left table that have no match on the right. The trick is to filter on a column from the right table that's NOT NULL in the actual data — usually its primary key — and check for NULL after the join:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 49.99), (2, 2, 99.00);

SELECT u.name
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.id IS NULL;

Only Cleo comes back. The join attaches order data where it exists; the WHERE o.id IS NULL then keeps only the rows where the attach failed. This is sometimes called an "anti-join."

ON vs WHERE: The Subtle Trap

This is the most common bug with LEFT JOIN, and it's worth pausing on. Conditions go in either the ON clause or the WHERE clause, but they behave very differently with outer joins.

• ON runs while the join is happening. Conditions there decide which right-side rows count as a match.
• WHERE runs after the join has produced its rows. It filters the combined result.

Watch what happens when you put a condition on the right table in WHERE:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, status TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 'shipped'), (2, 2, 'pending');

-- Wrong: silently turns LEFT JOIN into INNER JOIN
SELECT u.name, o.status
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
WHERE o.status = 'shipped';

Cleo has no order, so o.status is NULL for her row, and NULL = 'shipped' is not true — she gets filtered out. Boris's status is 'pending', also dropped. The LEFT JOIN quietly behaved like an INNER JOIN.

The fix: move the condition into ON, so it filters matches rather than output rows:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, status TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 'shipped'), (2, 2, 'pending');

SELECT u.name, o.status
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id AND o.status = 'shipped';

Now every user appears. Ada gets her shipped order; Boris gets NULL (his pending order didn't qualify as a match); Cleo gets NULL (no orders at all). That's the right answer when the question is "show me every user, plus their shipped orders if any."

Rule of thumb: conditions on the left table can go in WHERE. Conditions on the right table almost always belong in ON — unless you specifically want to find unmatched rows with IS NULL.

Counting With LEFT JOIN

A common job: count related rows per parent, including parents with zero. INNER JOIN would drop the zeros. LEFT JOIN plus COUNT of a right-side column gives the right answer:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (1, 1, 49.99), (2, 1, 12.50), (3, 2, 99.00);

SELECT u.name, COUNT(o.id) AS order_count, COALESCE(SUM(o.total), 0) AS revenue
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
GROUP BY u.id, u.name
ORDER BY u.name;

Two things worth noticing:

• COUNT(o.id) counts non-null right-side rows. Cleo gets 0, not 1 — because COUNT ignores NULL. If you wrote COUNT(*), Cleo would get 1 (the row exists, it just has NULLs in it). Almost always, COUNT(right.id) is what you want.
• COALESCE(SUM(o.total), 0) turns Cleo's NULL sum into 0. Without it, she'd show NULL revenue, which is technically correct but ugly to display.

Joining Multiple Tables

LEFT JOIN chains. Each join takes the running result and joins another table onto it. Once you've made a column nullable with a LEFT JOIN, keep using LEFT JOIN for any tables that hang off it — otherwise the next INNER JOIN will quietly drop rows you wanted to keep.

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE orders (id INTEGER PRIMARY KEY, user_id INTEGER, total REAL);
CREATE TABLE shipments (order_id INTEGER PRIMARY KEY, carrier TEXT);

INSERT INTO users VALUES (1, 'Ada'), (2, 'Boris'), (3, 'Cleo');
INSERT INTO orders VALUES (10, 1, 49.99), (11, 2, 99.00);
INSERT INTO shipments VALUES (10, 'UPS');

SELECT u.name, o.total, s.carrier
FROM users AS u
LEFT JOIN orders AS o ON o.user_id = u.id
LEFT JOIN shipments AS s ON s.order_id = o.id;

Three users come back. Ada has an order and a shipment. Boris has an order but no shipment (carrier is NULL). Cleo has no order, so both o.total and s.carrier are NULL. The chain of LEFT JOINs preserves every user regardless of how far down the relationship chain the data runs out.

When LEFT JOIN Is the Right Choice

Reach for LEFT JOIN when the question being asked is fundamentally about the left table, and the right table is supplementary information. Phrasings like "every user, with their orders if any" or "all products and their latest review" map directly to LEFT JOIN.

Reach for INNER JOIN when both sides are equally required — "orders with their user details" doesn't make sense for an order with no user, so the inner join's filtering is what you want.

If you find yourself writing LEFT JOIN ... WHERE right.col IS NOT NULL, you wanted an INNER JOIN. If you find yourself writing LEFT JOIN ... WHERE right.col IS NULL, you wanted an anti-join, and you've got it right.

Next: Self-Joins

Sometimes the table you want to join to is the same table you're already querying — employees and their managers, categories and their parents, pairs of users in the same city. That's a self-join, and it's the next page.