SQL JOIN Types Explained with Examples — INNER, LEFT, RIGHT, FULL, CROSS & SELF

1. The Sample Dataset

Every example below uses two tables. The dataset is small on purpose — five employees, three departments — and it contains three deliberate imperfections, because real data has them too:

• Dave has dept_id = NULL (a new hire not yet assigned).
• Erin has dept_id = 9, a department that no longer exists (a dangling reference).
• Finance has no employees at all.

Those three rows are what separate the join types from each other. Create the tables and load the data:

CREATE TABLE departments (
    dept_id   INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    emp_id    INT PRIMARY KEY,
    emp_name  VARCHAR(50) NOT NULL,
    dept_id   INT,                -- nullable FK to departments
    manager_id INT                -- nullable self-reference
);

INSERT INTO departments (dept_id, dept_name) VALUES
    (1, 'Engineering'),
    (2, 'Marketing'),
    (3, 'Finance');

INSERT INTO employees (emp_id, emp_name, dept_id, manager_id) VALUES
    (101, 'Alice', 1,    NULL),
    (102, 'Bob',   1,    101),
    (103, 'Carol', 2,    101),
    (104, 'Dave',  NULL, 103),
    (105, 'Erin',  9,    NULL);

departments

employees

One comparison table before we start. Each join type answers a different question about unmatched rows:

2. INNER JOIN — Only Matching Rows

INNER JOIN (the INNER keyword is optional — a bare JOIN means the same thing) returns only the rows where the ON condition is true on both sides. Unmatched rows are dropped silently: Dave, Erin, and the Finance department all disappear.

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

Result

When to use it

Use INNER JOIN when a match is required for the row to make sense: orders joined to customers, invoice lines joined to products, sessions joined to users. If a row without a match is meaningless for your report, inner joining is both correct and fastest — the optimizer has the most freedom with inner joins.

Pitfalls

• Silent data loss. Inner join hides dirty data instead of surfacing it. If Erin's dangling dept_id = 9 is a bug in your ETL, an inner join makes it invisible. When reconciling row counts, run SELECT COUNT(*) FROM employees (5) against the joined result (3) and ask why they differ.
• NULL never matches. NULL = NULL is not true in SQL — it is UNKNOWN. An inner join on a nullable key drops every NULL side, which is exactly what happened to Dave. There is no warning; the rows are simply gone.

3. LEFT JOIN — Keep Everything on the Left

LEFT JOIN (short for LEFT OUTER JOIN) keeps every row from the left table. Where a right-table match exists, its columns are filled in; where none exists, they are NULL. This is the join type you want for reporting: "list all employees, with their department if they have one."

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

Result

All five employees survive. Dave and Erin have no matching department, so dept_name is NULL for them — the join tells you the match is missing instead of hiding the row.

LEFT JOIN vs INNER JOIN

The whole left join vs inner join debate comes down to one question: what should happen to left rows that have no match? INNER JOIN drops them; LEFT JOIN keeps them with NULLs. Same query, different intent:

The classic pitfall: WHERE turns LEFT JOIN into INNER JOIN

This is the most common join bug in existence. Filter on a right-table column in the WHERE clause and every unmatched left row — which has NULL in that column — fails the filter and disappears:

-- BUG: this behaves exactly like an INNER JOIN
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name IS NOT NULL;   -- kills Dave and Erin
-- Returns 3 rows: Alice, Bob, Carol

The LEFT JOIN preserved Dave and Erin, then the WHERE clause deleted them. If that filter is the intent, write an INNER JOIN and say so — readers (and reviewers) won't have to puzzle over why a LEFT JOIN drops rows. If you genuinely need to keep unmatched rows, put the condition in ON or allow NULLs explicitly:

-- Keep unmatched rows: filter inside ON
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d
       ON e.dept_id = d.dept_id
      AND d.dept_name <> 'Marketing';
-- Alice, Bob keep Engineering; Carol gets NULL dept_name;
-- Dave and Erin stay with NULL.

-- Or allow NULL through the WHERE clause
SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_name = 'Engineering' OR d.dept_name IS NULL;

