Join two files on a shared column

Drop two files in, say which column in each identifies the same thing, and pick how much of each side you want to keep. The result appears immediately with a match report above it: how many rows found a partner, how many did not, and how many keys in the second file went unused. Both files are read in this tab. Neither one is uploaded.

A join is a question about two files, and the answer is a number

Every join tool will hand you a table. Very few of them tell you whether the join actually worked, and that is the only thing worth knowing before you use the output for anything. A join that silently matched a third of your rows produces a file that looks completely normal, opens fine in a spreadsheet, and is wrong.

So this page prints the arithmetic above the result, every time:

Left join on customer_id · A rows 10 · B rows 6 · matched A 9 (90%)
unmatched A 1 · unmatched B keys 1 · rows out 10

Six numbers, and between them they catch nearly every way a join goes wrong. Matched A below what you expected means the keys do not line up, usually because one file quotes its ids and the other does not, or one has trailing spaces from an export. Rows out higher than A rows means the second file has repeated keys and the join multiplied your data, which is the single most expensive mistake in this whole category: totals computed on that file are inflated and nothing about the file itself looks unusual. Unmatched B keys counts the records in the second file that nothing pointed at, which is how you find the customers who have never ordered.

Worked example: ten orders and six customers

Here is orders.csv, ten rows. It is the sample the page loads if you click Try with sample data:

order_id,customer_id,order_date,region,product,quantity,amount
1001,C001,2024-01-05,East,Widget,3,120.50
1002,C002,2024-01-06,West,Gadget,1,88.00
1003,C001,2024-01-11,East,Thing,2,240.00
...
1009,C009,2024-03-21,West,Gadget,3,132.40

And customers.csv, six rows:

customer_id,name,country,segment,signup_date
C001,Ada Lovelace,United Kingdom,Enterprise,2023-04-11
C002,Grace Hopper,United States,Enterprise,2023-06-02
...
C077,Radia Perlman,United States,Small business,2024-02-17

The sample is built to be awkward on purpose. Order 1009 belongs to C009, who is not in the customer list. Customer C077 has never placed an order. That gives you a non-zero figure on both sides of the match report from the first click, which is the point: a sample where everything matches teaches you nothing about what the tool does when things do not.

Left join on customer_id and you get ten rows out, one per order, with name and country attached. Order 1009's two new columns are blank, and the report says unmatched A 1. Switch to Anti and you get exactly that one row on its own, which is the list you would hand to whoever maintains the customer table. Switch to Inner and you get nine rows, with 1009 dropped.

Six join types, in the order people need them

Left keeps every row of the first file and attaches what it can find. This is the default because it is the only one that cannot lose a row you already had, and losing rows without noticing is the failure everyone regrets.
Inner keeps only rows that matched on both sides. Right when the join is a filter as much as a lookup: paid invoices that also appear in the bank export.
Right keeps every row of the second file instead. Useful when you loaded the two files in the order you happened to find them rather than the order the question wants.
Full outer keeps everything from both sides, with blanks wherever a partner is missing. This is the reconciliation view: every record that exists anywhere, and a visible gap wherever one system knows about something the other does not.
Anti returns the rows of the first file that found no match, and only the first file's columns. Orders with no customer. Employees with no manager. Line items with no product code. Most tools make you run a left join and then filter for blanks by hand; this is one dropdown.
Semi returns the rows of the first file that did match, once each, with none of the second file's columns. It is a filter that cannot duplicate rows, which matters when the lookup table has several rows per key and you only wanted to know whether a key was present.

Compound keys, and the three toggles that fix most non-matches

One column is not always enough. Sales by region and month, inventory by warehouse and SKU, a schedule by employee and date: the thing that identifies a row is two or three or four columns together. Add up to four pairs and every one has to agree before two rows are considered a match. The names do not have to be identical on both sides, which is the usual state of affairs when the two files came from different systems.

When a join matches nothing and you are sure the key is right, it is almost always one of three things, so all three are one click away:

Surrounding spaces is set to Ignore by default. Fixed-width exports pad every cell, and 'C001 ' is not 'C001' to any string comparison in the world.
Letter case is exact by default, because ABC123 and abc123 really are different in plenty of systems. Set it to Ignore when one side has been through a spreadsheet that helpfully retyped everything.
Numbers compares as text by default so leading zeros survive. Switch it to 1.0 = 01 = 1 when one file exported its ids as numbers and the other as text, which is the single most common cause of a join that matches zero rows out of fifty thousand.

