Articles

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

Learn SQL joins through verified INNER, LEFT, RIGHT, FULL, CROSS, composite-key, self, anti, and fanout-safe PostgreSQL examples.

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

emp_id	emp_name	dept_id	manager_id	salary
1	Ana	10	NULL	190000
2	Ben	10	1	140000
3	Cara	20	1	120000
4	Dev	20	3	95000
5	Elena	30	3	110000
6	Femi	NULL	1	105000

Input: departments

dept_id	dept_name	floor
10	Engineering	3
20	Sales	2
30	Marketing	2
40	Legal	5

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

emp_name	dept_id	dept_name
Ana	10	Engineering
Ben	10	Engineering
Cara	20	Sales
Dev	20	Sales
Elena	30	Marketing

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

dept_id	dept_name	floor
10	Engineering	3
20	Sales	2
30	Marketing	2
40	Legal	5

Input: employees

emp_id	emp_name	dept_id	manager_id	salary
1	Ana	10	NULL	190000
2	Ben	10	1	140000
3	Cara	20	1	120000
4	Dev	20	3	95000
5	Elena	30	3	110000
6	Femi	NULL	1	105000

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

dept_name	emp_name
Engineering	Ana
Engineering	Ben
Sales	Cara
Marketing	Elena
Legal	NULL

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

emp_id	emp_name	dept_id	manager_id	salary
1	Ana	10	NULL	190000
2	Ben	10	1	140000
3	Cara	20	1	120000
4	Dev	20	3	95000
5	Elena	30	3	110000
6	Femi	NULL	1	105000

Input: departments

dept_id	dept_name	floor
10	Engineering	3
20	Sales	2
30	Marketing	2
40	Legal	5

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

emp_name	dept_id	dept_name
Ana	10	Engineering
Ben	10	Engineering
Cara	20	Sales
Dev	20	Sales
Elena	30	Marketing
NULL	40	Legal

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

emp_id	emp_name	dept_id	manager_id	salary
1	Ana	10	NULL	190000
2	Ben	10	1	140000
3	Cara	20	1	120000
4	Dev	20	3	95000
5	Elena	30	3	110000
6	Femi	NULL	1	105000

Input: departments

dept_id	dept_name	floor
10	Engineering	3
20	Sales	2
30	Marketing	2
40	Legal	5

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

emp_name	dept_name
Ana	Engineering
Ben	Engineering
Cara	Sales
Dev	Sales
Elena	Marketing
NULL	Legal
Femi	NULL

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

dept_id	dept_name	floor
10	Engineering	3
20	Sales	2
30	Marketing	2
40	Legal	5

Input: quarters

quarter
Q1
Q2

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

Output

dept_name	quarter
Engineering	Q1
Engineering	Q2
Sales	Q1
Sales	Q2
Marketing	Q1
Marketing	Q2
Legal	Q1
Legal	Q2

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

dept_id	fiscal_year	budget_amount
10	2025	500000
10	2026	550000
20	2025	300000
20	2026	280000
30	2026	120000

Input: spend

dept_id	fiscal_year	spend_amount
10	2025	480000
10	2026	505000
20	2025	310000
30	2026	118000

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

dept_id	fiscal_year	budget_amount	spend_amount
10	2025	500000	480000
10	2026	550000	505000
20	2025	300000	310000
20	2026	280000	NULL
30	2026	120000	118000

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

emp_id	emp_name	dept_id	manager_id	salary
1	Ana	10	NULL	190000
2	Ben	10	1	140000
3	Cara	20	1	120000
4	Dev	20	3	95000
5	Elena	30	3	110000
6	Femi	NULL	1	105000

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

employee	manager
Ana	NULL
Ben	Ana
Cara	Ana
Dev	Cara
Elena	Cara
Femi	Ana

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

dept_id	dept_name	floor
10	Engineering	3
20	Sales	2
30	Marketing	2
40	Legal	5

Input: employees

emp_id	emp_name	dept_id	manager_id	salary
1	Ana	10	NULL	190000
2	Ben	10	1	140000
3	Cara	20	1	120000
4	Dev	20	3	95000
5	Elena	30	3	110000
6	Femi	NULL	1	105000

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

dept_name
Legal

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

dept_id	dept_name	floor
10	Engineering	3
20	Sales	2
30	Marketing	2
40	Legal	5

Input: employees

emp_id	emp_name	dept_id	manager_id	salary
1	Ana	10	NULL	190000
2	Ben	10	1	140000
3	Cara	20	1	120000
4	Dev	20	3	95000
5	Elena	30	3	110000
6	Femi	NULL	1	105000

Input: projects

project_id	emp_id	hours
100	1	10
101	1	20
102	2	15
103	5	5

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

dept_name	employees_with_projects	total_salary	total_hours
Engineering	2	330000	45
Marketing	1	110000	5

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.