A JOIN is the SQL operation that combines rows from two or more tables into a single result set by matching them on a condition, most often equality between a foreign key and the primary key it references. It is how relational databases reassemble information that normalization deliberately spread across separate tables.
JOIN appears in the FROM clause of a SELECT statement, and its type decides what happens to rows without a match: an inner join drops them, while outer joins keep them and fill the missing side with NULL. Choosing the right type and condition is one of the most consequential decisions in everyday SQL.
How a JOIN combines rows
Logically, a JOIN starts from the Cartesian product of two tables, every row of the first paired with every row of the second, and keeps only the pairs that satisfy the join condition in the ON clause. A query such as FROM orders o JOIN customers c ON o.customer_id = c.id therefore pairs each order with exactly its customer.
This logical model describes the result, not the work the database performs. No serious DBMS builds the full product; the query optimizer chooses a physical algorithm, decides the join order for multi-table queries and uses indexes where possible. Because the order of inner joins does not change the result, the optimizer is free to reorder them for speed.
The result of a JOIN is itself a table, so further joins, filters in WHERE, aggregation with GROUP BY and filtering of groups with HAVING all operate on the combined rows.
Types of JOIN in SQL
The SQL standard defines several JOIN types that differ in which unmatched rows survive. The table below summarizes them using customers and orders as the two tables.
| JOIN type | Rows returned | Typical question answered |
|---|---|---|
| INNER JOIN | Only pairs that match the condition | Which orders belong to which customers |
| LEFT OUTER JOIN | All left rows, NULLs where the right side has no match | All customers, including those without orders |
| RIGHT OUTER JOIN | All right rows, NULLs where the left side has no match | Same as LEFT JOIN with tables swapped |
| FULL OUTER JOIN | All rows from both sides, matched where possible | Reconciling two lists of records |
| CROSS JOIN | Every combination of rows | Generating all size and color variants |
| Self join | A table joined to itself under two aliases | Employees with their managers |
Two filtering forms complete the picture. A semi join, written with EXISTS or IN, returns left rows that have at least one match without duplicating them. An anti join, written with NOT EXISTS, returns left rows that have no match at all, such as customers who never ordered. Some databases also support LATERAL joins, which let the right side refer to columns of the current left row.
Physical join algorithms
Execution speed depends on the physical algorithm the optimizer chooses, which an execution plan reveals through commands such as EXPLAIN. Understanding the three classic algorithms explains most join performance behavior.
| Algorithm | How it works | Best suited for | Cost profile |
|---|---|---|---|
| Nested loop join | For each outer row, look up matching inner rows | Small outer input with an index on the inner table | Fast for few rows, very slow for large unindexed inputs |
| Hash join | Build a hash table on the smaller input, probe with the larger | Large unsorted inputs with equality conditions | About linear, needs memory for the hash table |
| Merge join | Walk two sorted inputs in step | Inputs already sorted or indexed on the join key | About linear after sorting |
The most common performance fix is an index on the foreign key column. Without it, joining from a parent row to its children forces a scan of the child table, and the same missing index also slows down deletes on the parent table.
JOIN compared with other ways to combine data
JOIN is not the only SQL construct that combines data. Choosing between a join, a subquery, a CTE or a set operation is often a question of readability and intent, since modern optimizers frequently produce similar plans for equivalent forms.
| Construct | Combines data | Prefer it when |
|---|---|---|
| JOIN | Horizontally, adding columns from related rows | Columns from several tables are needed |
| Subquery with EXISTS | As a filter without adding columns | Only the existence of a match matters |
| CTE | Names an intermediate result for later joins | A complex query needs readable steps |
| UNION | Vertically, stacking rows of compatible shape | Merging similar rows from different sources |
| Window function | Adds values computed over related rows | Running totals or rankings without collapsing rows |
Common JOIN mistakes
JOIN errors are dangerous because they usually return plausible but wrong results instead of failing. The following mistakes cause most incorrect reports and slow queries.
- Row multiplication: joining a customer to both orders and support tickets multiplies rows, so sums and counts become inflated; aggregate each child table first.
- Accidental inner join: a
WHEREcondition on a column of the right table, such asWHERE o.status = 'paid', removes the NULL rows of a LEFT JOIN; move it into theONclause. - Missing join condition: forgetting the condition produces a Cartesian product, which can return millions of rows from two modest tables.
- NULL keys never match: NULL is not equal to NULL, so rows with missing keys silently disappear from inner joins.
- Mismatched data types: joining a text column to a numeric column forces conversions that can prevent index use.
A reliable habit is to check row counts before and after each join while developing a query. An unexpected increase points to duplicates, and an unexpected decrease points to an unintended inner join.
Put simply
Imagine two lists: one with customer names and their customer numbers, another with orders that mention only the customer number. A JOIN lays the lists side by side and matches each order to the right customer by that shared number.
Different kinds of JOIN answer different questions. One keeps only the matches; another keeps every customer and simply leaves the order part blank for those who never bought anything.
In practice
An online bookstore wants a monthly report of every registered customer and their total spending, including customers who bought nothing. An analyst writes a LEFT JOIN from the customers table to an aggregated orders subquery and replaces missing totals with zero using COALESCE.
The first draft joined customers directly to both orders and returns, which doubled some totals because each order row repeated for every return. After aggregating orders and returns separately before joining, the totals match the accounting figures exactly. With an index on orders.customer_id, the report covers 150,000 customers in about a second, and the marketing team uses the zero-spend group for a reactivation campaign.