SQL 35 🛢️ Including Unmatched Rows with LEFT JOIN - KandZ

The LEFT JOIN returns all rows from the left table and the matching rows from the right table. When a left row has no match in the right table, the right table’s columns are filled with NULL, and the row still appears in the result. This is the behavior that distinguishes an outer join from an inner join: the LEFT JOIN preserves the left side even when there is no match, which makes it the right tool whenever “all of these, plus what matches over there” is the question.

The LEFT JOIN is the most commonly used outer join. It answers questions like “list all customers and their orders, including customers with no orders,” “show every product and its sales, including products that never sold,” and “display all employees and their assigned projects, including employees with no project.” In each case, the left table represents the entities that must appear, and the right table contributes optional data. When there is no matching row on the right, the result still includes the entity, with NULLs standing in for the missing data.

This chapter covers the syntax of LEFT JOIN, the behavior of NULLs in the result, filtering the right table, detecting unmatched rows, joining multiple tables with LEFT JOIN, the difference between a LEFT JOIN and an INNER JOIN in practice, and the patterns that prevent the most common mistakes.

Key point: LEFT JOIN returns all rows from the left table and the matching rows from the right table. Unmatched left rows appear with NULL in the right columns. Conditions on the right table belong in the ON clause to preserve unmatched rows, and in WHERE to filter them out. To find left rows with no match, filter on right.key IS NULL.

Why LEFT JOIN exists

The preservation problem. An INNER JOIN drops rows that have no match. When the left table’s rows are the primary subject of the query — all customers, all products, all employees — dropping them because they lack related data produces a result that is incomplete. LEFT JOIN exists to preserve those rows while still attaching matching data when it exists.

The optional relationship problem. Some relationships are optional. A customer may or may not have placed an order. A product may or may not have been reviewed. A task may or may not have an assigned user. LEFT JOIN models the optional side as “attach if present, NULL if not,” which matches the semantics of the relationship.

The reporting problem. Reports often need complete lists. A report of all products with their revenue, including products with zero revenue, requires a LEFT JOIN to keep the unsold products in the result. An INNER JOIN would silently drop them and produce a report that misrepresents the inventory.

The unmatched-detection problem. Finding rows in one table that have no match in another — customers who never ordered, products that were never sold, users who never logged in — is a common task. The LEFT JOIN combined with a WHERE right.key IS NULL filter is the canonical way to express it.

The conditional-data problem. Sometimes the question is “all of X, and the subset of Y that meets a condition.” A LEFT JOIN with the condition in the ON clause preserves all of X while only attaching the matching Y rows. Putting the same condition in WHERE destroys that behavior, turning the LEFT JOIN into an INNER JOIN.

a. Basic LEFT JOIN syntax

The syntax places the left table first, then LEFT JOIN and the right table, with the matching condition in ON.

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

Every customer appears in the result. When a customer has orders, the order columns are populated. When a customer has no orders, the order columns are NULL.

The LEFT OUTER JOIN spelling is equivalent. The OUTER keyword is optional.

FROM customers c
LEFT OUTER JOIN orders o ON c.customer_id = o.customer_id

The left table is the one named before the LEFT JOIN keyword. In the example, customers is the left table, and its rows are preserved.

b. The shape of NULL results

When a left row has no match, every column from the right table is NULL. This is not a stored NULL but a placeholder generated by the join.

name	order_id	total
Alice	101	250.00
Alice	102	180.00
Bob	103	320.00
Carol	NULL	NULL
David	104	95.00

Carol has no orders, so order_id and total are NULL. The name column, which comes from the left table, is populated.

This NULL behavior is what makes the IS NULL pattern work for detecting unmatched rows. A row where the right table’s primary key is NULL is a row where no match was found.

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

This returns exactly the customers who have no orders: Carol.

c. The ON clause versus WHERE

The placement of a filter determines whether unmatched left rows are preserved or removed.

A condition in the ON clause is part of the join. Only rows that satisfy the join condition match, and unmatched left rows are still preserved.

SELECT c.name, o.order_id, o.total
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
 AND o.total > 200;

This returns every customer. For customers with orders above 200, the order columns are populated. For customers with no orders above 200 — including those with no orders at all — the order columns are NULL.

A condition in the WHERE clause filters the joined result. It removes rows where the condition is not true, including rows where the right columns are NULL.

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

This returns only customers with orders above 200. Customers with no orders have total = NULL, and NULL > 200 is unknown, so those rows are excluded. The LEFT JOIN behaves like an INNER JOIN.

The rule is simple. If the condition should be part of the match, put it in ON. If the condition should filter the final result, put it in WHERE. For LEFT JOIN, the two placements have different results.

