SQL JOIN vs Excel VLOOKUP (Same Idea)
If you can write a VLOOKUP, you already understand a SQL JOIN. See the lookup value, the lookup table and the returned columns side by side, and why a LEFT JOIN behaves like VLOOKUP when a match is missing.
Step by step
Same idea
- JOIN is a lookup. VLOOKUP and a SQL JOIN both get data from another table by matching an ID. Here we build one JOIN and compare it with VLOOKUP part by part.
- The orders table. The orders table holds a customer_id for each order but not the customer's name. In Excel you would add a VLOOKUP column to bring the name in.
Lookup value
- Lookup value. Start by selecting order_id and customer_id from orders, with the short name o. The customer_id is the lookup value, the first thing you give VLOOKUP. Run it and you get 141 rows, one per order. Note this number, because you will check it again after the join.
SELECT o.order_id, o.customer_id FROM orders AS o;
Lookup table
- Lookup table. The names are in the customers table. This plays the part of VLOOKUP's table array. Its customer_id column is the key we match on. Add JOIN customers AS c ON c.customer_id = o.customer_id. The ON line says which columns must match, like VLOOKUP matching the first column of its range. With = it is an exact match.
JOIN customers AS c ON c.customer_id = o.customer_id;
Return columns
- Return columns. Now choose what comes back. VLOOKUP needs a column number and a separate formula for each column. In SQL you just name the columns, so add c.first_name and c.region to the SELECT list together. Run it. Each order now shows a first name and a region, but there are only 140 rows. 1 order has gone missing.
o.customer_id, c.first_name, c.region FROM
No match
- No match. That order points at a customer who isn't in the customers table. VLOOKUP would show #N/A, but a plain JOIN drops the row without any warning. Change JOIN to LEFT JOIN to keep every order. Run it again and you are back to 141 rows. Every order is there, just as a VLOOKUP column has a value or an error on every row.
LEFT JOIN customers - Find the #N/A. When nothing matches, the customer columns come back as NULL. NULL is SQL's version of #N/A. Add WHERE c.customer_id IS NULL to list only those rows. It returns 1 order, for customer 99, who isn't in the customers table. That is a data problem to raise with the data owner.
WHERE c.customer_id IS NULL; - The row count. Check the row count after every join. Fewer rows than you started with means some rows didn't match. More rows usually means the lookup table has the same key more than once.
Recap
- JOIN vs VLOOKUP. To sum up: the ON line holds the lookup value, the joined table is the table array, and the SELECT list holds the return columns. Use LEFT JOIN to keep every row, like VLOOKUP does.
Common mistakes
- Using a plain JOIN and not checking the row count. A plain JOIN (an INNER JOIN) drops unmatched rows without any warning, so 141 orders can quietly become 140. Use LEFT JOIN when every row must stay, and compare the row count before and after.
- Writing = NULL instead of IS NULL. WHERE c.customer_id = NULL returns no rows, because nothing is equal to NULL. Write WHERE c.customer_id IS NULL to find the unmatched rows.
- Filtering the joined table in WHERE after a LEFT JOIN. A filter like WHERE c.region = 'North' removes the unmatched rows, because their region is NULL, so the LEFT JOIN acts like a plain JOIN. Put that condition in the ON line if you want to keep every order.
- Duplicate keys in the lookup table. VLOOKUP returns only the first match, but a JOIN returns a row for every match, so a customer listed twice doubles their orders. Check that customer_id is unique in customers before you join.
When a business analyst uses this
When reconciling a data migration, a business analyst LEFT JOINs the migrated orders to the new customers table and adds WHERE c.customer_id IS NULL. Each row returned is an order with no matching customer, and that list goes into the defect log or RAID log before sign-off.
Questions people ask
Is a SQL JOIN the same as VLOOKUP?
The idea is the same: match an ID and bring back columns from another table. A JOIN can return many columns at once and returns every match, while VLOOKUP returns 1 column per formula and only the first match.
What is the difference between JOIN and LEFT JOIN?
A plain JOIN keeps only rows that have a match in both tables. A LEFT JOIN keeps every row from the first table and fills the other table's columns with NULL where nothing matched.
How do I find rows that didn't match in a SQL join?
Use a LEFT JOIN, then add WHERE on the joined table's key, for example WHERE c.customer_id IS NULL. Only the rows with no match are returned.
