Skip to main content

LEFT JOIN

Learning Focus

Use this lesson to understand LEFT JOIN with practical syntax and examples.

Concept Overview​

LEFT JOIN (also written as LEFT OUTER JOIN) returns all rows from the left table, plus matching rows from the right table. When a left row has no match, the right-side columns are returned as NULL.

Why is it important?​

  • Completeness: reporting often needs “show all X, even if Y is missing” (customers with 0 orders)
  • Data quality: identifying missing relationships is a common audit task
  • Safety: incorrect filtering can accidentally turn a LEFT JOIN into an INNER JOIN and hide missing rows

Where does it fit?​

LEFT JOIN is part of the join family (INNER, LEFT, RIGHT, FULL, CROSS). You use it frequently when building dimensional reports and when you need anti-join patterns (find rows that do not have a match).

Syntax & Rules​

Core Syntax​

SELECT select_list
FROM left_table l
LEFT JOIN right_table r
  ON r.foreign_key = l.primary_key;

Available Options / Parameters​

PartWhat it doesNotes
LEFT [OUTER] JOINkeeps all rows from the left tableOUTER is optional
ONdefines the matching ruleusually PK/FK equality
WHEREfilters after the joinfilters on right-table columns can remove NULL-extended rows
COALESCEreplaces NULLs produced by no-matchcommon for display and calculations

Key Rules and Considerations​

  • Unmatched right-side columns become NULL.
  • Filtering on r.* in WHERE can remove the NULL-extended rows and effectively behave like an inner join.
  • To keep the join outer while filtering right-side rows, put that filter in the ON clause.
  • When aggregating after a left join, use COUNT(r.id) rather than COUNT(*) if you want “0 when missing”.

Step-by-Step Examples​

Example 1: Keep All Customers (Even Without Orders) (Beginner)​

CREATE TABLE customers (
  customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   text NOT NULL
);

CREATE TABLE orders (
  order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint REFERENCES customers(customer_id),
  amount      numeric(12,2) NOT NULL CHECK (amount >= 0)
);

INSERT INTO customers (full_name) VALUES
  ('Alice'),
  ('Bob'),
  ('Charlie');

INSERT INTO orders (customer_id, amount) VALUES
  (1, 25.00),
  (1, 10.00),
  (2, 50.00);

SELECT c.customer_id, c.full_name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

Expected output:

 customer_id | full_name | order_id | amount
-------------+-----------+----------+--------
 1           | Alice     | 1        | 25.00
 1           | Alice     | 2        | 10.00
 2           | Bob       | 3        | 50.00
 3           | Charlie   |          |
(4 rows)

Explanation:

  • Charlie appears even though he has no orders.
  • The missing order columns are NULL for Charlie.

Example 2: Find “Missing Matches” (Anti-Join Pattern) (Intermediate)​

To find customers with no orders, check for NULL in a right-table key.

SELECT c.customer_id, c.full_name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.customer_id;

Expected output:

 customer_id | full_name
-------------+----------
 3           | Charlie
(1 row)

Explanation:

  • For customers with no matching order row, o.order_id is NULL.

Example 3: WHERE vs ON (Avoid Accidentally Losing Rows) (Advanced)​

If you filter on the right table in the WHERE clause, the left join can behave like an inner join.

Wrong SQL (filter in WHERE):

-- Intention: "show all customers, and only show large orders"
-- Reality: customers with no large orders disappear.

SELECT c.customer_id, c.full_name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount >= 20
ORDER BY c.customer_id, o.order_id;

Bad outcome:

  • Charlie disappears (and Alice's small order disappears) because o.amount >= 20 is not true for NULL.

Correct SQL (filter in ON to keep the join outer):

SELECT c.customer_id, c.full_name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.amount >= 20
ORDER BY c.customer_id, o.order_id;

Expected output (correct version):

 customer_id | full_name | order_id | amount
-------------+-----------+----------+--------
 1           | Alice     | 1        | 25.00
 2           | Bob       | 3        | 50.00
 3           | Charlie   |          |
(3 rows)

Explanation:

  • The join keeps all customers.
  • Orders are matched only if they meet the amount >= 20 condition.

Example 4: Counting Orders per Customer (COUNT(*) vs COUNT(order_id)) (Advanced)​

SELECT
  c.customer_id,
  c.full_name,
  COUNT(*) AS rows_after_join,
  COUNT(o.order_id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.full_name
ORDER BY c.customer_id;

Expected output:

 customer_id | full_name | rows_after_join | order_count
-------------+-----------+-----------------+------------
 1           | Alice     | 2               | 2
 2           | Bob       | 1               | 1
 3           | Charlie   | 1               | 0
(3 rows)

Explanation:

  • COUNT(*) counts joined rows, so Charlie still counts as 1.
  • COUNT(o.order_id) counts only matched orders, giving Charlie 0.

Practical Use Cases​

1) Customers with optional activity​

Show all customers and their most recent order time (if any).

SELECT c.customer_id, c.full_name, MAX(o.created_at) AS last_order_at
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.full_name;

2) Products with optional discounts​

SELECT p.product_id, p.name, d.discount_percent
FROM products p
LEFT JOIN discounts d ON d.product_id = p.product_id
WHERE d.expires_at IS NULL OR d.expires_at > now();

3) Audit for missing relationships​

SELECT u.user_id
FROM users u
LEFT JOIN user_profiles p ON p.user_id = u.user_id
WHERE p.user_id IS NULL;

4) Optional metadata tables​

SELECT a.account_id, m.last_seen_ip
FROM accounts a
LEFT JOIN account_metadata m ON m.account_id = a.account_id;

