PostgreSQL LEFT JOIN | PostgreSQL Tutorial

Introduction to PostgreSQL LEFT JOIN

LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matching rows from the right table. When there is no match in the right table, the right-side columns contain NULL.

Syntax:

SELECT select_list
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

How LEFT JOIN works

For each row in the left table (table1), PostgreSQL looks for matching rows in the right table (table2):

If a match exists → create a result row with columns from both tables.
If no match exists → create a result row with columns from table1 and NULL for all table2 columns.

This means the left table row is never dropped, unlike INNER JOIN.

Example: customers with or without orders

SELECT
  c.customer_id,
  c.first_name,
  c.last_name,
  p.amount
FROM customer c
LEFT JOIN payment p ON p.customer_id = c.customer_id
ORDER BY c.customer_id;

Customers who have never made a payment appear in the results with NULL in the amount column.

Anti-join: finding rows with no match

A common pattern is using LEFT JOIN + WHERE IS NULL to find rows in the left table that have no corresponding row in the right table:

-- Customers who have never made a payment
SELECT c.customer_id, c.first_name, c.last_name
FROM customer c
LEFT JOIN payment p ON p.customer_id = c.customer_id
WHERE p.customer_id IS NULL;

Filtering on the right table: ON vs WHERE

Be careful when filtering right-table columns — WHERE on a right-table column removes NULL rows, converting LEFT JOIN to INNER JOIN behavior:

-- This becomes an INNER JOIN (rows with no payment are dropped):
SELECT c.first_name, p.amount
FROM customer c
LEFT JOIN payment p ON p.customer_id = c.customer_id
WHERE p.amount > 5.00;

-- This preserves LEFT JOIN (filter applied before the join):
SELECT c.first_name, p.amount
FROM customer c
LEFT JOIN payment p ON p.customer_id = c.customer_id AND p.amount > 5.00;

Counting with NULL awareness

To count how many payments each customer has (including customers with zero payments), use COUNT on the right-table column (NULL values are not counted by COUNT(col)):

SELECT
  c.customer_id,
  c.first_name,
  COUNT(p.payment_id) AS payment_count
FROM customer c
LEFT JOIN payment p ON p.customer_id = c.customer_id
GROUP BY c.customer_id, c.first_name
ORDER BY payment_count DESC;

Production tips

Always index the join columns on both tables to avoid sequential scans.
Remember: filtering right-table columns in WHERE converts LEFT JOIN to INNER JOIN semantics — use the ON clause when you want to filter while preserving NULLs.
For anti-join patterns, NOT EXISTS is often equivalent and sometimes faster than LEFT JOIN + IS NULL.
Use COALESCE(p.amount, 0) to replace NULL with a default value in output.

Reference: PostgreSQL documentation — JOIN types.

Frequently Asked Questions

What is a LEFT JOIN in PostgreSQL?

LEFT JOIN (also called LEFT OUTER JOIN) returns every row from the left table, even if no matching row exists in the right table. For non-matching rows, columns from the right table are filled with NULL. This makes LEFT JOIN essential when you need to keep all records from the primary table while optionally including data from a related table.

How do I find rows that have no match in the joined table (anti-join)?

Use a LEFT JOIN with a WHERE clause filtering for NULL on a non-nullable column of the right table: SELECT a.* FROM table_a a LEFT JOIN table_b b ON a.id = b.a_id WHERE b.a_id IS NULL. This returns only rows from table_a that have no corresponding row in table_b. This pattern is called an anti-join.

What is the difference between LEFT JOIN and LEFT OUTER JOIN?

They are identical. The OUTER keyword is optional in PostgreSQL — LEFT JOIN and LEFT OUTER JOIN produce exactly the same result. The OUTER keyword exists for SQL standard compatibility and readability.

Does a WHERE clause on the right table convert a LEFT JOIN into an INNER JOIN?

Yes. If you add a WHERE condition on a right-table column that filters out NULL values (e.g. WHERE b.status = 'active'), rows where b is NULL (non-matching rows) will be excluded, effectively turning the LEFT JOIN into an INNER JOIN. To preserve left-join behavior while filtering, move the condition into the ON clause: LEFT JOIN table_b b ON a.id = b.a_id AND b.status = 'active'.