Back to blog

Published

LEFT JOIN vs INNER JOIN: the SQL mistake ORMs and AI assistants hide

Learn how LEFT JOIN differs from INNER JOIN, why a WHERE filter drops unmatched rows, and how GROUP BY changes customer counts in PostgreSQL reports today.

November 17, 2026·7 min read

A LEFT JOIN keeps rows from the left table that have no match on the right. However, a WHERE predicate on a right-table column that rejects null-extended rows, such as o.status = 'paid', can silently make the result behave like an INNER JOIN. Put that matching restriction in ON when every left-side row must remain. PostgreSQL applies WHERE filtering after the joined table expression, as described in the PostgreSQL WHERE documentation.

This is an easy mistake to approve in generated SQL. The query looks reasonable, returns valid rows, and may pass a test fixture where every customer happens to have an order. The missing customer only appears later, when someone asks why a dashboard, export, or notification list excludes records with no related data.

An ORM can make the mistake harder to spot because the code still says outerjoin. For example, this SQLAlchemy statement has the same semantics as the broken SQL below:

python
stmt = (
    select(Customer.name, Order.id, Order.status)
    .outerjoin(Order, Order.customer_id == Customer.id)
    .where(Order.status == "paid")
)

The method name suggests that customers are preserved, but the later .where() condition removes rows where Order.status is null. An AI assistant can produce the same shape of query quickly, which makes checking the resulting SQL and its edge-case rows worthwhile.

What does LEFT JOIN return that INNER JOIN does not?

Use a small example before reasoning about a production query. Imagine these tables:

sql
CREATE TEMP TABLE customers (
  id integer PRIMARY KEY,
  name text NOT NULL
);

CREATE TEMP TABLE orders (
  id integer PRIMARY KEY,
  customer_id integer NOT NULL,
  status text NOT NULL,
  total_cents integer NOT NULL
);

INSERT INTO customers (id, name) VALUES
  (1, 'Ada'),
  (2, 'Ben'),
  (3, 'Cy'),
  (4, 'Dana');

INSERT INTO orders (id, customer_id, status, total_cents) VALUES
  (101, 1, 'paid', 5000),
  (102, 1, 'pending', 2000),
  (103, 2, 'paid', 3000),
  (104, 4, 'pending', 4000);

An inner join returns only pairs that satisfy the join condition:

sql
SELECT c.name, o.id AS order_id, o.status
FROM customers AS c
INNER JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id;
nameorder_idstatus
Ada101paid
Ada102pending
Ben103paid
Dana104pending

Cy is absent because Cy has no matching order. PostgreSQL describes an inner join as producing the combined row for each matching pair. The PostgreSQL join documentation also explains that an outer join adds rows for unmatched input, filling the missing side with nulls.

Now change only the join type:

sql
SELECT c.name, o.id AS order_id, o.status
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
ORDER BY c.id, o.id;
nameorder_idstatus
Ada101paid
Ada102pending
Ben103paid
CyNULLNULL
Dana104pending

Cy's null-extended row is the practical difference here. The joined result has a row for Cy, even though there was no row in orders to combine with it.

Why does a WHERE filter turn this into an inner join?

Suppose the page needs each customer and their paid orders. An assistant may generate this query:

sql
SELECT c.name, o.id AS order_id, o.status
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'paid'
ORDER BY c.id, o.id;

It returns:

nameorder_idstatus
Ada101paid
Ben103paid

Cy's joined row contains NULL for o.status. The expression o.status = 'paid' is therefore not true for that row, and WHERE retains only true conditions. Cy disappears. Dana also disappears, even though Dana has an order, because none of Dana's orders qualify as paid.

The fix is to make the status condition part of the matching rule:

sql
SELECT c.name, o.id AS order_id, o.status
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
 AND o.status = 'paid'
ORDER BY c.id, o.id;

This returns:

nameorder_idstatus
Ada101paid
Ben103paid
CyNULLNULL
DanaNULLNULL