The equivalent SQL, and why it is on the page

Under every result there is a collapsed panel holding the DuckDB query that does exactly what the dropdowns above it just described. For the worked example it reads:

SELECT a.*, b."name", b."country", b."segment", b."signup_date"
FROM 'orders.csv' a
LEFT JOIN 'customers.csv' b
  ON trim(a."customer_id") = trim(b."customer_id")
;

It is not decoration. Turn on the key toggles and the query changes with them, so the trim() calls appear and disappear as you click. Pick Anti and it becomes a NOT EXISTS subquery, which is the correct SQL for that question rather than a left join with a WHERE clause bolted onto it. Copy it, take it to SQL in the browser, and run it over the same two files.

Two reasons this earns its place. The first is that a visual tool with six dropdowns will eventually run out of road, and when it does, the fastest way onward is a query you already have three quarters of. The second is that this is how people learn joins. Clicking Anti and watching the SQL turn into NOT EXISTS teaches the shape of the thing in a way no tutorial does.

What it accepts, and what it refuses

Formats mix freely. CSV, TSV, TXT, Excel, JSON and JSON Lines, and the two files do not have to match. Joining a JSON export to a spreadsheet is a normal Tuesday.
Workbooks never take Sheet1 silently. Every sheet holding data is found, the one with the most rows is read, and the page says which one it chose. Reading the wrong sheet without a word is a bug we found in a competitor's diff tool and did not want to reproduce.
Duplicate keys are reported, not hidden. If the second file has three rows for one key, SQL says you get three rows out, and so does this. What SQL does not do is tell you it happened; the warning above the result names the number of extra rows so you can decide whether that was the intention.
Colliding column names get a suffix. Both files carrying region gives you region and region_b, and the warning lists what was renamed. Nothing is overwritten.
A runaway join stops. Two files with a repeated key on both sides multiply into a cross product. Past two million rows the tool stops and says the key is not unique on either side, which is the actual problem, rather than freezing the tab while it builds a file nobody wants.
100 MB per file. Above that the browser tab is the wrong place, and the page points at the full editor, which streams rather than holding everything in memory.

Frequently Asked Questions

What is the difference between a join and just pasting two files side by side?

Pasting lines up row 1 with row 1, row 2 with row 2, and so on. That is only correct if both files are already sorted the same way and neither has a missing row, which is almost never true of two exports from two systems. A join lines rows up by a value they share, so it does not care what order either file is in, and it tells you about the rows that had no partner instead of quietly shifting everything down by one.

My join matched zero rows and I am certain the key is right. What now?

Try the three key toggles in order. Set Numbers to 1.0 = 01 = 1 first, because the commonest cause by a distance is one file exporting ids as numbers and the other as text. Then set Letter case to Ignore. Surrounding spaces are already ignored by default. If all three fail, look at an actual value from each file side by side: one of them usually has a prefix, a suffix, or a different number of digits.

Why did I get more rows out than I put in?

Because the second file has more than one row for at least one key, and a join pairs every match with every match, exactly as SQL does. The warning above the result names how many extra rows that produced. If you expected one row per key, deduplicate the lookup table first, or use a Semi join if you only needed to know which keys were present.

Can I join on more than one column?

Yes, up to four pairs. Every pair has to agree before two rows count as a match, which is what you want for data keyed by region and month, or warehouse and SKU. The column names can differ between the two files. Leave the second box empty and it assumes the same name on both sides.

How do I find the rows that did not match?

Choose the Anti join type. It returns exactly the rows of the first file that found no partner, with only the first file's columns, which is the list you would hand to whoever maintains the other system. Full outer does the reverse job as well, keeping unmatched rows from both sides in one table with blanks where the gaps are.

Do my files get uploaded?

No. There is no upload endpoint on this page. JavaScript in your tab reads both files, matches the rows and builds the result locally. Nothing is stored between visits, so reloading gives you two empty boxes again. The same is true of the equivalent SQL panel, which is generated in the page and never sent anywhere.

Can I choose which columns come across from the second file?

Yes. Leave Columns to bring over empty and you get everything except the key columns, which would be duplicates. Type a comma-separated list to narrow it. The names available from the second file are listed in the report above the result, so you do not have to open the other file to remember what they were.

Join two files and see whether it worked

Free, no account, no upload. The match report tells you the truth about your keys before you download anything.

Back to the join tool