SQL · Joins

JOINs, and why LEFT JOIN is VLOOKUP's big sibling

"How is a LEFT JOIN different from VLOOKUP?" is a favourite bridge question between the Excel and SQL rounds. You already know VLOOKUP, so we'll build from it.

Your win todayPredict, row by row, what INNER and LEFT JOIN return, including NULLs and duplicated rows, and explain the VLOOKUP comparison in two sentences.

Two tables

customers

id	name
1	Asha
2	Ravi
3	Meera

orders

order_id	customer_id	amount
101	1	500
102	1	300
103	2	700
104	9	200

Note: Meera (3) has no orders, and order 104 points to customer 9, who doesn't exist.

The join family

Shaded = rows kept. Where a kept row has no partner, the partner's columns come back as NULL.^[PostgreSQL] (A Venn picture is a simplification. The next figure shows the thing it hides: duplicates.)

LEFT JOIN, row by row

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

Rule: every left row appears at least once. Once per match, or once with NULLs if there's no match.

LEFT JOIN vs VLOOKUP

	VLOOKUP	LEFT JOIN
Keeps every row of the main table?	✅ (the formula column sits beside it)	✅
No match	`#N/A`	`NULL`
Several matches	Returns the first one only	Returns all of them. The row count grows.
Columns brought back	One per formula	As many as you list in SELECT
Match on several keys	Needs a helper column	`ON a.x = b.x AND a.y = b.y`
Scale	Slows on big sheets	Built for millions of rows

The trapA LEFT JOIN onto a "many" table multiplies rows. If you then SUM a column from the left table, it gets double-counted. Check the row count before and after a join.

Two handy patterns

-- Anti-join: customers who never ordered
SELECT c.name FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.order_id IS NULL;          -- → Meera

-- Filter trap: a WHERE on the right table quietly turns LEFT into INNER
LEFT JOIN orders o ON o.customer_id = c.id AND o.amount > 400   -- keeps all customers
LEFT JOIN orders o ON o.customer_id = c.id WHERE o.amount > 400 -- drops Meera (NULL > 400 is not true)

Say it in the interview"A LEFT JOIN keeps every row from the left table and attaches matching rows from the right. Where there's no match, the right-side columns are NULL. It's like VLOOKUP with two differences: VLOOKUP returns only the first match and gives #N/A, while a LEFT JOIN returns every match, so rows can multiply, and gives NULL. It can also bring back many columns and join on several keys at once."

Retrieval check

How many rows does customers INNER JOIN orders return here?

In a LEFT JOIN, what does an unmatched left row get?

A key has 3 matches. VLOOKUP returns 1 value. What does a LEFT JOIN return?

Which query finds customers with no orders?

Type it

How many rows does the LEFT JOIN above return?

Now flip it: orders LEFT JOIN customers. How many rows?

Your turn

Open a free browser SQL sandbox, e.g. sqliteonline.com. Create both tables (ask your teacher for the CREATE/INSERT script).
Run INNER and LEFT joins and check your row-count predictions (3 and 4).
Run the anti-join and get back Meera.
Run both versions of the amount > 400 filter (ON vs WHERE) and compare the results.
In Excel, VLOOKUP each customer's amount from the orders sheet. Asha shows only 500. Explain why that differs from the join.
Say the LEFT JOIN vs VLOOKUP answer out loud in under 30 seconds.

Go deeper

Primary source: PostgreSQL Docs: Joins Between Tables. It's short and official, and it shows NULL-filled outer-join rows. For practice: StrataScratch join questions.

💬 Ask your teacher: "give me the CREATE TABLE script" or "what's a self join / cross join?"