d. Chaining multiple LEFT JOINs

A LEFT JOIN can be followed by another LEFT JOIN. Each adds a table and a condition, and the outer behavior propagates.

SELECT c.name, o.order_id, p.product_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id;

Customers with no orders appear with NULL for order, item, and product columns. Customers with orders but no items appear with NULL for item and product columns. The chain preserves the customers at the top.

Mixing LEFT and INNER joins requires care. An INNER JOIN after a LEFT JOIN can effectively drop the unmatched rows that the LEFT JOIN preserved.

-- The INNER JOIN to products removes rows where product is NULL
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id

If the intent is to preserve unmatched customers through the whole chain, every join must be a LEFT JOIN. If a subsequent INNER JOIN requires a match, the earlier LEFT JOIN’s preservation is undone for the rows that fail the inner join.

e. Detecting unmatched rows

The LEFT JOIN with WHERE right.key IS NULL is the canonical way to find left rows with no match.

-- Customers who have never placed an order
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

The reverse pattern finds right rows with no left match using a RIGHT JOIN, or a LEFT JOIN with the tables reversed.

-- Orders with no matching customer
SELECT o.order_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

The column used in the IS NULL test should be the join key from the table being checked for absence. Using a column that is legitimately NULL in matched rows would produce false positives.

A NOT EXISTS subquery is an alternative that expresses the same intent:

SELECT c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Some databases optimize NOT EXISTS better than LEFT JOIN ... IS NULL. The result is the same. The choice between them is often a matter of readability or performance.

Complete Example Session

-- ============================================
-- PART 1: CREATE SAMPLE TABLES
-- ============================================
CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name        VARCHAR(50),
    country     VARCHAR(50)
);

CREATE TABLE orders (
    order_id    INTEGER PRIMARY KEY,
    customer_id INTEGER,
    total       NUMERIC(10, 2),
    order_date  DATE
);

INSERT INTO customers VALUES
(1, 'Alice',   'Norway'),
(2, 'Bob',     'Sweden'),
(3, 'Carol',   'Denmark'),
(4, 'David',   'Norway'),
(5, 'Eve',     'Finland');

INSERT INTO orders VALUES
(101, 1, 250.00, '2026-01-15'),
(102, 1, 180.00, '2026-02-20'),
(103, 2, 320.00, '2026-01-10'),
(104, 4, 95.00,  '2026-03-05');

-- ============================================
-- PART 2: INNER JOIN FOR COMPARISON
-- ============================================
SELECT c.name, o.order_id
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;
-- Returns 4 rows: Carol and Eve excluded

-- ============================================
-- PART 3: LEFT JOIN PRESERVES ALL CUSTOMERS
-- ============================================
SELECT c.name, o.order_id, o.total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;
-- Returns 6 rows: Carol and Eve appear with NULLs

-- ============================================
-- PART 4: FIND UNMATCHED ROWS
-- ============================================
SELECT c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
-- Returns: Carol, Eve

-- ============================================
-- PART 5: FILTER IN ON PRESERVES OUTER
-- ============================================
SELECT c.name, o.order_id, o.total
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
 AND o.total > 200;
-- Carol and Eve still appear with NULLs

-- ============================================
-- PART 6: FILTER IN WHERE BREAKS OUTER
-- ============================================
SELECT c.name, o.order_id, o.total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.total > 200;
-- Carol and Eve disappear; only Alice and Bob

-- ============================================
-- PART 7: CHAINED LEFT JOINS
-- ============================================
SELECT c.name, o.order_id, p.product_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id;
-- All customers appear, with NULLs where no match exists

-- ============================================
-- PART 8: COUNT WITH LEFT JOIN
-- ============================================
SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name;
-- Carol and Eve show 0, not NULL, because COUNT ignores NULLs

-- ============================================
-- PART 9: SUM WITH LEFT JOIN
-- ============================================
SELECT c.name, COALESCE(SUM(o.total), 0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name;
-- Carol and Eve show 0 due to COALESCE

-- ============================================
-- PART 10: NOT EXISTS ALTERNATIVE
-- ============================================
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);
-- Returns: Carol, Eve

These ten parts cover the comparison with INNER JOIN, LEFT JOIN preserving all rows, detecting unmatched rows, filtering in ON versus WHERE, chained LEFT JOINs, counting with LEFT JOIN, summing with COALESCE, and the NOT EXISTS alternative.

Quick Reference

LEFT JOIN Syntax

Form	Example
Explicit	`FROM a LEFT JOIN b ON a.id = b.id`
With OUTER	`FROM a LEFT OUTER JOIN b ON a.id = b.id`
Multiple	`FROM a LEFT JOIN b ON ... LEFT JOIN c ON ...`

ON vs WHERE for LEFT JOIN

Condition Location	Unmatched Left Rows	Behavior
ON	Preserved with NULLs	Outer join maintained
WHERE	Removed	Becomes inner join

Unmatched Detection

Pattern	Purpose
`LEFT JOIN ... WHERE b.key IS NULL`	Left rows with no match
`NOT EXISTS (SELECT 1 FROM b WHERE ...)`	Alternative form
`LEFT JOIN ... WHERE b.key IS NOT NULL`	Same as inner join

Aggregation with LEFT JOIN

Function	Result on Unmatched Rows
`COUNT(*)`	Counts the row (1), even with NULLs
`COUNT(b.key)`	Counts only matches (0 if none)
`SUM(b.col)`	NULL if no matches
`COALESCE(SUM(b.col), 0)`	Zero if no matches

Best Practices

✅ Do This:

-- Preserve left rows with LEFT JOIN
FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- Put right-table conditions in ON to preserve outer
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.total > 200;

-- Detect unmatched rows
LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.order_id IS NULL;

-- Count matches, not rows
COUNT(o.order_id)

-- Default NULL sums to zero
COALESCE(SUM(o.total), 0)

❌ Don’t Do This:

-- Filter right table in WHERE on a LEFT JOIN
LEFT JOIN orders o ON c.customer_id = o.customer_id WHERE o.total > 200;  -- ❌ breaks outer

-- Count rows instead of matches
COUNT(*)  -- ❌ returns 1 for unmatched rows

-- Mix INNER JOIN after LEFT JOIN unintentionally
LEFT JOIN orders o ON ... INNER JOIN order_items oi ON ...;  -- ❌ may drop rows

Common Pitfalls

Pitfall	Why It Happens	Fix
LEFT JOIN behaves like INNER	Filter on right table in WHERE	Move filter to ON
Unmatched rows missing	Later INNER JOIN drops them	Use LEFT JOIN throughout
COUNT returns 1 instead of 0	Used `COUNT(*)`	Use `COUNT(right.key)`
SUM returns NULL	No matches; NULL propagates	Use `COALESCE(SUM(...), 0)`
Duplicate rows	One-to-many match on right	Aggregate or expect multiplication
Wrong column in IS NULL test	Used a nullable column	Use the join key

Real-World Examples

1. All Customers and Their Orders

SELECT c.name, o.order_id FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

2. Customers Without Orders

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

3. Products Never Sold

SELECT p.product_name FROM products p
LEFT JOIN order_items oi ON p.product_id = oi.product_id
WHERE oi.order_id IS NULL;

4. Employees Without Projects

SELECT e.name FROM employees e
LEFT JOIN assignments a ON e.employee_id = a.employee_id
WHERE a.project_id IS NULL;

5. Orders Above Threshold, Preserving All Customers

SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id AND o.total > 200;

6. Count with Zero Default

SELECT c.name, COUNT(o.order_id) AS cnt
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name;

7. Sum with Zero Default

SELECT c.name, COALESCE(SUM(o.total), 0) AS total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.name;

8. Chained Left Joins

SELECT c.name, o.order_id, p.product_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
LEFT JOIN products p ON oi.product_id = p.product_id;

9. NOT EXISTS Alternative

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

10. Date-Range Filter in ON

SELECT c.name, o.order_id
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
 AND o.order_date >= '2026-01-01';

Visual

INNER JOIN vs LEFT JOIN

┌──────────────────────────────────────────────────────────────┐
│  customers              orders                               │
│  ┌────┬────────┐        ┌─────┬──────┐                       │
│  │ id │ name   │        │ oid │ cid  │                       │
│  ├────┼────────┤        ├─────┼──────┤                       │
│  │ 1  │ Alice  │        │ 101 │ 1    │                       │
│  │ 2  │ Bob    │        │ 102 │ 1    │                       │
│  │ 3  │ Carol  │        │ 103 │ 2    │                       │
│  └────┴────────┘        └─────┴──────┘                       │
│                                                              │
│  INNER JOIN:            LEFT JOIN:                           │
│  ┌────────┬─────┐       ┌────────┬─────┐                    │
│  │ Alice  │ 101 │       │ Alice  │ 101 │                    │
│  │ Alice  │ 102 │       │ Alice  │ 102 │                    │
│  │ Bob    │ 103 │       │ Bob    │ 103 │                    │
│  └────────┴─────┘       │ Carol  │ NULL│  ← preserved       │
│                         └────────┴─────┘                    │
│  Carol excluded                                              │
└──────────────────────────────────────────────────────────────┘