The rule of thumb: conditions on the right table belong in ON; conditions on the left table belong in WHERE when you want LEFT JOIN semantics preserved.

Pitfall: COUNT(*) after a LEFT JOIN

Aggregate the other direction — count employees per department, including departments with nobody — and COUNT(*) lies to you:

SELECT d.dept_name, COUNT(*) AS headcount
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name;

Finance matches zero employees, but the LEFT JOIN still produces one row for it (all employee columns NULL), and COUNT(*) counts rows, so Finance reports a headcount of 1. Count the joined column instead — COUNT ignores NULLs:

SELECT d.dept_name, COUNT(e.emp_id) AS headcount
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
GROUP BY d.dept_name
ORDER BY d.dept_name;
-- Engineering: 2, Finance: 0, Marketing: 1  <- correct

4. RIGHT JOIN and FULL OUTER JOIN

RIGHT JOIN — the mirror image

RIGHT JOIN keeps every row from the right table and fills NULLs for left rows that don't match. Semantically it is a LEFT JOIN with the table order swapped:

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

Result

Every department appears — Finance with a NULL employee — but Dave and Erin are gone, because they are unmatched rows on the left side. My opinionated advice: avoid RIGHT JOIN in real codebases. Any A RIGHT JOIN B can be written as B LEFT JOIN A, and standardizing on LEFT JOIN means every query reads "the driving table comes first." Teams that mix both directions end up with queries nobody can reason about at 2 a.m.

FULL OUTER JOIN — both sides preserved

FULL OUTER JOIN keeps every row from both tables, matching where possible and padding with NULLs elsewhere. It is the natural join for reconciliation: "show me every employee and every department, and let me see which ones failed to link up."

SELECT e.emp_name, d.dept_name
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.dept_id;

Result

Six rows: the three matches, the two orphan employees, and the one unused department. A data-quality query like WHERE e.emp_id IS NULL OR d.dept_id IS NULL on top of this finds both dangling references and empty departments in one pass.

Pitfall: MySQL has no FULL OUTER JOIN

PostgreSQL, SQL Server, Oracle, and SQLite (3.39+) support FULL OUTER JOIN. MySQL and MariaDB do not. The standard workaround is LEFT JOIN plus the missing right-only rows via UNION:

SELECT e.emp_name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
UNION
SELECT e.emp_name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

Use UNION, not UNION ALL: the matched rows appear in both halves and must be de-duplicated.

5. CROSS JOIN and SELF JOIN

CROSS JOIN — every combination

CROSS JOIN pairs every row of the left table with every row of the right table. No ON clause, no condition: 5 employees × 3 departments = 15 rows. Each employee appears once per department.

SELECT e.emp_name, d.dept_name
FROM employees e
CROSS JOIN departments d
ORDER BY e.emp_id, d.dept_id;
-- 15 rows: Alice×Engineering, Alice×Marketing, Alice×Finance,
--          Bob×Engineering, ... Erin×Finance

That sounds useless until you need exactly it. Legitimate uses:

• Generating a grid or spine: cross join a date series with a region list to build every (date, region) cell for a report, then LEFT JOIN actuals onto it so empty cells still appear.
• Parameterizing every row: CROSS JOIN (SELECT 1.13 AS rate) r attaches a config value to all rows without a subquery in the SELECT list.
• Combinatorial test data: sizes × colors × materials for a product matrix.

Pitfall: the accidental cross join

The dangerous case is a cross join you didn't ask for. Forget the ON condition, or use old comma syntax without a WHERE link, and you get a Cartesian product:

-- 15 rows instead of 3 — no join condition at all
SELECT e.emp_name, d.dept_name
FROM employees e, departments d;

-- Same disaster, modern syntax
SELECT e.emp_name, d.dept_name
FROM employees e
JOIN departments d;   -- no ON: behaves as CROSS JOIN

With 10,000 orders and 10,000 customers that is 100 million rows. Modern engines still run it — slowly, then fatally for your temp space. Always check that every joined table contributes an ON condition.

SELF JOIN — one table, two roles

