PostgreSQL Left Join
LEFT JOIN returns every row from the left table, attaching matching right-table data where it exists and NULL where it does not.
How LEFT JOIN Works
LEFT JOIN (also written LEFT OUTER JOIN) starts from the full set of rows in the left table. For each one, it attaches matching rows from the right table if any exist. When no match exists, the row is still included, but every column that would have come from the right table is filled with NULL.
Basic Left Join
SELECT c.name, o.id AS order_id
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id;Finding Rows With No Match
A very common pattern pairs LEFT JOIN with a WHERE clause checking that the right-side key is NULL. This isolates left-table rows that have no corresponding right-table entry at all, such as customers who have never placed an order.
Customers With No Orders
SELECT c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE o.id IS NULL;Left Join with Aggregation
Combining LEFT JOIN with COUNT and GROUP BY lets you report a zero for entities with no related rows, instead of silently dropping them the way an INNER JOIN would.
Order Count Including Zero
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.name
ORDER BY order_count ASC;- Every row from the left table appears at least once in the output.
- Unmatched rows get NULL in every right-table column.
- LEFT JOIN + IS NULL is the standard idiom for finding 'missing' relationships.
- COUNT(o.id) with LEFT JOIN correctly counts zero, unlike COUNT(*) which would count one.
