Joining Two Excel Sheets With SQL in DuckDB or SQLite

Two sheets, one with orders and one with customer details, and a need to see them together. In Excel this is VLOOKUP or XLOOKUP in a helper column, copied down, producing one value per formula. SQL does the same thing for every row at once and answers questions the lookup cannot: which orders have no customer, which customers have no orders, which keys appear twice. This post shows how to join two sheets in DuckDB and SQLite, which join to use, and the checks that catch the mistakes joins invite. It is a spoke of doing proper analysis on Excel data with SQL.

Get both sheets in as tables

In DuckDB, query the workbook directly, or read each sheet with pandas:

import duckdb
import pandas as pd

orders = pd.read_excel("sales.xlsx", sheet_name="Orders", dtype={"customer_id": str})
customers = pd.read_excel("sales.xlsx", sheet_name="Customers", dtype={"customer_id": str})

Read the key as text on both sides. A key like 00123 read as a number on one sheet and as text on the other will not match, and a key read as 123 loses its zeros. If you use SQLite, load the frames with to_sql into a database file or :memory:. In DuckDB the DataFrames can be queried by their variable names.

Sample data:

Order 3 refers to C9, who is not in the customers sheet. Customer C3 has no orders.

Choose the join

The join type decides what happens to rows without a partner.

-- INNER: only rows that match on both sides
SELECT o.order_id, o.amount, c.name
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id;

order_id | amount | name
---------+--------+-------
1        | 100    | Acme
2        | 50     | Globex
4        | 25     | Acme

Order 3 vanished. An inner join silently drops unmatched rows, which is how totals shrink without warning. When the question is "total revenue", dropping an order is wrong.

-- LEFT: every order, with customer details where they exist
SELECT o.order_id, o.amount, c.name
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id;

order_id | amount | name
---------+--------+-------
1        | 100    | Acme
2        | 50     | Globex
3        | 75     | NULL
4        | 25     | Acme

Every order stays, with NULL where the customer is missing. This is the equivalent of XLOOKUP with a "not found" result, and it is usually the right default for facts joined to lookups. A FULL OUTER JOIN keeps unmatched rows from both sides, so Initech would also appear with a null order. Use it when you are comparing two lists and want everything.

Find what does not match

The most useful output of a join is often the list of failures. An anti join returns the rows with no partner:

-- Orders whose customer is not in the Customers sheet
SELECT o.*
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

-- Customers with no orders
SELECT c.*
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id);

The first returns order 3 and its C9. This is the list to send to whoever maintains the customers sheet. In Excel it takes a lookup column and a filter on #N/A. Use NOT EXISTS or LEFT JOIN ... IS NULL rather than NOT IN, because NOT IN with a NULL in the list returns no rows at all.

Duplicate keys multiply rows

If the right-hand side has the same key twice, each matching left row is repeated for each. Suppose the customers sheet listed C1 twice, once for each of two addresses:

An order for C1 now appears twice in the joined result, and SUM(amount) doubles it. This is the most common wrong-number cause with joins, and it produces no error. Test the lookup side before joining:

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

If any rows come back, decide what the right answer is: keep the latest, merge, or fix the sheet. Do not join and hope. Afterwards, check that the row count is unchanged:

SELECT (SELECT COUNT(*) FROM orders) AS before,
       (SELECT COUNT(*) FROM orders o LEFT JOIN customers c USING (customer_id)) AS after;

For a left join to a unique lookup, before and after must be equal. A bigger after means duplicates on the right.

Make the keys match

Joins compare values exactly. Text keys that differ in spaces or case do not match, and the cause is often invisible.

• Trailing spaces and non-breaking spaces: TRIM(customer_id) handles ordinary spaces, and a non-breaking space needs REPLACE(customer_id, chr(160), '') (trimming spaces and non breaking spaces).

• Case: UPPER(customer_id) on both sides, or a case-insensitive collation.

• Types: CAST(customer_id AS VARCHAR) on both sides when one sheet stored the key as a number.

• Leading zeros: make both sides text and pad if needed (LPAD(CAST(id AS VARCHAR), 5, '0')).

• Hidden characters: compare LENGTH before and after cleaning.

Do the cleaning in a subquery or a view, once, and join the cleaned columns:

WITH c AS (SELECT UPPER(TRIM(customer_id)) AS k, name, region FROM customers),
     o AS (SELECT UPPER(TRIM(customer_id)) AS k, order_id, amount FROM orders)
SELECT o.order_id, o.amount, c.name
FROM o LEFT JOIN c ON c.k = o.k;

Joining on a lowered, trimmed key is a lookup normalization that you can reuse.

Joining on more than one column

Some lookups need two keys: product and month, region and year. Put both in the ON clause:

JOIN prices p ON p.product = o.product AND p.month = date_trunc('month', o.order_date)

Check uniqueness on the pair. Joining on a range, such as an order date falling between a price's valid-from and valid-to, is also possible in SQL and has no neat spreadsheet equivalent:

JOIN prices p ON p.product = o.product
             AND o.order_date >= p.valid_from AND o.order_date < p.valid_to

Overlapping ranges in the price table produce duplicate matches, so test for overlaps first.

Joining across workbooks

The same approach joins sheets from different files. Read each workbook, name the frames, and join. This is the situation where Excel needs external links, which break when files move. With SQL, the files are inputs to a query you can rerun (merging hundreds of workbooks into one table handles many similar files).

Verify with totals

After the join, compare a measure with the source. SELECT SUM(amount) FROM orders before, and the same sum over the joined result after. For a left join to a unique lookup they must be equal. For an inner join, the difference is the amount of the unmatched orders, and the anti join lists them. Keep the numbers beside the query. If someone doubts a figure, you show them the counts and the unmatched rows.

SQLite notes

SQLite supports INNER, LEFT and, from version 3.39, RIGHT and FULL OUTER joins. In older versions, emulate a full join with a union of two left joins. Its flexible typing means that text '123' and number 123 compare as unequal unless you cast, so cast keys explicitly. Load sheets with explicit types by creating the tables first.

When a join of two sheets is the wrong approach

You need a lookup in the sheet itself. If the result must live beside the data in the workbook for others to use, a lookup formula or a Power Query merge keeps it inside Excel (VLOOKUP and XLOOKUP versus SQL JOIN).

The keys do not exist. Matching on names is fuzzy matching, with errors; get an identifier.

One-to-many belongs in a database. If you join the same two sheets every month, load them into tables with keys and constraints.

The sheets are small and one-off. A single lookup column in Excel may take less time than setting up a query.

See DuckDB's page on joins and the SQLite page on the SELECT statement and joins. To convert a small result to JSON, use the CSV to JSON converter.