A SELF JOIN is a regular join where the table appears on both sides under different aliases. The canonical case is a hierarchy: employees.manager_id points back at employees.emp_id, so joining the table to itself resolves each person's manager name.

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

Result

Note it's a LEFT self join — Alice and Erin have no manager, and an inner self join would drop them. The same pattern handles comparing rows to peers, e.g. finding coworkers in the same department:

SELECT a.emp_name AS emp_a, b.emp_name AS emp_b
FROM employees a
JOIN employees b
  ON a.dept_id = b.dept_id
 AND a.emp_id < b.emp_id;

Result

Only one pair: Alice and Bob share Engineering. The a.emp_id < b.emp_id trick does two jobs at once — it removes self-pairings (Alice with Alice) and kills the mirrored duplicate (Bob, Alice). Dave's NULL department matches nothing because NULL never equals NULL, and Erin is alone in her phantom department 9.

6. Semi-Join and Anti-Join with EXISTS

A semi-join answers "which left rows have at least one match on the right?" — without returning any right-table columns and, crucially, without duplicating left rows when the right side matches multiple times. SQL expresses it with EXISTS:

-- Employees that have a valid department (semi-join)
SELECT e.emp_name
FROM employees e
WHERE EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.dept_id = e.dept_id
)
ORDER BY e.emp_name;
-- Result: Alice, Bob, Carol

The subquery never returns data — only "true or false" — so the engine can stop at the first match (SELECT 1 is idiomatic; the column list is irrelevant). Flip it to NOT EXISTS and you get an anti-join: rows with no match.

-- Employees with missing or dangling departments (anti-join)
SELECT e.emp_name, e.dept_id
FROM employees e
WHERE NOT EXISTS (
    SELECT 1
    FROM departments d
    WHERE d.dept_id = e.dept_id
)
ORDER BY e.emp_name;
-- Result: Dave (NULL), Erin (9)  <- your data-quality report

Why not just use JOIN?

Because a plain JOIN can multiply rows. If two departments shared an emp_id-matching row (or you join orders to order_lines), a JOIN returns the left row once per match and your COUNT or SUM silently inflates. EXISTS returns each left row at most once regardless of how many matches exist — that is the whole reason the semi-join exists as a concept.

Pitfall: NOT IN with NULLs

The tempting alternative to NOT EXISTS is NOT IN, and it has a notorious trap. If the subquery's column contains a single NULL, NOT IN returns zero rows:

-- Suppose dept_id can be NULL in departments.
SELECT emp_name
FROM employees
WHERE dept_id NOT IN (SELECT dept_id FROM departments);
-- If any departments.dept_id is NULL: empty result, every time.

x NOT IN (1, 2, NULL) evaluates to x <> 1 AND x <> 2 AND x <> NULL, and the last comparison is UNKNOWN, which sinks the whole expression. NOT EXISTS has no such failure mode — prefer it for anti-joins.

7. Join Types Cheat Sheet and Pitfalls

Everything on one screen. "Keeps unmatched rows from" is the property that defines each join type:

The pitfalls worth memorizing

1. WHERE on a right-table column degrades LEFT JOIN to INNER JOIN. Move right-table conditions into ON, or add OR col IS NULL.
2. COUNT(*) counts padded rows. After a LEFT JOIN, use COUNT(right_table.col) so NULL placeholders aren't counted as real matches.
3. NULL never joins to NULL. Nullable foreign keys silently drop rows from inner joins. Decide explicitly how unmatched-NULL rows should appear.
4. One-to-many joins multiply rows. Joining orders to order_lines and then SUM(order_total) counts each order once per line. Pre-aggregate in a subquery, or use a semi-join when you only need existence.
5. Forgotten ON = Cartesian product. Every table in the FROM clause must participate in a join condition unless you truly want a CROSS JOIN.
6. RIGHT JOIN hurts readability. Rewrite as LEFT JOIN so the driving table is always first.
7. NOT IN breaks on NULL. Use NOT EXISTS for anti-joins.

Get these seven behaviors internalized and join-related bugs stop being mysterious: every surprising result traces back to one row on one side that did — or didn't — match.

FAQ