SQL Joins: Every Join Type with Runnable Examples and Exact Results

A join evaluates pairs of rows against an ON condition. The join type decides which unmatched rows are preserved, while key uniqueness decides how many matches each row can produce.

Predict two things before running a join: which input population must survive, and what one output row represents. That catches both silent row loss and silent row multiplication.

Row preservation chooses INNER, LEFT, or RIGHT

INNER JOIN keeps only matching employee-department pairs. Femi has no department key, and Legal has no employee, so neither appears.

Input: employees

Input: departments

SELECT
  e.emp_name,
  e.dept_id,
  d.dept_name
FROM employees AS e
JOIN departments AS d
  ON d.dept_id = e.dept_id
ORDER BY e.emp_id;

Output

LEFT JOIN preserves every left row. This query also demonstrates filter placement: the salary condition belongs in ON because employees are optional matches and every department must remain.

Input: departments

Input: employees

SELECT
  d.dept_name,
  e.emp_name
FROM departments AS d
LEFT JOIN employees AS e
  ON e.dept_id = d.dept_id
 AND e.salary > 100000
ORDER BY d.dept_id, e.emp_id;

Output

The SQL order-of-operations guide explains why ON controls matching before WHERE filters the joined rows.

RIGHT JOIN is the mirror: it preserves the right table. It can always be rewritten as a left join with the table order swapped, but understanding the written form helps when reviewing existing SQL.

Input: employees

Input: departments

SELECT
  e.emp_name,
  d.dept_id,
  d.dept_name
FROM employees AS e
RIGHT JOIN departments AS d
  ON e.dept_id = d.dept_id
ORDER BY d.dept_id, e.emp_id;

Output

FULL OUTER and CROSS JOIN solve different shapes

FULL OUTER JOIN preserves unmatched rows from both inputs. It is useful for reconciliation because it exposes missing records on either side.

Input: employees

Input: departments

SELECT
  e.emp_name,
  d.dept_name
FROM employees AS e
FULL OUTER JOIN departments AS d
  ON e.dept_id = d.dept_id
ORDER BY d.dept_id NULLS LAST, e.emp_id;

Output

CROSS JOIN does not search for matching keys. It returns every combination. A small dimension such as quarters can create a reporting scaffold that later receives actual measures.

Input: departments

Input: quarters

SELECT
  d.dept_name,
  q.quarter
FROM departments AS d
CROSS JOIN quarters AS q
ORDER BY d.dept_id, q.quarter;

Output

Join keys must match table grain

Budgets and spend are each one row per department and fiscal year. Joining on department alone would mix years. The full composite key preserves the intended department-year grain, and LEFT JOIN retains a budget whose spend is not recorded.

Input: budgets

Input: spend

SELECT
  b.dept_id,
  b.fiscal_year,
  b.budget_amount,
  s.spend_amount
FROM budgets AS b
LEFT JOIN spend AS s
  ON s.dept_id = b.dept_id
 AND s.fiscal_year = b.fiscal_year
ORDER BY b.dept_id, b.fiscal_year;

Output

Self, semi, and anti joins

A self join gives one table two roles. Employees are the left role; managers are another reference to the same table. LEFT JOIN keeps Ana, who has no manager.

Input: employees

SELECT
  e.emp_name AS employee,
  m.emp_name AS manager
FROM employees AS e
LEFT JOIN employees AS m
  ON m.emp_id = e.manager_id
ORDER BY e.emp_id;

Output

A semi-join keeps a left row when any match exists; EXISTS expresses it without emitting or duplicating right rows. An anti-join keeps a left row when no match exists; NOT EXISTS expresses that inverse safely even when the right-side join column contains NULL.

Input: departments

Input: employees

SELECT d.dept_name
FROM departments AS d
WHERE NOT EXISTS (
  SELECT 1
  FROM employees AS e
  WHERE e.dept_id = d.dept_id
)
ORDER BY d.dept_id;

Output

The NULL comparison guide explains why NOT IN needs special care when its list or subquery can contain NULL.

Control fanout before aggregating

Ana owns two projects. Joining raw projects would repeat her salary twice. First collapse projects to one row per employee, then join at employee grain and aggregate to department grain.

Input: departments

Input: employees

Input: projects

WITH project_hours AS (
  SELECT emp_id, SUM(hours) AS total_hours
  FROM projects
  GROUP BY emp_id
)
SELECT
  d.dept_name,
  COUNT(e.emp_id) AS employees_with_projects,
  SUM(e.salary) AS total_salary,
  SUM(p.total_hours) AS total_hours
FROM departments AS d
JOIN employees AS e
  ON e.dept_id = d.dept_id
JOIN project_hours AS p
  ON p.emp_id = e.emp_id
GROUP BY d.dept_id, d.dept_name
ORDER BY d.dept_id;

Output

This output intentionally includes only employees who have projects. If every employee or department must appear, change the population-preserving joins and zero policy accordingly. The GROUP BY guide and SQL COUNT guide cover the aggregation choices after the join.

FAQ

Can an inner join return more rows than either input?

Yes. One left row emits one result for every matching right row. Duplicate or one-to-many keys can therefore multiply rows.

Is RIGHT JOIN different from LEFT JOIN?

Its preservation direction is reversed, but any right join can be expressed as a left join by swapping table order. Choose the form that makes the preserved population easiest to read.

Where should a right-table filter go in a LEFT JOIN?

Put it in ON when it limits eligible matches but unmatched left rows must remain. Put it in WHERE when the requirement truly removes joined rows after matching.

When should I use EXISTS instead of JOIN?

Use EXISTS when the question only asks whether a match exists and no right-side columns are needed. It preserves one output row per qualifying left row.

How do I detect join fanout?

State the expected key and row count before joining. Then compare row counts and key uniqueness after each join. If a supposed one-to-one join multiplies keys, inspect duplicates on the join columns before aggregating.