Sample Tables — Simple Data

All JOIN examples below use these two tiny tables so you can see exactly what each JOIN returns. Keep them in mind — every output table is derived from these rows.

Why JOIN?

Real databases separate data into related tables. JOIN brings them together.

INNER JOIN

INNER JOIN returns only rows where the join condition matches in both tables.

SELECT e.first_name, d.department_name
FROM employees e
INNER JOIN departments d
    ON e.department_id = d.department_id;

Returns matching records only.

LEFT JOIN

LEFT JOIN returns all rows from the left table and matching rows from the right, filling missing matches with NULLs.

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

All departments, including those with no employees.

RIGHT JOIN

RIGHT JOIN returns all rows from the right table and matching rows from the left, the mirror of LEFT JOIN.

SELECT e.first_name, d.department_name
FROM employees e
RIGHT JOIN departments d
    ON e.department_id = d.department_id;

FULL OUTER JOIN

FULL OUTER JOIN returns all rows from both tables, matching where possible and filling gaps with NULLs on either side.

SELECT e.first_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d
    ON e.department_id = d.department_id;

MySQL has no native FULL OUTER JOIN — use UNION of LEFT and RIGHT.

SELF JOIN

SELF JOIN joins a table to itself, treating the same table as two logical copies — essential for employee-manager hierarchies.

SELECT e.first_name AS employee, m.first_name AS manager
FROM employees e
LEFT JOIN employees m
    ON e.manager_id = m.employee_id;

Same table as two logical copies (e and m).

CROSS JOIN

CROSS JOIN produces the Cartesian product, combining every row from one table with every row from the other table.

SELECT a.team_name AS team_a, b.team_name AS team_b
FROM teams a
CROSS JOIN teams b;

4 × 5 = 20 rows — every combination.

Employee Earning More Than Manager

This classic interview problem finds employees who earn more than their manager by self-joining the employees table on manager_id.

SELECT e.first_name AS employee_name, e.salary, m.first_name AS manager_name, m.salary
FROM employees e
JOIN employees m
    ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

Key: e.manager_id = m.employee_id

Multi-Table JOIN

Multi-table JOIN connects more than two tables in sequence, following relationships from employees to departments to jobs.

SELECT e.first_name, d.department_name, j.job_title
FROM employees e
JOIN departments d ON e.department_id = d.department_id
JOIN jobs j ON e.job_id = j.job_id;