5) “Keep all left rows, show matched right rows only if valid”​

SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.status = 'paid';

Common Mistakes & Troubleshooting​

1) Filtering the right table in WHERE (turns into inner join)​

Wrong SQL:

SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'paid';

Bad outcome:

  • Customers without paid orders disappear.

Fix:

SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
 AND o.status = 'paid';

2) Forgetting the ON clause​

Wrong SQL:

SELECT *
FROM customers c
LEFT JOIN orders o;

Typical error:

ERROR:  syntax error at end of input

Fix:

SELECT *
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;

3) Using COUNT(*) when you mean “count of matches”​

Wrong SQL:

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

Bad outcome:

  • Customers with no orders appear as 1, not 0.

Fix:

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

4) Confusing “missing match” with real NULL values​

Wrong assumption:

  • Assuming a NULL on the right side always means “no match”.

Fix:

  • Use a right-table primary key (like o.order_id IS NULL) for missing-match detection.

Debugging checklist:

  1. Identify which table must be fully preserved (that table goes on the left).
  2. Start with a small join preview and inspect NULL-extended rows.
  3. Move right-table filters into ON if you still want to keep unmatched left rows.
  4. When counting matches, use COUNT(r.pk).
  5. If results are unexpectedly large, check for row multiplication (join cardinality).

Best Practices​

  • ✅ Use LEFT JOIN when the business question requires keeping all left-side rows. ❌ Avoid INNER JOIN when “missing rows” are meaningful.
  • ✅ Put right-table filters in the ON clause when you want to preserve unmatched left rows. ❌ Avoid right-table filters in WHERE that unintentionally drop NULL-extended rows.
  • ✅ Use WHERE r.pk IS NULL to find missing matches. ❌ Avoid using nullable non-key columns as the “missing match” detector.
  • ✅ Use COUNT(r.pk) to count matches and get 0 for missing. ❌ Avoid COUNT(*) when a left join is involved and you want match counts.
  • ✅ Qualify columns and use meaningful table aliases. ❌ Avoid ambiguous column references in multi-table queries.

Hands-On Practice​

Use this setup for the exercises:

CREATE TABLE practice_customers (
  customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  full_name   text NOT NULL
);

CREATE TABLE practice_orders (
  order_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint REFERENCES practice_customers(customer_id),
  amount      numeric(12,2) NOT NULL
);

INSERT INTO practice_customers (full_name) VALUES
  ('Alice'),
  ('Bob'),
  ('Charlie');

INSERT INTO practice_orders (customer_id, amount) VALUES
  (1, 10.00),
  (1, 25.00),
  (2, 5.00);

Exercise 1 (Easy): Show all customers and any orders​

Task: Return all customers with their orders (if any).

-- Your SQL here

Solution:

SELECT c.customer_id, c.full_name, o.order_id, o.amount
FROM practice_customers c
LEFT JOIN practice_orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

Exercise 2 (Medium): Find customers with no orders​

Task: Return customers that have no matching order rows.

-- Your SQL here

Solution:

SELECT c.customer_id, c.full_name
FROM practice_customers c
LEFT JOIN practice_orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.customer_id;

Exercise 3 (Advanced): Count orders per customer (0 included)​

Task: Return each customer with the number of orders they have.

-- Your SQL here

Solution:

SELECT c.customer_id, c.full_name, COUNT(o.order_id) AS order_count
FROM practice_customers c
LEFT JOIN practice_orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.full_name
ORDER BY c.customer_id;

Connection to Other Concepts​

ConceptWhy it matters
INNER JOINexcludes unmatched rows; compare behavior to LEFT JOIN
RIGHT JOINmirrors LEFT JOIN when you swap table order
NULL Valuesexplains why NULL-extended rows behave differently in filters
GROUP BY + aggregatescommon after LEFT JOIN for per-entity counts
COALESCEreplace NULLs from missing matches for display/calculation

Visual Learning Diagram​

flowchart TD
  A[Left Table Rows] --> B[LEFT JOIN]
  C[Right Table Rows] --> B
  B --> D[All Left Rows Kept]
  B --> E[Unmatched Right = NULL]
  E --> F[Anti-Join: WHERE right.pk IS NULL]
  B --> G[Common Bug]
  G --> H[Right Filter in WHERE]
  H --> I[Becomes INNER JOIN]

  classDef allNodes fill:#3e3e3e,stroke:#ffffff,stroke-width:2px,color:#f5f5f5
  classDef highlight fill:#3e3e3e,stroke:#ffffff,stroke-width:4px,color:#f5f5f5
  class A,B,C,D,E,F,G,H,I allNodes
  class B highlight

Common Pitfalls​

PitfallConsequencePrevention
Filtering right-table columns in WHEREunmatched left rows disappearmove those predicates into ON
Using COUNT(*) after LEFT JOINmissing matches counted as 1use COUNT(r.pk)
Detecting missing match using nullable right columnsfalse positivescheck the right primary key for NULL
Joining on non-unique keysrow multiplicationjoin on PK/FK or pre-aggregate
Not qualifying columnsambiguous referencesuse table aliases and qualify

Quick Reference​

SELECT * FROM a LEFT JOIN b ON b.a_id = a.id;
SELECT * FROM a LEFT JOIN b ON b.a_id = a.id WHERE b.id IS NULL;
SELECT * FROM a LEFT JOIN b ON b.a_id = a.id AND b.status = 'active';
SELECT a.id, COUNT(b.id) FROM a LEFT JOIN b ON b.a_id = a.id GROUP BY a.id;
SELECT COALESCE(b.col, 'none') FROM a LEFT JOIN b ON b.a_id = a.id;

What's Next​