LEFT JOIN in SQL: syntax, examples and pitfalls

LEFT JOIN is useful whenever I have a list of things that must stay in the result, for example every product in the catalogue, and I want to add data from a second table, for example sales. A product without sales does not disappear. It gets a row in which the columns from the second table are NULL.

That sounds simple, and it is, until filters, duplicates and the search for missing rows come in. I walk through all three on one small dataset below. A comparison of every join type on customer and order data is in the article SQL JOIN types: how I combine tables and check the result. This page is only about LEFT JOIN.

LEFT JOIN syntax

SELECT columns
FROM left_table AS l
LEFT JOIN right_table AS r
    ON r.key = l.key;

• The left table comes after FROM. Every one of its rows reaches the result at least once.
• The right table comes after LEFT JOIN. Its rows reach the result only when they meet the ON condition.
• When nothing matches a left row, the database returns it anyway and fills the right-table columns with NULL.

LEFT OUTER JOIN means exactly the same thing, the word OUTER is optional. The PostgreSQL documentation describes it in two steps: first an ordinary inner join is performed, then every left row that matched no right row is added.

I compare keys of the same type. PostgreSQL rejects a comparison of an integer column with a text column with the error operator does not exist. SQLite converts the text to a number according to its type affinity rules, so the number 1 also matches the strings '01' and ' 1'. So I cast explicitly with CAST, in one place.

Example: products and their sales

I work on a small synthetic dataset: four products and six sales. The mug sold three times, the notebook once, the lamp only in September and the backpack never. Sale 6 has no product ID, because records like that do turn up in exports. The code runs unchanged in SQLite and PostgreSQL.

-- example: fixture
CREATE TEMP TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL
);
CREATE TEMP TABLE sales (
    sale_id INTEGER PRIMARY KEY,
    product_id INTEGER,
    sale_date DATE NOT NULL,
    quantity INTEGER NOT NULL
);
INSERT INTO products VALUES
    (1, 'mug'), (2, 'notebook'), (3, 'lamp'), (4, 'backpack');
INSERT INTO sales VALUES
    (1, 1, '2026-09-28', 2),
    (2, 1, '2026-10-03', 1),
    (3, 1, '2026-10-15', 3),
    (4, 2, '2026-10-07', 5),
    (5, 3, '2026-09-12', 1),
    (6, NULL, '2026-10-09', 2);

I want every product together with its sales:

-- example: basic
SELECT p.product_id, p.product_name, s.sale_id, s.sale_date, s.quantity
FROM products AS p
LEFT JOIN sales AS s ON s.product_id = p.product_id
ORDER BY p.product_id, s.sale_id;

Six rows from four products. The backpack stayed even though it has no sales, and the mug appears three times because it has three matching sales. LEFT JOIN promises that each product appears at least once. It does not promise exactly once.

Sale 6 is missing from the result. Its product_id is empty, and NULL = 1 is not true, so it matches no product. I keep the rows of the left side, and the left side here is products. If I started with FROM sales and joined products, I would get six sales rows, and sale 6 would have an empty product name. So I choose the table order by asking what I must not lose.

I always add ORDER BY. Without it the database can return rows in any order, and comparing results by eye stops making sense.

A condition in ON vs a condition in WHERE

Now I want every product and only the October sales. I put the date condition into ON, because it describes which sales should match:

-- example: october-on
SELECT p.product_id, p.product_name, s.sale_id
FROM products AS p
LEFT JOIN sales AS s
    ON s.product_id = p.product_id
   AND s.sale_date >= '2026-10-01'
   AND s.sale_date < '2026-11-01'
ORDER BY p.product_id, s.sale_id;

Five rows: the mug with sales 2 and 3, the notebook with sale 4, and the lamp and the backpack with NULL. The lamp did sell in September, but that sale does not meet the match condition, so the lamp is left without a partner.

The same condition in WHERE asks a different question:

-- example: october-where
SELECT p.product_id, p.product_name, s.sale_id
FROM products AS p
LEFT JOIN sales AS s ON s.product_id = p.product_id
WHERE s.sale_date >= '2026-10-01'
  AND s.sale_date < '2026-11-01'
