SQL JOIN explained with Excel: INNER JOIN and LEFT JOIN side by side

The SQL query and its spreadsheet twin

The query SELECT o.order_id, o.amount, c.name FROM orders o LEFT JOIN customers c ON o.customer_id = c.customer_id walks through every order, looks up its customer_id in the customers table and adds the name. In Excel that is one lookup per row:

=XLOOKUP(B2,$G$2:$G$5,$H$2:$H$5,"Unknown")

The ON clause is the lookup key (B2 against G2:G5), the selected column c.name is the return range H2:H5, and the left table is simply the table you write the formula in. With VLOOKUP the same join is =IFNA(VLOOKUP(B2,$G$2:$H$5,2,FALSE),"Unknown"). The FALSE matters: a join is an exact match.

INNER JOIN: only the rows where the lookup succeeds

An INNER JOIN drops every order whose customer is missing. In a spreadsheet, flag the rows that would survive with =IF(ISNUMBER(MATCH(B2,$G$2:$G$5,0)),1,0) and use the flag in your totals: =SUM(E2:E7) counts the joined rows and =SUMPRODUCT(C2:C7,E2:E7) adds up their revenue.

This is why an INNER JOIN can quietly lose money from a report. Orders 1004 and 1006 (190 in revenue) belong to customer ids C07 and C09, which are not in the customer table. In SQL they simply disappear from the result, with no error. In Excel you at least see the #N/A.

Worked example: six orders joined to four customers

Orders (A:C) and customers (G:H) are yellow inputs. Column D is the LEFT JOIN, column E flags the rows an INNER JOIN keeps, and the totals compare both.

Worked example: six orders joined to four customers

	A	B	C	D	E	F	G	H
1	order_id	customer_id	amount	LEFT JOIN: name	In INNER JOIN?		customer_id	name
2	1,001	C01	120	Bluebird Café	1		C01	Bluebird Café
3	1,002	C03	75	Pine Street Bakery	1		C02	Northside Deli
4	1,003	C02	240	Northside Deli	1		C03	Pine Street Bakery
5	1,004	C07	60	Unknown	0		C04	Harbor Hotel
6	1,005	C01	95	Bluebird Café	1
7	1,006	C09	130	Unknown	0
8
9	Rows in the LEFT JOIN (every order)				6
10	Rows in the INNER JOIN (matched orders)				4
11	Revenue in the INNER JOIN				530
12	Revenue in the LEFT JOIN				720
13	Orphan orders (name = Unknown)				2
14	Customers without any order (anti join)				1

Invented orders and customers. The same logic works with VLOOKUP and IFNA in older Excel versions and in Google Sheets.

LEFT JOIN and NULL: keep every row, label the gaps

A LEFT JOIN keeps all six orders. Where no customer matches, SQL returns NULL in the customer columns, the equivalent of #N/A from a lookup. In reports, replace it with a label: COALESCE(c.name, 'Unknown') in SQL, the fourth argument of XLOOKUP or IFNA(…,"Unknown") in Excel. Counting the label, =COUNTIF(D2:D7,"Unknown"), tells you how many orphan rows the data has.

Use a LEFT JOIN whenever the left table is the thing you are counting, such as all orders or all employees, and an INNER JOIN only when unmatched rows really should be excluded.

Finding unmatched rows in both directions

Data checks often need the rows that did not join. Orders without a customer: LEFT JOIN … WHERE c.customer_id IS NULL, in Excel the rows labelled Unknown. Customers without any order (an anti join): in Excel, =SUMPRODUCT(--(COUNTIF(B2:B7,G2:G5)=0)) counts the customers whose id never appears in the orders, here one, Harbor Hotel.

RIGHT JOIN is a LEFT JOIN with the tables swapped, and FULL OUTER JOIN keeps unmatched rows from both sides. In a spreadsheet you build those by running the lookup from the other table as well.

The duplicate-key trap

Lookups and joins behave differently when the key is not unique. XLOOKUP and VLOOKUP return the first match only. A SQL JOIN returns every match, so if customer C01 appears twice in the customer table, each C01 order appears twice in the result and the revenue is double-counted.

Before you trust a join, check that the key is unique on the lookup side: =COUNTIF($G$2:$G$5,G2) must be 1 for every customer, or in SQL SELECT customer_id, COUNT(*) FROM customers GROUP BY customer_id HAVING COUNT(*) > 1 must return nothing.

Common mistakes

• Using an INNER JOIN by default. It silently drops unmatched rows, and totals shrink without an error.
• Approximate matching. A VLOOKUP without FALSE as the last argument is not a join and can return the wrong customer.
• Duplicate keys on the lookup side. SQL repeats rows and double-counts, while a lookup hides the duplicate. Check uniqueness first.
• Mixed key formats. "C01" and "C01 " or 1001 as text and as a number do not match. Clean keys with TRIM and consistent types.
• Filtering the right table in WHERE. WHERE c.segment = 'Retail' after a LEFT JOIN removes the NULL rows and turns it into an INNER JOIN. Put the condition in the ON clause.
• Ignoring the orphans. Rows labelled Unknown usually point to a data problem worth fixing at the source.