Joining Two CSV Files With SQL in DuckDB Without a Database

Two exports, one key. The customers file has names and segments, the orders file has amounts, and the question needs both. People reach for a spreadsheet lookup, a pandas merge or a database import for this. With DuckDB it is a join on two file names. This article joins a 2,000,000 row orders file to a 250,000 row customers file, then joins two small files whose keys do not quite match, which is the situation you will meet more often and which produces no error at all. The wider approach is in the DuckDB CSV guide, and the join syntax is described on the FROM clause page.

The simplest join

Each file name stands in for a table. A join between them looks like a join between any two tables:

duckdb -box -c "SELECT o.order_id, o.amount, c.name FROM read_csv('orders-2m.csv') o JOIN read_csv('customers-250k.csv') c ON c.customer_id = o.customer_id WHERE o.order_id <= 3 ORDER BY o.order_id"

┌──────────┬────────┬─────────────────┐
│ order_id │ amount │      name       │
├──────────┼────────┼─────────────────┤
│ 1        │ 103.1  │ Customer 022313 │
│ 2        │ 31.59  │ Customer 193489 │
│ 3        │ 89.98  │ Customer 163643 │
└──────────┴────────┴─────────────────┘

Orders 1, 2 and 3 with their amounts and the customer name looked up from the other file. I used read_csv with full paths here because the files live in another directory; a quoted path works the same way. The ON clause names the key on each side. When the two columns have the same name, USING (customer_id) is shorter and returns one customer_id column instead of two.

Join and aggregate, with nothing loaded

The reason to join in SQL instead of looking values up row by row is that the next step is usually an aggregate. Revenue and order count per customer segment, from the two files directly:

cd /batches/csv-0001-0200/work && time duckdb -box -c "SELECT c.segment, count(*) AS orders, round(sum(o.amount), 2) AS revenue FROM 'orders-2m.csv' o JOIN 'customers-250k.csv' c USING (customer_id) GROUP BY c.segment ORDER BY revenue DESC" 2>&1

┌───────────┬────────┬─────────────┐
│  segment  │ orders │   revenue   │
├───────────┼────────┼─────────────┤
│ online    │ 503514 │ 40309459.75 │
│ wholesale │ 500307 │ 40108102.92 │
│ partner   │ 498710 │ 39931531.58 │
│ retail    │ 497469 │ 39825552.11 │
└───────────┴────────┴─────────────┘

real	0m0.369s
user	0m0.551s
sys	0m0.191s

Four segments: 503,514 orders and 40,309,459.75 for online, then wholesale, partner and retail close behind. The four counts add up to exactly 2,000,000, so every order found its customer; if they summed to less, some customer_id values would be missing from the customers file, and an inner join would have dropped those orders without telling you. The command took 0.37 seconds of wall time, including starting DuckDB, parsing 130 MB and the customers file and building the hash table for the join.

In a hash join the smaller input is the one that has to fit in memory, so put the smaller file on the build side and watch memory if both are large; I did not test where this breaks down.

When the keys look the same and are not

Joins on identifiers fail quietly when the identifiers differ in ways you cannot see. Two small files with a customer key written by two different systems:

customer_id,name
 a-100 ,Ana
A-101,Bo
a-102,Cy

customer_id,city
A-100,Lyon
a-101 ,Oslo
A-103,Rome

In the first file, the first key is lowercase and has a space at each end. In the second, a-101 has a trailing space and A-100 is uppercase. To a person these are the same three or four customers. To a database they are different strings, and the join on = returns nothing:

duckdb -box -c "SELECT a.customer_id AS a_key, b.customer_id AS b_key, name, city FROM 'keys-a.csv' a JOIN 'keys-b.csv' b ON a.customer_id = b.customer_id"

┌───────┬───────┬──────┬──────┐
│ a_key │ b_key │ name │ city │
├───────┼───────┼──────┼──────┤
└───────┴───────┴──────┴──────┘

An empty table and no error. This is the dangerous result, because it can be mistaken for "these files have nothing in common". When a join returns fewer rows than you expect, or none, look at the keys before you look at anything else: select the key columns from each file with quote() or length() and read them.

The fix is to normalize both sides in the join condition, trimming whitespace and choosing a case:

duckdb -box -c "SELECT trim(a.customer_id) AS a_key, trim(b.customer_id) AS b_key, name, city FROM 'keys-a.csv' a JOIN 'keys-b.csv' b ON upper(trim(a.customer_id)) = upper(trim(b.customer_id))"

┌───────┬───────┬──────┬──────┐
│ a_key │ b_key │ name │ city │
├───────┼───────┼──────┼──────┤
│ a-100 │ A-100 │ Ana  │ Lyon │
│ A-101 │ a-101 │ Bo   │ Oslo │
└───────┴───────┴──────┴──────┘

