Joins confuse beginners because most explanations start with Venn diagrams. Let us start with data instead. Once you see what happens to actual rows, every join type makes sense.
The two tables
A fictional tiffin service, Dabba Express, keeps its customers in one table and orders in another.
| customer_id | name | city |
|---|---|---|
| 1 | Asha | Pune |
| 2 | Ravi | Indore |
| 3 | Meera | Pune |
| order_id | customer_id | amount |
|---|---|---|
| 101 | 1 | 250 |
| 102 | 1 | 180 |
| 103 | 2 | 300 |
| 104 | 4 | 220 |
Look closely: Meera (customer 3) has no orders, and order 104 belongs to customer 4, who is not in the customers table. Real data is full of cases like this, and they are exactly where join types differ.
INNER JOIN: only matches
SELECT c.name, o.order_id, o.amount
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id;An inner join keeps a row only when both sides match. Asha appears twice (two orders), Ravi once. Meera disappears (no orders) and order 104 disappears (no customer). Three rows in total.
LEFT JOIN: keep everything on the left
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id;Every customer stays. Meera now appears with NULL in order_id and amount, because there was nothing to match. Order 104 is still missing, because it is on the right side.
This is the join you will use most as an analyst, because business questions are usually about one main thing (customers, branches, products) and you do not want to lose the ones with no activity.
Classic interview question: "Find customers who have never ordered." Answer: LEFT JOIN orders and keep rows where the order side is NULL: WHERE o.order_id IS NULL.
RIGHT JOIN and FULL OUTER JOIN
A RIGHT JOIN is a left join written the other way round: every order stays, and order 104 shows NULL for the customer name. Most people simply swap the table order and use LEFT JOIN, which reads more naturally.
A FULL OUTER JOIN keeps everything from both sides: Meera with no order, and order 104 with no customer. It is useful for reconciliation, for example comparing a bank's ledger with a payment gateway's report to find entries that exist on only one side.
Mistake 1: filtering the right table in WHERE
Suppose you want every customer with their orders above ₹200, still keeping customers who have none.
-- Looks right, but drops Meera
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.amount > 200;The WHERE runs after the join. Meera's row has amount = NULL, and NULL > 200 is not true, so she is removed. Your left join silently became an inner join. Put the condition in the ON clause instead:
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o
ON o.customer_id = c.customer_id
AND o.amount > 200;Mistake 2: counting after a join that multiplies rows
Asha has two orders, so after the join she appears twice. If you then COUNT(*) customers per city, Pune is over-counted. When you join a "one" table to a "many" table, either count distinct keys (COUNT(DISTINCT c.customer_id)) or aggregate the many side first in a subquery or CTE, then join.
Mistake 3: joining on the wrong key
Joining on a column that is not unique on either side (like city) multiplies rows explosively. Before writing a join, ask: "For each row on the left, how many rows on the right should match?" If the answer should be "at most one", check that the right table's key really is unique.
Self and cross joins in one line each
- A self join joins a table to itself, for example an employees table where each row has a
manager_idpointing to another employee. - A cross join pairs every row with every row, useful for building a grid such as every store × every date, which you then left join actual sales onto.
Practise until it is automatic
Read this once, then go and write twenty joins. Our joins practice set starts with simple two-table matches and ends with multi-table interview problems, and every answer is checked instantly. When you are comfortable, move on to window functions.
Practise what you just read
Reading helps; writing the code yourself is what sticks. Start here: