JOIN two files

This is the lesson people come to SQL for. Everything so far could have been done, painfully, in a spreadsheet. Combining three files on a shared key is where the spreadsheet approach becomes a column of VLOOKUPs that nobody can audit, and where one SQL query does the job in five lines and tells you what it dropped.

Three files are loaded on this page: orders with eighty rows, customers with twenty-four, and products with sixteen. Orders is the first one, so it also answers to csv.

The shared column is customer_id. Orders also carries a product_id that matches the products file. That is the whole setup, and it is the shape of every relational database you will ever meet: narrow tables that point at each other by id.

The anatomy of a join

Three parts. Which files, in what relationship, matched on what.

FROM orders o
JOIN customers c ON o.customer_id = c.customer_id

The single letters after each file name are aliases, and they let you write o.total and c.name. Once two files are in play, qualifying every column is not pedantry: both files here have a column called customer_id, and an unqualified reference to it is ambiguous. Even where it is not ambiguous today, it becomes ambiguous the moment somebody adds a column to one of the files.

The ON clause is a condition, not a keyword pair. It usually tests equality between two ids, but it can be any expression, and it can have several conditions joined by AND when the key is made of two columns.

Inner joins drop things quietly

A plain JOIN is an inner join: a row appears in the result only if it found a partner. Orders with no matching customer vanish. Customers with no orders vanish. Nothing warns you.

This file has both cases deliberately built in. Two orders point at customer 1099, who is not in the customer file, and four customers have never ordered anything. Join the two files and you get seventy-eight rows out of eighty. If you then total the revenue, you are quietly two orders short, and there is nothing on the screen to tell you.

So the habit to build is arithmetic. You know orders has eighty rows. Count the joined result. If it is smaller, an inner join dropped unmatched rows and you should find out why before you publish the number. If it is larger, something more alarming has happened: the key is not unique on one side, and rows have been multiplied. A join that turns eighty orders into a hundred and forty is not a join problem, it is a duplicate-key problem in the file, and every total computed from it will be inflated.

LEFT JOIN keeps everything on one side

LEFT JOIN keeps every row of the first file whether or not it matched, filling the other file's columns with nulls where it did not. That gives you both the report and the audit in one query.

SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL

Keep everything, then keep only the rows where the match failed. Two rows come back, and they are the two orphans. This pattern has a name, the anti-join, and it is the fastest way to answer "what is in file A but not in file B" for any two files at all.

Swap the file order and the same pattern finds customers who have never ordered. Note the subtle part: the null test must be on a column from the right-hand file, and preferably on its key, because a column that could legitimately be null in the data would give you false positives.

RIGHT JOIN is the mirror image and is rare in practice, because people prefer to reorder the files. FULL OUTER JOIN keeps unmatched rows from both sides at once, which is exactly what you want when reconciling two systems that are each supposed to hold the same records.

Filtering in ON versus in WHERE

On an inner join it makes no difference. On a LEFT JOIN it changes the answer completely. A condition in the ON clause decides whether a row counts as matched; a condition in the WHERE clause is applied afterwards, to the joined result, and any row whose right-hand side is all nulls will fail almost any condition you write about it. The classic symptom is a LEFT JOIN that behaves exactly like an inner one, and the cause is nearly always a right-table condition that belongs in the ON.

More than two files

Chain them. Each JOIN adds one file and one ON clause, and the last exercise here joins orders to products to total revenue by category. There is no limit worth worrying about, and DuckDB is perfectly happy joining several files that are each hundreds of megabytes, in your browser tab, without a server.

That is genuinely the same query you would write against a warehouse. When these files outgrow a lesson page, the full editor takes any number of loaded files and joins across all of them with the same syntax.

What to look up next

Look up "SQL join fan-out" or "join fanout duplicate rows". It is the formal name for the row-multiplication problem above, and it is the single most common cause of a report that is wrong by a factor nobody can explain.