ORDER BY p.product_id, s.sale_id;

Three rows. The lamp and the backpack are gone, each for a different reason. WHERE works on the result of the join. The lamp only has a September sale, so its row fails the date condition. The backpack has no sales, and in the row added for it s.sale_date is NULL. The comparison NULL >= '2026-10-01' is unknown, and WHERE only lets true through. In practice I got an INNER JOIN dressed up as a LEFT JOIN.

The trap applies to conditions on right-table columns. A filter on the left table, such as WHERE p.product_name <> 'lamp', can safely stay in WHERE, because it removes products and leaves the matching alone. The exception on the right side is a condition that is true for NULL, above all IS NULL, which I come back to when looking for missing rows. For the same reason, the advice to “filter in WHERE before the join” gets the order wrong. WHERE filters the joined result. I narrow the right table in ON or in a subquery on the right side.

With dates I also watch the boundary. I write sale_date < '2026-11-01' instead of <= '2026-10-31'. If the column also stores a time, the second version loses a sale from 31 October recorded at, say, 14:30.

Counting gaps: COUNT and COALESCE

From the same join I build an October summary:

-- example: october-summary
SELECT p.product_id, p.product_name,
       COUNT(s.sale_id) AS sales_count,
       COALESCE(SUM(s.quantity), 0) AS units
FROM products AS p
LEFT JOIN sales AS s
    ON s.product_id = p.product_id
   AND s.sale_date >= '2026-10-01'
   AND s.sale_date < '2026-11-01'
GROUP BY p.product_id, p.product_name
ORDER BY p.product_id;

I count COUNT(s.sale_id), the non-empty sale IDs. COUNT(*) counts rows, so it would give the lamp and the backpack one sale each: the row LEFT JOIN added. SUM over nothing but NULL values returns NULL, which is why COALESCE turns the result into zero. Here zero is the true answer: not a single lamp sold in October.

I do not add COALESCE by reflex, though. In a moment I join a price list in which the backpack has no price. COALESCE(price, 0) would make it a free backpack, and that promotion would soon show up in the margin report. I leave a missing price as NULL or describe it openly, for example in a separate column saying the price is missing.

Duplicate keys multiply rows

Multiplication was already visible with the mug: three sales, three rows. I expect that, because one product has many sales. It gets worse when I join a table where I expect one row per key and there are more. I add a synthetic price list with price history:

-- example: prices
CREATE TEMP TABLE price_list (
    product_id INTEGER NOT NULL,
    valid_from DATE NOT NULL,
    price INTEGER NOT NULL
);
INSERT INTO price_list VALUES
    (1, '2026-01-01', 25),
    (1, '2026-10-01', 29),
    (2, '2026-01-01', 12),
    (3, '2026-01-01', 89);

The mug has two prices: an old one and one valid from October. If I join the price list on product_id alone, the mug appears twice. That is why I compare row counts before and after every lookup join:

-- example: row-check
SELECT
    (SELECT COUNT(*) FROM products) AS rows_before,
    (SELECT COUNT(*)
     FROM products AS p
     LEFT JOIN price_list AS pl ON pl.product_id = p.product_id) AS rows_after;

The result is 4 before and 5 after. LEFT JOIN does not lose products, so every extra row means a key that matches more than once. I look for it directly:

-- example: duplicate-keys
SELECT product_id, COUNT(*) AS price_rows
FROM price_list
GROUP BY product_id
HAVING COUNT(*) > 1
ORDER BY product_id;

The query returns product 1 with two rows. Now I know the cause is the price history and that the report needs one price per product. I pick the latest one with ROW_NUMBER:

-- example: latest-price
WITH ranked AS (
    SELECT product_id, price,
           ROW_NUMBER() OVER (
               PARTITION BY product_id ORDER BY valid_from DESC
           ) AS rn
    FROM price_list
)
SELECT p.product_id, p.product_name, r.price
FROM products AS p
LEFT JOIN ranked AS r
    ON r.product_id = p.product_id
   AND r.rn = 1
ORDER BY p.product_id;

Four rows: the mug at 29, the notebook at 12, the lamp at 89 and the backpack with NULL. The condition r.rn = 1 sits in ON. In WHERE it would remove the backpack, for the same reason the backpack vanished under the October filter: in its added row r.rn is NULL.

