SQL joins: linking tables

In short: A JOIN combines rows from two tables by a condition, usually equal key values. This lets you query separately stored data together.

In more detail: Example: table customer(id, name) and orders(id, customer_id, amount). With ON orders.customer_id = customer.id you link orders to customers’ names.

In Depth

• INNER JOIN: Only rows that have a partner on both sides. Customers without orders are missing.
• LEFT JOIN: All rows of the left table, even without a partner. Missing values on the right are NULL. With WHERE orders.id IS NULL you find customers without orders.
• RIGHT JOIN: The mirror image. Usually written as a LEFT JOIN with swapped tables.
• FULL OUTER JOIN: All rows of both sides, with NULL for missing partners.
• CROSS JOIN: Every row with every row (Cartesian product). Careful: n × m rows.
• SELF JOIN: Joining a table with itself, for example employees and their managers.
• Pitfalls: Without a condition a Cartesian product arises. With 1:n relationships rows multiply and sums go wrong. In a LEFT JOIN a filter on the right table belongs in ON, not in WHERE.
• Speed: Index foreign-key columns (SQL index).

See also: SQL, optimising queries with EXPLAIN