LEFT JOIN and RIGHT JOIN

Objectives

By the end of this lesson, you should be able to:

• Keep every row from one table with LEFT JOIN, matched or not
• Keep every row from one table with RIGHT JOIN, matched or not
• Explain why most PostgreSQL code favors LEFT JOIN over RIGHT JOIN

💡 Why this matters: “Every customer, including ones with no orders yet” is a different, equally common question from “only customers who’ve ordered something.” INNER JOIN can only answer the second one, LEFT JOIN answers the first.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database, using the same customers and orders tables as the previous lesson.

LEFT JOIN

SELECT c.name, o.order_date, o.amount
FROM customers c LEFT JOIN orders o ON c.id = o.customer_id
ORDER BY c.name, o.order_date;

     name        | order_date | amount
------------------+------------+---------
 Casey Brooks    | 2024-01-20 |  75.00
 Morgan Alvarez  |            |
 Riley Nguyen    | 2024-01-10 | 120.00
 Riley Nguyen    | 2024-02-15 |  45.00

LEFT JOIN keeps every row from the left table (customers, the one written first) no matter what. Morgan Alvarez now appears, with order_date and amount both NULL, since she has no matching row in orders, PostgreSQL fills in the missing side with NULLs rather than dropping her. “Left” refers to which side of the JOIN keyword the table appears on, customers is written to the left of LEFT JOIN.

RIGHT JOIN

SELECT c.name, o.order_date, o.amount
FROM orders o RIGHT JOIN customers c ON o.customer_id = c.id
ORDER BY c.name, o.order_date;

     name        | order_date | amount
------------------+------------+---------
 Casey Brooks    | 2024-01-20 |  75.00
 Morgan Alvarez  |            |
 Riley Nguyen    | 2024-01-10 | 120.00
 Riley Nguyen    | 2024-02-15 |  45.00

RIGHT JOIN keeps every row from the right table, customers here, the one written second. This produces an identical result to the LEFT JOIN above, just with orders and customers swapped in the FROM clause, “keep every row from customers” is expressed either as customers LEFT JOIN orders or orders RIGHT JOIN customers, both say the same thing.

Why LEFT JOIN Is More Common

Since LEFT JOIN and RIGHT JOIN can always express the same result just by swapping table order, most real PostgreSQL code sticks to LEFT JOIN exclusively, one consistent pattern to read rather than switching between two. RIGHT JOIN isn’t wrong, it’s just rarely necessary once LEFT JOIN is the default habit.

Try It

1. Write a LEFT JOIN from customers to orders, and confirm Morgan Alvarez appears with NULL values.
2. Rewrite the same query as a RIGHT JOIN (with the table order flipped) and confirm the result is identical.
3. Add WHERE o.id IS NULL to the LEFT JOIN query, this isolates customers with zero orders, explain in your own words why this pattern works.
4. Explain, in your own words, what would happen to Morgan Alvarez’s row if this had been an INNER JOIN instead.

Recap

• LEFT JOIN keeps every row from the left (first-written) table, filling unmatched columns from the right table with NULL.
• RIGHT JOIN keeps every row from the right (second-written) table, the mirror image of LEFT JOIN.
• The same result can always be expressed as either a LEFT JOIN or a RIGHT JOIN by swapping table order, most code sticks to LEFT JOIN for consistency.
• LEFT JOIN ... WHERE right_table.column IS NULL is a common pattern for finding rows with no match at all.

Next lesson: FULL JOIN, keeping every row from both tables at once.