LEFT, RIGHT & FULL JOIN

Outer joins keep rows even when there is no match on the other side, filling in the missing columns with NULL. PostgreSQL supports three flavors: LEFT JOIN, RIGHT JOIN, and FULL JOIN.

LEFT JOIN: keep everything from the left table

A LEFT JOIN (short for LEFT OUTER JOIN) returns every row from the left-hand table, whether or not it has a match in the right-hand table. Unmatched rows get NULL for every column that came from the right table.

Every customer, including those with no orders yet

SQL

SELECT c.customer_id, c.first_name, c.last_name, o.order_id, o.order_date
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id;

customer_id | first_name | last_name | order_id | order_date
------------+------------+-----------+----------+------------
          1 | Maria      | Chen      |     1042 | 2024-06-02
          2 | David      | Okafor    |     1041 | 2024-06-01
          3 | Priya      | Nair      |     NULL | NULL

Priya has never placed an order, but she still appears in the result — with NULL in the order columns — because LEFT JOIN preserves every row from customers, the table on the left of the join.

RIGHT JOIN: the mirror image

A RIGHT JOIN is the same idea, flipped: it keeps every row from the right-hand table, filling in NULL for unmatched left-side columns.

Every order, plus any (nonexistent) unmatched customers

SQL

SELECT c.first_name, c.last_name, o.order_id
FROM customers c
RIGHT JOIN orders o ON o.customer_id = c.customer_id;

RIGHT JOIN is rarely seen in practice

Any RIGHT JOIN can be rewritten as a LEFT JOIN by simply swapping the order of the two tables — the query above is identical in meaning to orders LEFT JOIN customers. Because most developers find it more natural to always list the “keep everything from this table” table first, LEFT JOIN is used far more often, and RIGHT JOIN shows up mostly when adapting a query someone else wrote.

FULL JOIN: keep everything from both sides

A FULL JOIN (FULL OUTER JOIN) returns every row from both tables. Rows that match are combined normally; rows that don’t match still appear, with NULL on whichever side is missing.

Every customer and every order, matched where possible

SQL

SELECT c.customer_id, c.first_name, o.order_id
FROM customers c
FULL JOIN orders o ON o.customer_id = c.customer_id;

This is especially useful for finding mismatches in either direction at once — customers with no orders and orders that reference a customer that no longer exists, all in one result set.

Finding rows with no match

A very common pattern is combining a LEFT JOIN with a WHERE ... IS NULL check on a right-side key, to isolate rows that have no counterpart at all:

Customers who have never placed an order

SQL

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

Because unmatched rows get NULL for every right-side column, checking that the right-side key is NULL is a reliable way to isolate exactly the “no match” rows.

LEFT JOIN keeps every row from the left table
RIGHT JOIN keeps every row from the right table (rarely used — usually rewritten as LEFT JOIN)
FULL JOIN keeps every row from both tables
LEFT JOIN + WHERE right.key IS NULL finds rows with no match on the right