Cy has no orders at all. Dana has orders, but no paid order. Moving o.status = 'paid' into ON preserves both customers because the condition defines which order rows qualify as a match.

The distinction is simple once the intermediate result is visible. ON determines which orders row can match a customer. WHERE removes rows from the joined result. PostgreSQL's documentation separates join qualification from the later WHERE filtering step, which is why moving this predicate changes the result. See the documented table-expression processing order.

A WHERE condition can still be correct with a left join. For example, filtering WHERE c.name LIKE 'A%' filters the preserved left-side rows. A right-side condition can also preserve unmatched rows when it tests for them directly: WHERE o.id IS NULL selects Cy's null-extended row. The warning is about predicates such as o.status = 'paid' that reject null-extended right-side values when unmatched left rows matter.

What does GROUP BY do after a join?

Joins can increase row counts before grouping reduces them again. Ada has two matching orders, so the join has two Ada rows. Grouping by customer then collapses those rows into one group per customer.

sql
SELECT c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;
nameorder_count
Ada2
Ben1
Cy0
Dana1

COUNT(o.id) counts non-null order IDs, so Cy gets zero. Avoid substituting COUNT(*) without thinking: the left join still produces Cy's null-extended row, and COUNT(*) would report one for Cy. PostgreSQL documents that grouping combines rows sharing the grouping values and that aggregate functions operate over each group. The PostgreSQL aggregation documentation is worth keeping nearby when reviewing report queries.

Now add the same misplaced filter:

sql
SELECT c.name, COUNT(o.id) AS paid_order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.status = 'paid'
GROUP BY c.id, c.name
ORDER BY c.id;
namepaid_order_count
Ada1
Ben1

Cy and Dana have no group because their rows were removed before grouping. Put the condition in ON instead:

sql
SELECT c.name, COUNT(o.id) AS paid_order_count
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.id
 AND o.status = 'paid'
GROUP BY c.id, c.name
ORDER BY c.id;
namepaid_order_count
Ada1
Ben1
Cy0
Dana0

The query now says what the report means: list every customer, then count only their paid orders.

Run this self check in psql

Paste the setup and these three queries into psql.

  1. Run the plain LEFT JOIN query from the first example. Predict whether Cy appears before you run it.
  2. Run the LEFT JOIN query with WHERE o.status = 'paid'. Again, predict whether Cy appears.
  3. Run the grouped query that puts o.status = 'paid' in ON and uses COUNT(o.id). Predict Cy's count before you run it.

If your prediction differs from the result, inspect the rows before adding GROUP BY. That is usually faster than trying to reason from a final count alone. This small habit also makes AI-generated queries easier to review. A query can be syntactically correct and still encode the wrong definition of “all customers.”

Keep the join rule warm

You do not need to build a reporting system every week to retain this distinction. Keep one mental test close: after the join, which rows exist; after WHERE, which of those rows remain; after GROUP BY, how many rows represent each group?

Unrot offers daily practice sessions of 3 to 7 tasks, taking about 5 minutes, with SQL and PostgreSQL practice, streaks, XP, and spaced repetition. You can use the SQL and PostgreSQL track to revisit joins in short reps, or try Unrot as a guest before creating an account. Practice will not replace inspecting the real query and its sample rows, but it can make the review question come back faster when the next left join appears.

Keep reading

  • Unrot vs LeetCode

    Compare Unrot and LeetCode for daily practice, interview prep, pricing, features, and fit. Find the tool that best suits your current engineering goal.

  • Unrot vs Codewars

    Compare Codewars and Unrot for kata, rank progression, community solutions, pricing, and daily recall practice to choose the coding habit that fits your week.

  • Unrot vs Exercism

    Compare Unrot and Exercism for daily practice, mentored coding exercises, pricing, and keeping Python, SQL, Git, or TypeScript skills fresh at work today.

Reading about it is not the same as doing it.

Start a free session