I do not fix duplicates like these with SELECT DISTINCT. With identical rows it would hide the symptom, and with two different prices it would still leave two rows. A larger example in which duplicated matches double the revenue is in the article on SQL JOIN types.

Anti-join, NOT EXISTS and the NOT IN trap

LEFT JOIN is good at finding what is missing. Which products did not sell in October?

-- example: anti-join
SELECT p.product_id, p.product_name
FROM products AS p
LEFT JOIN sales AS s
    ON s.product_id = p.product_id
   AND s.sale_date >= '2026-10-01'
   AND s.sale_date < '2026-11-01'
WHERE s.sale_id IS NULL
ORDER BY p.product_id;

The lamp and the backpack. The date condition is in ON, and only s.sale_id IS NULL stays in WHERE. This pattern is called an anti-join: it keeps the left rows for which no match was found. Without the date condition the result would be the backpack alone, the only product with no sales at all.

I test IS NULL on a column that is never empty in the right table: the primary key or the column used in the join. If I tested an optional column, for example a discount, a sale without a discount would pass for a missing sale. The operator itself is covered in the IS NULL entry.

NOT EXISTS gives the same result:

-- example: not-exists
SELECT p.product_id, p.product_name
FROM products AS p
WHERE NOT EXISTS (
    SELECT 1
    FROM sales AS s
    WHERE s.product_id = p.product_id
      AND s.sale_date >= '2026-10-01'
      AND s.sale_date < '2026-11-01'
)
ORDER BY p.product_id;

This version says exactly what I am asking: does a matching sale exist? It does not bring in right-table columns, so the result has at most one row per product. When I only need the list of gaps, this is the one I choose, and I keep LEFT JOIN for queries that also show data from the matched rows. Which version is faster is something the query plan shows on a specific database, so I do not assume it in advance.

There is a tempting third version with NOT IN:

-- example: not-in
SELECT p.product_id, p.product_name
FROM products AS p
WHERE p.product_id NOT IN (
    SELECT s.product_id
    FROM sales AS s
    WHERE s.sale_date >= '2026-10-01'
      AND s.sale_date < '2026-11-01'
)
ORDER BY p.product_id;

An empty result. Among the October sales there is sale 6 with an empty product_id. For the lamp the condition reads: 3 differs from 1, from 2 and from an unknown value. The last comparison cannot be decided, so the whole condition is NULL and the row is dropped. The backpack drops out for the same reason, and the mug and the notebook rightly so, because they sold in October. Both the PostgreSQL documentation and the NOT IN result table in SQLite describe this. If I do use NOT IN, I add AND s.product_id IS NOT NULL to the subquery. More often I simply pick NOT EXISTS.

When I choose a different JOIN

I choose LEFT JOIN when one side defines the population: every product, every customer, every day of the month. The other joins answer other questions:

• INNER JOIN when a missing match excludes the row from the answer, for example when I only care about products that sold.
• RIGHT JOIN keeps the right side. Any RIGHT JOIN can be written as a LEFT JOIN with the tables swapped, and I usually do that, because I find a query easier to read when the table I keep comes first.
• FULL JOIN when I want to see gaps on both sides at once: products without sales and sales without a product, like sale 6.
• EXISTS and NOT EXISTS when I only want to check whether a match exists, without adding columns.

SQLite has supported RIGHT and FULL OUTER JOIN only since version 3.39.0 from 2022. On an older version, LEFT JOIN with swapped tables is the way to go.

I also watch chains of joins. If after a LEFT JOIN to sales I add another table with an INNER JOIN on a sales column, for example the store where the sale happened, products without sales drop out again. Further joins on the optional side are LEFT JOINs too, or I combine them in a subquery first.

A short exercise

Run the sample data and add a backpack sale on 20 October. Before running the queries, write down what you expect: how many rows the example with the condition in ON returns, how many the one with the condition in WHERE returns and which products remain in the anti-join. Then add a second price for the notebook to the price list and check whether the row-count check catches it. Finally, delete sale 6 and see what changes in the NOT IN query.