SQL › Your First Joins

INNER JOIN, LEFT JOIN and RIGHT JOIN

Why joins exist

A relational database stores each kind of thing in its own table, linked by keys. Let's imagine an online shop: customers, wallets, products and reviews each get a table.

For example, a wallet's customer_id says who owns it.

A join combines tables in one query, so you can pick columns from all of them. For example, each customer's name next to their wallet balance.

The parts of a join

Every join has a left table, a join keyword, a right table and a join condition after ON. Left and right only mean which side of the keyword a table is on.

The joins run first and build one wide table; WHERE then filters it.

Order of execution: FROM + JOINs → WHERE → SELECT → ORDER BY

Code
SELECT a.column, b.column
FROM table_a a                            -- the left table
[INNER | LEFT | RIGHT] JOIN table_b b     -- the join keyword and the right table
  ON b.key = a.key                        -- the join condition
WHERE condition
ORDER BY a.column;

From the request to the tables

Find the table that holds each output column. Then join them on columns that hold the same values, usually a foreign key and the primary key it references.

The names don't have to match, only the values. Only the condition's columns are compared, not whole rows.

Let's imagine the grocery buyer wants each Grocery product with its category name. Both tables hold category_id, so that's the join condition:

Code
SELECT p.product_name, c.category_name
FROM products p
JOIN categories c ON c.category_id = p.category_id
WHERE c.category_name = 'Grocery'
ORDER BY p.product_name;

INNER JOIN

An inner join keeps a row only when its join-condition value is found in both tables; rows with no match on either side are dropped. JOIN alone means INNER JOIN.

Let's imagine the shop posts wallet statements to Kolkata and Kochi customers who have a wallet:

Code
SELECT c.full_name, c.city, a.account_no, a.balance
FROM customers c
INNER JOIN accounts a ON a.customer_id = c.customer_id
WHERE c.city IN ('Kolkata', 'Kochi')
ORDER BY c.full_name;        -- Rohan Das and Harish Pillai have no wallet, so they're left out

LEFT JOIN

A left join (LEFT OUTER JOIN) is the inner join plus every unmatched left row, with NULL in the right table's columns.

Use it when the request says "all" or "every" row of a table, and put that table on the left.

Now imagine the shop emails all its Kolkata and Kochi customers, with a balance where there is one:

Code
SELECT c.full_name, c.city, a.account_no, a.balance
FROM customers c
LEFT JOIN accounts a ON a.customer_id = c.customer_id
WHERE c.city IN ('Kolkata', 'Kochi')
ORDER BY c.full_name;        -- the same matches, plus Harish Pillai and Rohan Das with NULLs

RIGHT JOIN

A right join (RIGHT OUTER JOIN) adds the unmatched right rows instead. Swapping the tables turns it into a LEFT JOIN, which most people prefer.

Let's imagine the catalogue team wants the Toys and Smart Home categories, even empty ones:

Code
SELECT p.product_name, c.category_name
FROM products p
RIGHT JOIN categories c ON c.category_id = p.category_id
WHERE c.category_id IN (8, 13)
ORDER BY c.category_name, p.product_name;   -- Smart Home has no products, so its product is NULL
 
-- The same rows with the tables swapped and a LEFT JOIN
SELECT p.product_name, c.category_name
FROM categories c
LEFT JOIN products p ON p.category_id = c.category_id
WHERE c.category_id IN (8, 13)
ORDER BY c.category_name, p.product_name;

Same-named columns

When both tables have a column with the same name, a bare name is ambiguous: put the table's alias in front of it.

Let's imagine you list wallet payments with customer names; both tables have customer_id:

Code
-- Fails: both tables have customer_id
SELECT customer_id, amount FROM payments p JOIN customers c ON c.customer_id = p.customer_id;
 
SELECT p.payment_id, c.full_name, p.amount
FROM payments p
JOIN customers c ON c.customer_id = p.customer_id
WHERE p.method = 'wallet'
ORDER BY p.payment_id;

Joining more than two tables

Add one table at a time and check the result after each step.

The left side of a join is everything built so far, from FROM down to that keyword, not just the previous table. So an inner join after a LEFT JOIN drops its NULL rows again.

An inner join that loses nothing today can lose rows when new data arrives. A row that matches several rows appears once per match.

Let's imagine the Pune manager wants every Pune customer, their wallet balance and the products they reviewed:

Code
-- Step 1: every Pune customer has a wallet, so the inner join loses no one today
SELECT c.full_name, a.balance
FROM customers c
JOIN accounts a ON a.customer_id = c.customer_id
WHERE c.city = 'Pune'
ORDER BY c.full_name;
 
-- Step 2: the inner joins to reviews and products drop Pallavi Kulkarni, who wrote no reviews
SELECT c.full_name, a.balance, p.product_name
FROM customers c
JOIN accounts a ON a.customer_id = c.customer_id
JOIN reviews r ON r.customer_id = c.customer_id
JOIN products p ON p.product_id = r.product_id
WHERE c.city = 'Pune'
ORDER BY c.full_name, p.product_name;
 
-- Step 3: LEFT JOIN all the way keeps her; Anita Desai appears once per review
SELECT c.full_name, a.balance, p.product_name
FROM customers c
LEFT JOIN accounts a ON a.customer_id = c.customer_id
LEFT JOIN reviews r ON r.customer_id = c.customer_id
LEFT JOIN products p ON p.product_id = r.product_id
WHERE c.city = 'Pune'
ORDER BY c.full_name, p.product_name;

Rows without a match

After a LEFT JOIN, an unmatched row has NULL in every right-table column. Test the right table's key with IS NULL: a real match never has a NULL key.

Let's imagine marketing wants the products nobody has reviewed yet:

Code
SELECT p.product_id, p.product_name
FROM products p
LEFT JOIN reviews r ON r.product_id = p.product_id
WHERE r.review_id IS NULL
ORDER BY p.product_id;

ON vs WHERE

A condition in ON decides which rows match, and unmatched left rows still stay. In WHERE it runs after the join and removes rows, NULL ones too, so the LEFT JOIN acts like an inner join.

Let's imagine you want every product, with its 5-star reviews where there are any:

Code
-- Every product, with its 5-star reviews if any
SELECT p.product_name, r.rating
FROM products p
LEFT JOIN reviews r ON r.product_id = p.product_id AND r.rating = 5
ORDER BY p.product_id;
 
-- Only products that have a 5-star review
SELECT p.product_name, r.rating
FROM products p
LEFT JOIN reviews r ON r.product_id = p.product_id
WHERE r.rating = 5
ORDER BY p.product_id;

Remember

• Start from the output: find the table for each column, then join on columns that hold the same values.
• INNER JOIN keeps matches only; LEFT and RIGHT JOIN add the unmatched rows of their side, with NULLs.
• "All" or "every" on one table means a LEFT JOIN with that table on the left.
• The left side is everything joined before the keyword, so keep joining with LEFT JOIN after one.
• With a LEFT JOIN, put conditions on the right table in ON, not WHERE; find missing matches with IS NULL on the right table's key.

Practice

1. Who Bought What in Orders 1 to 5 (Easy)
2. Customers Who Never Ordered (Easy)
3. Every Customer and Their October Orders (Medium)