SQL joins simulator

The two tables hold customers and their orders. Each order has a customer_id that should point at a customer's id, but not every one does: order 103 points at customer 5, who does not exist, and order 104 has no customer at all (NULL). Customers 3 and 4 have no orders. That mix is what makes the join types give different answers.

Pick a join type and the result appears on the right, with NULL wherever a join filled in a missing side. The grid shows the join condition evaluated for every pair of a customer and an order. Press Step through to watch the evaluation one comparison at a time, in the order a nested loop join would do it. Things to try:

• Switch between INNER and LEFT. The matching rows are the same; LEFT adds Linus and Margaret with NULLs.
• Choose FULL OUTER and count: 3 matches, 2 customers without orders, 2 orders without customers.
• Change order 103's customer_id to 3. Linus gains an order, and one fewer row has NULLs.
• Set a customer's id to NULL. It can no longer match anything, even an order whose customer_id is also NULL.
• Step through Semi (EXISTS): once a customer has a match, the remaining orders are skipped.

Every result is computed by evaluating the condition with SQL's three-valued logic. The same data gives the same rows in SQLite 3.45, PostgreSQL and other databases that follow the standard.

A join combines rows from two tables into one result. Conceptually, the database considers every pair made of one row from each table, evaluates the join condition for that pair, and keeps the pairs where the condition is true. In the query on this page the condition is o.customer_id = c.id, so each order is paired with the customer it belongs to.

That is an inner join. The other kinds are called outer joins, and differ only in what they do with rows that matched nothing: a left join keeps the unmatched rows of the first table, a right join those of the second, and a full outer join both, filling the other table's columns with NULL. A cross join has no condition and keeps every pair.

Relational databases split data across tables so that each fact is stored once: a customer's name lives in one row of customers, not in every order. Joins are how queries put the pieces back together.

These results come from the simulator's default tables: four customers and five orders. Rows are shown in the order a nested loop produces them; a database may return them in any order unless the query has an ORDER BY.

The difference shows up only when a row of the left table has no match. An inner join drops it; a left join keeps it, with NULLs. Two practical rules follow:

• Use a left join when every row of the first table must appear, such as "every customer, with their orders if they have any", and an inner join when only rows with a match make sense, such as "orders with the name of the customer who placed them".
• A left join returns at least as many rows as the left table has. An inner join can return fewer, the same, or more, because of the duplicates described next.

The keywords are interchangeable with longer forms: JOIN means INNER JOIN, and LEFT JOIN means LEFT OUTER JOIN. The word OUTER changes nothing.

A join produces one row per matching pair, not one row per row of the left table. Ada has two orders, so she appears twice in every join above. This is how a one-to-many relationship should look, but it catches people out in two ways.

The first is aggregates after a join. If customers had a credit column, SUM(c.credit) over the joined rows would count Ada's credit twice. Aggregate the many side first, in a subquery or a common table expression, and join the totals.

The second is joining two one-to-many tables at once. Joining customers to their 3 orders and their 4 support tickets gives 3 × 4 = 12 rows per customer, every order paired with every ticket. Counts and sums over that result are wrong in a way that is easy to miss on small test data.

SQL uses three-valued logic: a condition is true, false or unknown. Any comparison involving NULL, including NULL = NULL, is unknown, and a join keeps a pair only when the condition is true. So a row whose join column is NULL never matches anything. Order 104 in the examples has no customer and appears only in the right and full outer joins, as an unmatched row.

Two other kinds of NULL show up in results, and it helps to tell them apart: a NULL that was stored in the table, like order 104's customer_id, and a NULL that the outer join filled in because there was no matching row. To join on columns where NULL should match NULL, use a IS NOT DISTINCT FROM b (PostgreSQL, SQL Server 2022, SQLite's IS, MySQL's <=>), knowing that it usually stops the database from using a plain index lookup.

For an inner join, a condition gives the same result in ON or in WHERE. For an outer join it does not. ON decides which rows match, and unmatched left rows are kept anyway. WHERE runs afterwards, on the joined rows, and the NULLs that the join filled in fail almost any condition. These two queries differ only in where one condition is written; the results were checked in SQLite 3.45:

Often the question is not "which orders" but "which customers have any orders", or "which have none". These are a semi-join and an anti-join. They return rows of one table only, each at most once, so they never duplicate rows. SQL has no keyword for them; they are written with EXISTS and NOT EXISTS, and databases run them as joins.

• A self join joins a table to itself under two aliases, for example employees to their managers: FROM employees e JOIN employees m ON m.id = e.manager_id.
• JOIN … USING (customer_id) is shorthand for an equality on columns with the same name in both tables, and returns that column once.
• A natural join joins on every column the two tables have in common by name. Adding a column such as created_at to both tables later silently changes its meaning, so most style guides avoid it.
• A non-equi join uses a condition other than equality, such as matching each sale to the price band it falls in with BETWEEN.
• LATERAL (PostgreSQL, MySQL 8.0.14 and later) or CROSS APPLY (SQL Server) lets the right side refer to the current left row, for queries like "the three latest orders of each customer".

Joins are often drawn as two overlapping circles, with the overlap shaded for an inner join. The picture suggests that a join works on sets of identical items, like UNION and INTERSECT do. It does not: a join builds new rows out of pairs, one table's rows need not resemble the other's, and one left row can produce many result rows. The circles cannot show the duplicates of a one-to-many join, the effect of a NULL key, or a cross join at all. Lukas Eder's 2016 post "Say NO to Venn Diagrams When Explaining JOINs" makes the case in detail. The grid in the simulator, with every pair and its outcome, is closer to what the database does.

Checking every pair, as the simulator does, takes time proportional to the product of the two table sizes, which is fine for small tables and hopeless for large ones. Databases choose among three main algorithms, and the query planner picks one using its estimates of table sizes.

Join questions in interviews usually test one of these points:

• Row counts. "Table A has 4 rows, table B has 5. How many rows can an inner join return?" Anywhere from 0 to 20, depending on the data; a left join returns at least 4; a cross join exactly 20.
• Finding missing rows. Customers with no orders: NOT EXISTS, or LEFT JOIN with WHERE o.id IS NULL. Mention the NOT IN and NULL trap.
• ON vs WHERE in an outer join, as above.
• Duplicates and aggregates. Why a total is too high after a join, and how to aggregate before joining.
• Self joins, such as employees earning more than their managers.
• Performance. Which index helps a join, and what nested loop, hash and merge joins are.