Quartz 5

SQL Joins

Properties1
tagsdatabase/sql, database/queries

3 min read

SQL Joins

Joins combine rows from two or more tables based on a related column, implementing the Relational Algebra join operation (⋈) in SQL. They are the primary mechanism for querying across related tables in a relational database, leveraging foreign key relationships defined in the schema.

Join Types

INNER JOIN

Returns only rows where the join condition matches in both tables. The most common join type.

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

Equivalent Relational Algebra: or

LEFT (OUTER) JOIN

Returns all rows from the left table and matched rows from the right. Unmatched right-side columns are NULL.

SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

Use case: Find employees even if they have no department assigned.

RIGHT (OUTER) JOIN

Mirror of LEFT JOIN — all rows from the right table, matched rows from the left.

SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

Note: Not supported in SQLite. Can always be rewritten as a LEFT JOIN by swapping table order.

FULL OUTER JOIN

Returns all rows from both tables. Unmatched rows on either side get NULL for the other table’s columns.

SELECT e.name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

Not supported in SQLite. Can be emulated with LEFT JOIN UNION RIGHT JOIN.

CROSS JOIN

Cartesian product — every row of the first table paired with every row of the second. No ON clause. Produces |A| × |B| rows.

SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;

Use case: Generating all possible combinations (e.g., product variants).

SELF JOIN

A table joined to itself. Requires table aliases to distinguish the two references.

SELECT e.name AS employee, m.name AS manager
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id;

Use case: Hierarchical data (employees/managers), finding duplicates.

NATURAL JOIN

Joins on all columns with the same name in both tables. Implicit and potentially dangerous — schema changes can silently alter behavior.

SELECT * FROM employees NATURAL JOIN departments;

Generally avoided in production code in favor of explicit ON clauses.

Join Conditions

  • ON — Explicit join condition: ON a.id = b.a_id
  • USING — Shorthand when join columns share the same name: USING (department_id)
  • Natural — Implicit on all shared column names (fragile)

Multiple Joins

Joins can be chained. Execution order matters for performance but not correctness (the optimizer reorders):

SELECT e.name, d.name, p.title
FROM employees e
JOIN departments d ON e.dept_id = d.id
JOIN projects p ON e.id = p.lead_id;

Anti-Join Pattern

Find rows with no match using LEFT JOIN + WHERE NULL:

SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL;

Equivalent to NOT EXISTS or NOT IN subqueries, but often more performant.

Performance Considerations

  • Ensure join columns are indexed — see Index
  • Prefer explicit JOIN syntax over implicit comma-separated joins in FROM
  • Use Explain Analyze to inspect the join strategy the optimizer chose (nested loop, hash join, merge join)

Sources

Graph View

Backlinks