LEFT, RIGHT and FULL joins: keeping rows with no match

Part of the free LogicWiz SQL & Databases course, Module 5: Joins. The queries and practice run in the app on real Postgres.

Marketing wants a ₹100 coupon for every customer who signed up but never ordered. An inner join with orders can't even see those customers. So how do you find rows that have no partner?

You'll learn to

Keep every row of one table with LEFT JOIN
Find rows with no match (the anti-join)
Put a filter in ON or in WHERE on purpose
Know what RIGHT and FULL joins add

Learn

LEFT JOIN keeps the left table whole

A LEFT JOIN B returns every pair an inner join would, plus each row of A that matched nothing. For those rows, B's columns are filled with NULL.

The table written first (left of the word JOIN) is the one that is kept whole. So you choose: "I want every customer, with orders where they exist" means customers LEFT JOIN orders.

Every customer, with or without orders

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

26 rows: the 24 orders, plus Diya Joshi (11) and Arjun Mehta (12) once each with order_id NULL. An inner join would show 24.

The anti-join: who never ordered?

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

Keep everyone, then keep only those where the order side came back empty. o.order_id is the primary key of orders, so it is NULL only when there was no match. This "LEFT JOIN … IS NULL" pattern is called an anti-join.

The ON-vs-WHERE trap. You want every restaurant with its delivered orders:

FROM restaurants r LEFT JOIN orders o ON … WHERE o.status = 'delivered'

WHERE runs after the join. For Pizza Corner, o.status is NULL, NULL = 'delivered' is not true, and the row is thrown away. Your LEFT JOIN quietly became an inner join. A condition on the right table belongs in ON: ON o.restaurant_id = r.restaurant_id AND o.status = 'delivered'.

See the difference

-- filter in WHERE: restaurants with no delivered order vanish
SELECT r.name, o.order_id
FROM restaurants r
LEFT JOIN orders o ON o.restaurant_id = r.restaurant_id
WHERE o.status = 'cancelled';

-- filter in ON: every restaurant stays, NULL where nothing matched
SELECT r.name, o.order_id
FROM restaurants r
LEFT JOIN orders o ON o.restaurant_id = r.restaurant_id
                  AND o.status = 'cancelled'
ORDER BY r.name;

The first result has 2 rows; the second has all 8 restaurants. Neither is wrong; they answer different questions. Know which one you are asking.

RIGHT and FULL

A RIGHT JOIN B keeps every row of B. It is exactly B LEFT JOIN A, so most people just swap the tables and always write LEFT.
A FULL JOIN B keeps unmatched rows from both sides, with NULLs on whichever side is missing.

FULL is handy for reconciling two lists: "which orders have no partner, and which partners have no order?" in one query.

FULL JOIN: gaps on both sides

SELECT p.name AS partner, o.order_id
FROM orders o
FULL JOIN partners p ON p.partner_id = o.partner_id
WHERE p.partner_id IS NULL
   OR o.order_id IS NULL;

Three orders were never assigned a partner, and Anil has never been given an order. Both kinds of gap show up together.

MySQL has LEFT and RIGHT joins but no FULL OUTER JOIN. You emulate it with a LEFT JOIN UNION a RIGHT JOIN (UNION comes in Module 6).

"Find customers who never placed an order" is one of the most asked SQL interview questions. Know three answers: LEFT JOIN … WHERE right.key IS NULL, NOT EXISTS, and NOT IN (which has a NULL trap, Module 6). Test IS NULL on a column that can't be NULL in a real match, like the right table's primary key.

In one line

LEFT JOIN keeps every left row; WHERE right.pk IS NULL finds rows with no match; filters on the right table go in ON.