Filter in ON vs WHERE

┌──────────────────────────────────────────────────────────────┐
│  FILTER IN ON                        FILTER IN WHERE         │
│                                                              │
│  LEFT JOIN orders o                  LEFT JOIN orders o      │
│    ON c.id = o.customer_id             ON c.id = o.customer_id│
│   AND o.total > 200                  WHERE o.total > 200     │
│                                                              │
│  Result:                             Result:                 │
│  ┌────────┬───────┐                  ┌────────┬───────┐     │
│  │ Alice  │ 250   │                  │ Alice  │ 250   │     │
│  │ Bob    │ 320   │                  │ Bob    │ 320   │     │
│  │ Carol  │ NULL  │ ← preserved      └────────┴───────┘     │
│  │ David  │ NULL  │ ← preserved      Carol and David gone   │
│  └────────┴───────┘                                          │
└──────────────────────────────────────────────────────────────┘

Detecting Unmatched Rows

┌──────────────────────────────────────────────────────────────┐
│  LEFT JOIN orders o                                          │
│    ON c.customer_id = o.customer_id                          │
│  WHERE o.order_id IS NULL                                    │
│                                                              │
│  Result:                                                     │
│  ┌────────────────────────────────────────────────────────┐  │
│  │  Rows where the right table's key is NULL              │  │
│  │  = left rows with no matching right row                │  │
│  └────────────────────────────────────────────────────────┘  │
└──────────────────────────────────────────────────────────────┘

Aggregation with LEFT JOIN

┌──────────────────────────────────────────────────────────────┐
│  COUNT(*)                COUNT(o.order_id)                   │
│  ────────────            ─────────────────                   │
│  Counts the row          Counts non-NULL order_ids           │
│  Unmatched rows = 1      Unmatched rows = 0                  │
│                                                              │
│  SUM(o.total)            COALESCE(SUM(o.total), 0)           │
│  ─────────────           ─────────────────────────           │
│  Unmatched = NULL        Unmatched = 0                       │
└──────────────────────────────────────────────────────────────┘

Summary

Item	Value
LEFT JOIN	All left rows + matching right rows
Unmatched left rows	Appear with NULL in right columns
Left table	Named before `LEFT JOIN`
Filter in ON	Preserves unmatched left rows
Filter in WHERE	Removes unmatched left rows
Unmatched detection	`WHERE right.key IS NULL`
COUNT with LEFT JOIN	Use `COUNT(right.key)` for matches
SUM with LEFT JOIN	Use `COALESCE(SUM(...), 0)`
NOT EXISTS	Alternative to LEFT JOIN + IS NULL
Chained LEFT JOINs	Preserve outer behavior throughout

Key takeaways:

LEFT JOIN returns all rows from the left table. Unmatched left rows appear with NULL in the right columns. This is the behavior that distinguishes it from INNER JOIN.
The left table is the one named before LEFT JOIN. Its rows are preserved. The right table contributes matching data when it exists.
Conditions on the right table belong in ON to preserve outer behavior. Putting them in WHERE removes the unmatched rows and turns the LEFT JOIN into an INNER JOIN.
WHERE right.key IS NULL detects unmatched rows. This is the canonical pattern for finding left rows with no match, such as customers who never ordered.
COUNT(right.key) counts matches, not rows. COUNT(*) returns 1 for an unmatched left row; COUNT(right.key) returns 0.
COALESCE(SUM(...), 0) defaults NULL sums to zero. SUM over no matching rows returns NULL, which propagates through the result.
Chained LEFT JOINs preserve outer behavior throughout. Mixing INNER JOIN after a LEFT JOIN can drop the unmatched rows that were preserved earlier.
NOT EXISTS is an alternative to LEFT JOIN + IS NULL. Both express “left rows with no match”; the choice is a matter of readability or performance.

Remember: LEFT JOIN is the join type that answers “all of these, plus what matches over there.” The left table’s rows are the primary subject of the query, and the right table contributes optional data. When the right table has a match, the columns are populated; when it does not, they are NULL. This NULL behavior is the key to the two patterns that LEFT JOIN enables: preserving unmatched rows in a report, and detecting unmatched rows with WHERE right.key IS NULL. The most common mistake is putting a condition on the right table in the WHERE clause, which removes the NULL rows and turns the LEFT JOIN into an INNER JOIN. The correct placement is the ON clause, which makes the condition part of the match. When aggregating over a LEFT JOIN, use COUNT(right.key) to count matches and COALESCE to default sums to zero. Understanding these behaviors lets you write reports and queries that include entities with no related data, which is one of the most common requirements in business reporting.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!