Now A-100 (Ana, Lyon) and A-101 (Bo, Oslo) match. Put the same expression on both sides, always: a clean key on one side and a raw key on the other is just as broken as two raw ones. If the cleaning is more than upper(trim(x)), such as stripping hyphens or leading zeros, write it once in a subquery or a WITH clause that exposes a clean key column, and join on that. Then there is one place to change when the next file brings a new variation.

Left joins and anti joins: what did not match

An inner join shows what matched. The more useful questions are often about what did not. A LEFT JOIN keeps every row of the first file, with NULL in the columns from the second where there is no match:

duckdb -box -c "SELECT upper(trim(a.customer_id)) AS key, a.name, b.city FROM 'keys-a.csv' a LEFT JOIN 'keys-b.csv' b ON upper(trim(a.customer_id)) = upper(trim(b.customer_id)) ORDER BY 1"
duckdb -box -c "SELECT upper(trim(b.customer_id)) AS key, b.city FROM 'keys-b.csv' b ANTI JOIN 'keys-a.csv' a ON upper(trim(a.customer_id)) = upper(trim(b.customer_id))"

┌───────┬──────┬──────┐
│  key  │ name │ city │
├───────┼──────┼──────┤
│ A-100 │ Ana  │ Lyon │
│ A-101 │ Bo   │ Oslo │
│ A-102 │ Cy   │ NULL │
└───────┴──────┴──────┘
┌───────┬──────┐
│  key  │ city │
├───────┼──────┤
│ A-103 │ Rome │
└───────┴──────┘

The first table has all three customers from the first file. A-102 (Cy) is there with city NULL, because the second file has no A-102. The second table is an ANTI JOIN the other way around: the rows of the second file with no partner in the first. A-103 (Rome) appears in the second file alone. DuckDB spells the anti join out as its own keyword, where other databases need a LEFT JOIN ... WHERE b.key IS NULL or a NOT EXISTS. Together, the two answer the reconciliation question, which keys are in A only and which in B only, in two short queries. I run both before I trust any join of two files from two sources.

When the key types disagree

Types in a join matter as well. The customers file has a numeric id; a second file has text keys such as C-1:

printf 'customer_id,tier\n1,gold\n2,silver\n' > tiers.csv
printf 'customer_id,tier\nC-1,gold\n' > tiers-text.csv
duckdb -c "SELECT * FROM 'customers.csv' c JOIN 'tiers-text.csv' t ON c.id = t.customer_id" 2>&1 | head -5

Conversion Error: Could not convert string 'C-1' to INT64 when casting from source column customer_id

LINE 1: ... * FROM 'customers.csv' c JOIN 'tiers-text.csv' t ON c.id = t.customer_id
                                                                       ^^^^^^^^^^^^^
hint: -agent renders errors as JSON and results compactly for AI coding agents (duckdb -help lists all options)

Conversion Error: Could not convert string 'C-1' to INT64 when casting from source column customer_id. DuckDB tried to compare an integer with a string by converting the string, and the first value that was not a number stopped the query. It is a useful failure, since it names the column. The usual cause is a sniffed type that differs between files: one file has only numeric keys and is read as BIGINT, the other has one text key and is read as VARCHAR. The fix is to read the key column as VARCHAR on both sides with types, as in the type fixing article, and join as text; the types parameter is described in the CSV import overview. If the keys have leading zeros in one file and not in the other, which is the same problem in disguise, lpad(key, 6, '0') on the side without them makes them equal.

How to tell it worked

Count rows before and after. An inner join on a unique key should return no more rows than the larger input and no fewer than the number of matches you expect, and a one to many join should return the row count of the many side. Run the anti join in both directions and read the unmatched keys. Check for duplicate keys on the side that should be unique, with SELECT key, count(*) FROM file GROUP BY key HAVING count(*) > 1: a duplicate in the customers file would multiply every order that references it, and the totals would be inflated with no complaint. And compare one total with a figure you trust, such as the revenue of the orders file alone, which should be unchanged by a join that found a customer for every order.

When a join on files is the wrong move

If you will join the same two files many times, convert them to Parquet once, as the conversion article shows, so that the text parsing happens once. If the key is fuzzy, such as names that are spelled differently in two systems, a join on equality will not help and you need a matching step first. If one of the files is too large for the machine and the other is too, a database with indexes, or a partitioned approach, is the better tool. And if the point is to see the differences between two versions of the same file, a join on a key is a clumsy way to do it, and a comparison tool that shows added, removed and changed rows is clearer.