SQL Joins Explained: INNER, LEFT, RIGHT, FULL & CROSS with Examples

Sample Tables for All Examples

Every join example in this guide uses the same two tables. Having consistent sample data makes it easier to see exactly how each join type behaves differently.

-- employees table
+----+----------+---------------+
| id | name     | department_id |
+----+----------+---------------+
|  1 | Alice    |             1 |
|  2 | Bob      |             2 |
|  3 | Charlie  |             1 |
|  4 | Diana    |          NULL |  -- No department assigned
+----+----------+---------------+

-- departments table
+----+-------------+
| id | name        |
+----+-------------+
|  1 | Engineering |
|  2 | Marketing   |
|  3 | Sales       |  -- No employees assigned
+----+-------------+

Notice that Diana has no department (NULL), and the Sales department has no employees. These edge cases make the differences between join types immediately visible.

INNER JOIN: Only Matching Rows

INNER JOIN is the most common join type. It returns only rows where there is a match in both tables. Rows without a match in either table are excluded.

SELECT e.name AS employee, d.name AS department
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

-- Result:
+----------+-------------+
| employee | department  |
+----------+-------------+
| Alice    | Engineering |
| Bob      | Marketing   |
| Charlie  | Engineering |
+----------+-------------+
-- Diana excluded (no department_id)
-- Sales excluded (no matching employees)

INNER JOIN is the default join type in most databases. Writing JOIN without a qualifier is equivalent to INNER JOIN.

LEFT JOIN: All Left Rows + Matches

LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table, plus matched rows from the right table. When there is no match, the right-side columns contain NULL.

SELECT e.name AS employee, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- Result:
+----------+-------------+
| employee | department  |
+----------+-------------+
| Alice    | Engineering |
| Bob      | Marketing   |
| Charlie  | Engineering |
| Diana    | NULL        |  -- Included! No match, so NULL
+----------+-------------+
-- Sales still excluded (it's in the RIGHT table)

-- Common pattern: Find employees WITHOUT a department
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id
WHERE d.id IS NULL;
-- Result: Diana

LEFT JOIN is essential for finding "orphaned" records and for reports that need to include all primary records even when related data is missing. You can format the SQL output as JSON using our JSON Formatter when building API endpoints that return join results.

RIGHT JOIN: All Right Rows + Matches

RIGHT JOIN is the mirror of LEFT JOIN. It returns all rows from the right table, plus matched rows from the left table. In practice, RIGHT JOIN is rarely used because you can achieve the same result by swapping the table order in a LEFT JOIN.

SELECT e.name AS employee, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

-- Result:
+----------+-------------+
| employee | department  |
+----------+-------------+
| Alice    | Engineering |
| Charlie  | Engineering |
| Bob      | Marketing   |
| NULL     | Sales       |  -- Included! No employees, so NULL
+----------+-------------+
-- Diana excluded (she's in the LEFT table without a match)

-- Equivalent LEFT JOIN (preferred — more readable):
SELECT e.name AS employee, d.name AS department
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id;

FULL OUTER JOIN: All Rows from Both Tables

FULL OUTER JOIN returns all rows from both tables, with NULL filling in where there is no match. It combines the results of LEFT JOIN and RIGHT JOIN.

SELECT e.name AS employee, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

-- Result:
+----------+-------------+
| employee | department  |
+----------+-------------+
| Alice    | Engineering |
| Bob      | Marketing   |
| Charlie  | Engineering |
| Diana    | NULL        |  -- No department
| NULL     | Sales       |  -- No employees
+----------+-------------+

-- Find ALL unmatched records from both sides
SELECT e.name AS employee, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id
WHERE e.id IS NULL OR d.id IS NULL;
-- Result: Diana (no dept) + Sales (no employees)

-- Note: MySQL does NOT support FULL OUTER JOIN
-- Workaround: UNION of LEFT JOIN and RIGHT JOIN
SELECT e.name, d.name FROM employees e LEFT JOIN departments d ON e.department_id = d.id
UNION
SELECT e.name, d.name FROM employees e RIGHT JOIN departments d ON e.department_id = d.id;

CROSS JOIN: Cartesian Product

CROSS JOIN produces every possible combination of rows from both tables. No ON clause is needed because there is no matching condition. Use it deliberately for generating combinations, but beware of the explosive row count.

-- Generate all employee-department combinations
SELECT e.name AS employee, d.name AS department
FROM employees e
CROSS JOIN departments d;

-- Result: 4 employees × 3 departments = 12 rows
+----------+-------------+
| employee | department  |
+----------+-------------+
| Alice    | Engineering |
| Alice    | Marketing   |
| Alice    | Sales       |
| Bob      | Engineering |
| Bob      | Marketing   |
| Bob      | Sales       |
| Charlie  | Engineering |
| ...      | ...         |
+----------+-------------+

-- Practical example: Generate a calendar of dates × products
SELECT d.date, p.name AS product
FROM generate_series('2026-01-01'::date, '2026-12-31'::date, '1 day') AS d(date)
CROSS JOIN products p
ORDER BY d.date, p.name;

Self JOIN: Joining a Table to Itself

A self join joins a table to itself, which is useful for hierarchical data (employee-manager relationships, category trees) and comparing rows within the same table.

-- employees table with manager_id (self-referencing)
+----+---------+------------+
| id | name    | manager_id |
+----+---------+------------+
|  1 | Alice   |       NULL |  -- CEO, no manager
|  2 | Bob     |          1 |  -- Reports to Alice
|  3 | Charlie |          1 |  -- Reports to Alice
|  4 | Diana   |          2 |  -- Reports to Bob
+----+---------+------------+

-- Find each employee's manager
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Result:
+----------+---------+
| employee | manager |
+----------+---------+
| Alice    | NULL    |  -- No manager (CEO)
| Bob      | Alice   |
| Charlie  | Alice   |
| Diana    | Bob     |
+----------+---------+

-- Find employees who share the same manager
SELECT e1.name AS employee1, e2.name AS employee2, m.name AS shared_manager
FROM employees e1
JOIN employees e2 ON e1.manager_id = e2.manager_id AND e1.id < e2.id
JOIN employees m ON e1.manager_id = m.id;
-- Result: Bob & Charlie both report to Alice

Join Comparison Summary

Performance Optimization Tips

Joins can be expensive on large tables. These optimization strategies can reduce query time from minutes to milliseconds.

-- 1. Always index join columns
CREATE INDEX idx_employees_dept ON employees(department_id);
CREATE INDEX idx_orders_user ON orders(user_id);

-- 2. Use EXPLAIN ANALYZE to understand query plans
EXPLAIN ANALYZE
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name = 'Engineering';

-- 3. Filter early with WHERE before joining
-- SLOW: Join everything, then filter
SELECT o.id, o.total
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE o.created_at > '2026-01-01';

-- FASTER: Filter in subquery, then join
SELECT o.id, o.total
FROM (SELECT * FROM orders WHERE created_at > '2026-01-01') o
JOIN products p ON o.product_id = p.id;

-- 4. Select only needed columns (avoid SELECT *)
-- BAD:  SELECT * FROM orders JOIN users ON ...
-- GOOD: SELECT o.id, o.total, u.email FROM orders o JOIN users u ON ...

-- 5. Use EXISTS instead of JOIN for existence checks
-- Instead of: SELECT DISTINCT u.* FROM users u JOIN orders o ON u.id = o.user_id
SELECT * FROM users u WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

Format your complex SQL queries for readability using our SQL Formatter tool. Properly formatted SQL makes join conditions and WHERE clauses easier to review in code reviews.

Related Articles