Replace Excel VLOOKUP with SQL Joins

Context

Analysts often use Excel's VLOOKUP to pull columns from one table into another based on a shared key. In SQL, the equivalent idea is combining related tables with joins.

Core Question

Explain how you would translate an Excel VLOOKUP workflow into SQL. Your answer should cover:

1. Which SQL operation is most similar to VLOOKUP
2. How matching keys work across two tables
3. When to use INNER JOIN versus LEFT JOIN
4. What happens when there is no match in the lookup table
5. Common issues such as duplicate keys or missing values

Scope Guidance

The interviewer expects a practical explanation, not just a definition. You should describe the SQL pattern clearly, compare it to how VLOOKUP behaves in Excel, and mention the most common edge cases that affect correctness.

JOIN as the SQL equivalent of VLOOKUP

The closest SQL equivalent to Excel's VLOOKUP is a join between two tables using a shared key. Instead of pulling one cell formula down a spreadsheet, SQL combines rows from both tables in a single query.

SELECT s.order_id, s.customer_id, c.customer_name
FROM sales s
LEFT JOIN customers c
  ON s.customer_id = c.customer_id;

INNER JOIN vs LEFT JOIN

INNER JOIN returns only rows with matching keys in both tables. LEFT JOIN returns all rows from the left table and fills unmatched right-side columns with NULL, which is often closer to how analysts expect a lookup to behave.

SELECT s.order_id, c.customer_name
FROM sales s
LEFT JOIN customers c
  ON s.customer_id = c.customer_id;

Matching on a key column

A lookup works only if both tables share a reliable key such as customer_id, product_id, or email. If the key is not unique or contains inconsistent values, the result can duplicate rows or fail to match correctly.

SELECT o.order_id, p.product_name
FROM orders o
JOIN products p
  ON o.product_id = p.product_id;

Handling missing matches

When a row in the main table has no corresponding row in the lookup table, a LEFT JOIN keeps the row and returns NULL for the missing lookup columns. This is useful when you want to preserve the original dataset and identify unmatched records.

SELECT s.customer_id, c.customer_name
FROM sales s
LEFT JOIN customers c
  ON s.customer_id = c.customer_id
WHERE c.customer_id IS NULL;

Duplicate keys in the lookup table

Unlike Excel users often assume, SQL joins can return multiple matches for one key if the lookup table contains duplicates. That can multiply rows in the result, so interview answers should mention the importance of unique keys or pre-aggregation.

SELECT customer_id, COUNT(*)
FROM customers
GROUP BY customer_id
HAVING COUNT(*) > 1;