What is a SQL join?

A SQL JOIN combines rows from two or more tables based on a matching column between them. Most non-trivial business questions — "which customers ordered last quarter", "which employees report to which managers", "which patients have unfilled prescriptions" — require pulling data from at least two tables, and JOIN is how SQL expresses that combination.

The standard SQL specification (ANSI SQL-92) defines six join behaviours: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, CROSS, and the implicit comma-join. Most modern databases support all of them; a handful (most notably MySQL up to version 8 and SQLite) do not implement FULL OUTER JOIN natively, which we cover below.

The six join types at a glance

A "self join" is not a separate operator — it is any join (INNER, LEFT, etc.) where a table is joined to itself, usually with a different alias. Anti-join and semi-join, similarly, are patterns expressed with NOT EXISTS / EXISTS, NOT IN, or LEFT JOIN ... WHERE B.key IS NULL — they are not SQL keywords.

INNER JOIN

INNER JOIN keeps only rows where the ON predicate matches in both tables. It is the default and most common join — when in doubt, this is the one to reach for.

SELECT e.first_name, e.last_name, j.job_title
FROM   employees e
INNER JOIN jobs j
       ON j.job_id = e.job_id
ORDER BY e.last_name;

Notes: the INNER keyword is optional in every major database — JOIN alone means INNER JOIN. Spelling it out is a readability convention, not a performance choice.

LEFT JOIN (LEFT OUTER JOIN)

LEFT JOIN keeps every row from the left table, attaching matching right-table rows where they exist and NULLs where they do not. It is the right tool whenever the left side is the canonical entity list and you want to enrich it with optional related data.

SELECT d.department_name, e.first_name, e.last_name
FROM   departments d
LEFT JOIN employees e
       ON e.department_id = d.department_id
ORDER BY d.department_name;

You can chain LEFT JOINs but be aware of the order: LEFT JOIN is not associative once a later join introduces a NULLable side. If you need the same left-preservation across all joins, keep them all LEFT and never mix INNER in the middle.

Counting "left rows with no match"

SELECT d.department_name
FROM   departments d
LEFT JOIN employees e
       ON e.department_id = d.department_id
WHERE  e.employee_id IS NULL;

RIGHT JOIN (RIGHT OUTER JOIN)

RIGHT JOIN is the mirror of LEFT JOIN: it preserves every row from the right table. It exists for completeness but is uncommon in modern code because rewriting any RIGHT JOIN as a LEFT JOIN with the tables swapped reads more naturally.

-- Discouraged
SELECT * FROM employees e RIGHT JOIN departments d ON d.department_id = e.department_id;

-- Equivalent and clearer
SELECT * FROM departments d LEFT  JOIN employees   e ON e.department_id = d.department_id;

FULL OUTER JOIN

FULL OUTER JOIN keeps every row from both tables — matched pairs together, unmatched left rows with NULL on the right, and unmatched right rows with NULL on the left. It is the natural choice when reconciling two sources of truth (e.g. CRM customers vs. billing customers).

SELECT COALESCE(c.customer_id, b.customer_id) AS customer_id,
       c.email                              AS crm_email,
       b.email                              AS billing_email
FROM   crm_customers     c
FULL OUTER JOIN billing_customers b
       ON b.customer_id = c.customer_id
WHERE  c.customer_id IS NULL OR b.customer_id IS NULL;

SELECT a.id AS a_id, b.id AS b_id FROM a LEFT  JOIN b ON b.id = a.id
UNION
SELECT a.id AS a_id, b.id AS b_id FROM a RIGHT JOIN b ON b.id = a.id;

CROSS JOIN

CROSS JOIN produces the unfiltered cartesian product — every row of A paired with every row of B. It has no ON clause. Useful for generating dense calendar grids, parameter sweeps for tests, or all-pairs comparisons. Use with care: a CROSS JOIN of two 1,000-row tables yields 1,000,000 rows.

SELECT r.region_name, m.month_name
FROM   regions r
CROSS JOIN months m
ORDER BY r.region_name, m.month_num;

Self join

A self join is a join where a table appears on both sides under different aliases. The two canonical use cases are hierarchies (employee → manager) and within-table comparisons (this row vs. the previous row, when window functions are not available or are clumsy).

SELECT e.first_name || ' ' || e.last_name  AS employee,
       m.first_name || ' ' || m.last_name  AS manager
FROM   employees e
LEFT JOIN employees m
       ON m.employee_id = e.manager_id
ORDER BY manager NULLS FIRST, employee;

Use LEFT JOIN (not INNER) for the hierarchy pattern — otherwise the top of the tree (the row whose manager_id is NULL) is dropped. Recursive CTEs are the right tool when you need the full ancestor chain in a single query.

Anti joins and semi joins

Anti and semi joins are not SQL keywords — they are patterns expressed with EXISTS, NOT EXISTS, NOT IN, or LEFT JOIN ... IS NULL. A semi join keeps rows from A that have at least one match in B (without duplicating A). An anti join keeps rows from A that have no match in B.

-- Preferred (NOT EXISTS — handles NULLs correctly).
SELECT c.customer_id, c.email
FROM   customers c
WHERE  NOT EXISTS (
         SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
       );

-- Equivalent in most engines (LEFT JOIN ... IS NULL).
SELECT c.customer_id, c.email
FROM   customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE  o.order_id IS NULL;

Choosing the right join

Performance considerations

The choice of INNER vs. OUTER rarely changes runtime by itself — modern planners are good at both. Three other factors matter much more:

1. Indexes on the join columns. A join on an unindexed column on a million-row table forces a hash or merge with full-table scan; the same join with a B-tree index typically runs in milliseconds.
2. Selectivity. Apply the most-restrictive WHERE filter first (the planner often does this automatically, but only if statistics are fresh — run ANALYZE / UPDATE STATISTICS regularly).
3. Data types. Joining a VARCHAR to an INTEGER triggers an implicit cast that disables index use. Match the types and (in MySQL) collations on both sides.

Dialect differences

Practical advice: prefer ON over USING and avoid NATURAL JOIN. Both USING and NATURAL JOIN match on column name, which means a future schema change (renaming a column, adding a column with the same name) silently changes query semantics. ON is explicit and survives refactors.

Common mistakes

1. Filtering the OUTER side in WHERE — turns LEFT JOIN into INNER JOIN. Move the predicate into ON.
2. Joining on an implicit type cast — disables the index. ALWAYS match types.
3. Forgetting DISTINCT or aggregation when the right side is one-to-many — a single A row gets duplicated by every B match.
4. NOT IN with NULLable sub-query — silently returns zero rows. Use NOT EXISTS.
5. Implicit comma cross-joins from missing the join keyword. Always write JOIN explicitly.
6. Self-joining without LEFT for hierarchy traversal — drops the root.
7. Using SELECT * across multiple tables — column ambiguity, fragile downstream code, and unpredictable plan changes when columns are added.