SQL Joins Explained: INNER, LEFT, RIGHT, FULL, and More

SQL joins are the backbone of relational databases. Once you understand how each join type works — and more importantly, when to use each one — you can extract exactly the data you need from any database. This guide walks through every join type with concrete data examples, so you can see precisely what each query returns.

Sample Data

Throughout this guide, we'll use two simple tables:

-- employees table
id | name    | dept_id
---+---------+--------
 1 | Alice   | 1
 2 | Bob     | 2
 3 | Carol   | 1
 4 | Dave    | NULL  (no department assigned)
 5 | Eve     | 3

-- departments table
id | name
---+---------------
 1 | Engineering
 2 | Marketing
 3 | Design
 4 | HR           (no employees yet)

INNER JOIN

An INNER JOIN returns rows where there is a match in both tables. Rows with no match in either table are excluded.

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

Result:

employee | department
---------+-----------
Alice    | Engineering
Bob      | Marketing
Carol    | Engineering
Eve      | Design

Notice: Dave (no dept_id) and HR (no employees) are both excluded because there's no match.

When to use: You only want rows that have a valid relationship in both tables. This is the most common join type.

-- Find all orders with their customer details
SELECT o.id, o.total, c.name, c.email
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed';

-- Three-table join
SELECT o.id, p.name AS product, c.name AS customer
FROM order_items oi
INNER JOIN orders o ON oi.order_id = o.id
INNER JOIN products p ON oi.product_id = p.id
INNER JOIN customers c ON o.customer_id = c.id;

LEFT JOIN (LEFT OUTER JOIN)

A LEFT JOIN returns all rows from the left table, plus matching rows from the right table. If there's no match in the right table, NULL values fill the right-side columns.

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

Result:

employee | department
---------+-----------
Alice    | Engineering
Bob      | Marketing
Carol    | Engineering
Dave     | NULL         ← Dave has no dept, but still appears
Eve      | Design

The left table (employees) is fully included. Dave appears even though he has no matching department.

When to use: You want all records from the primary table, with optional related data. Classic use case: "show me all users, and their subscription details if they have one."

-- All customers and their total orders (even those with no orders)
SELECT c.name, COUNT(o.id) AS order_count, SUM(o.total) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id, c.name
ORDER BY total_spent DESC NULLS LAST;

-- Find records with NO match (the "anti-join" pattern)
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL;
-- Returns: Dave (employee with no department)

-- Find users who have never made a purchase
SELECT u.name
FROM users u
LEFT JOIN purchases p ON u.id = p.user_id
WHERE p.id IS NULL;

RIGHT JOIN (RIGHT OUTER JOIN)

The mirror of LEFT JOIN. Returns all rows from the right table, with NULLs for unmatched left rows.

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

Result:

employee | department
---------+-----------
Alice    | Engineering
Carol    | Engineering
Bob      | Marketing
Eve      | Design
NULL     | HR           ← HR has no employees, but still appears

The right table (departments) is fully included. HR appears even though it has no employees.

In practice, RIGHT JOIN is rarely used — most developers flip the tables and use LEFT JOIN instead, which reads more naturally (left-to-right reading order matches the table that's fully included).

-- This RIGHT JOIN
SELECT e.name, d.name FROM employees e RIGHT JOIN departments d ON e.dept_id = d.id;

-- Is equivalent to this LEFT JOIN (just swap the tables)
SELECT e.name, d.name FROM departments d LEFT JOIN employees e ON e.dept_id = d.id;

FULL OUTER JOIN

Returns all rows from both tables. Where there's no match, NULLs fill in.

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

Result:

employee | department
---------+-----------
Alice    | Engineering
Bob      | Marketing
Carol    | Engineering
Dave     | NULL
Eve      | Design
NULL     | HR

Both Dave (no department) and HR (no employees) appear.

Note: MySQL does not support FULL OUTER JOIN natively. Simulate it with UNION:

-- MySQL FULL OUTER JOIN workaround
SELECT e.name, d.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id

UNION

SELECT e.name, d.name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

When to use: Data reconciliation, auditing, or when you need to see all records from two tables and identify what's matched and what's not.

CROSS JOIN

Returns the Cartesian product — every row from the left table combined with every row from the right table. No ON clause needed.

-- All possible employee-department combinations
SELECT e.name, d.name
FROM employees e
CROSS JOIN departments d;
-- Returns 5 employees × 4 departments = 20 rows

-- Practical: generate a calendar grid
SELECT months.m, days.d
FROM
  (SELECT 1 AS m UNION SELECT 2 UNION SELECT 3 ... UNION SELECT 12) AS months
CROSS JOIN
  (SELECT 1 AS d UNION SELECT 2 ... UNION SELECT 31) AS days;

When to use: Generating combinations (size matrices × color options), building test data, or computing all pairings.

SELF JOIN

A table joined to itself. Useful for hierarchical data (org charts, categories with parent-child relationships) or comparing rows within the same table.

-- employees with a manager_id referencing the same table
-- id | name    | manager_id
-- 1  | CEO     | NULL
-- 2  | Alice   | 1
-- 3  | Bob     | 1
-- 4  | Carol   | 2

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
-- CEO      | NULL
-- Alice    | CEO
-- Bob      | CEO
-- Carol    | Alice

-- Find employees with the same department
SELECT a.name AS emp1, b.name AS emp2
FROM employees a
INNER JOIN employees b ON a.dept_id = b.dept_id
WHERE a.id < b.id;  -- prevent (A,B) and (B,A) duplicates

Advanced Join Patterns

Multiple Join Conditions

-- Join on multiple columns
SELECT *
FROM orders o
INNER JOIN shipments s
  ON o.id = s.order_id
  AND o.warehouse_id = s.warehouse_id;

-- Non-equi join (join on a condition other than equality)
-- Find employees whose salary is within a pay grade range
SELECT e.name, e.salary, g.grade
FROM employees e
INNER JOIN pay_grades g
  ON e.salary BETWEEN g.min_salary AND g.max_salary;

Joining on Subqueries

-- Join with an aggregated subquery
SELECT d.name AS department, dept_avg.avg_salary
FROM departments d
INNER JOIN (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
) AS dept_avg ON d.id = dept_avg.dept_id;

-- Using CTEs (cleaner than subqueries)
WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_salary, COUNT(*) AS headcount
    FROM employees
    GROUP BY dept_id
)
SELECT d.name, da.avg_salary, da.headcount
FROM departments d
LEFT JOIN dept_avg da ON d.id = da.dept_id
ORDER BY da.avg_salary DESC NULLS LAST;

Lateral Joins

-- PostgreSQL LATERAL: correlated subquery in a join
-- Get the 3 most recent orders per customer
SELECT c.name, recent.id, recent.total
FROM customers c
CROSS JOIN LATERAL (
    SELECT id, total
    FROM orders
    WHERE customer_id = c.id
    ORDER BY created_at DESC
    LIMIT 3
) AS recent;

Performance Tips

• Index your join columns. Both sides of the ON condition should be indexed. Without indexes, joins require full table scans.
• Filter early. Apply WHERE conditions before joining when possible, or use subqueries/CTEs to pre-filter large tables.
• Prefer INNER JOIN over LEFT JOIN when you can. INNER JOIN is often faster because it excludes non-matching rows earlier in execution.
• Use EXPLAIN / EXPLAIN ANALYZE to inspect the query plan and spot missing indexes.

-- Always explain a slow join query
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- Add index if missing
CREATE INDEX idx_orders_user_id ON orders(user_id);

Quick Reference

INNER JOIN   — only matching rows from BOTH tables
LEFT JOIN    — all from left + matching from right (NULLs for no match)
RIGHT JOIN   — all from right + matching from left (NULLs for no match)
FULL JOIN    — all from both tables (NULLs for no match on either side)
CROSS JOIN   — every combination (left rows × right rows)
SELF JOIN    — table joined to itself (use aliases)

-- Template
SELECT columns
FROM left_table [alias]
[INNER | LEFT | RIGHT | FULL OUTER | CROSS] JOIN right_table [alias]
  ON join_condition
[WHERE filter_condition]
[GROUP BY ...]
[HAVING ...]
[ORDER BY ...]

-- Anti-join (find rows with no match)
SELECT l.*
FROM left_table l
LEFT JOIN right_table r ON l.id = r.left_id
WHERE r.id IS NULL;

SQL joins are fundamental, and once you've worked through a few real queries, the logic becomes intuitive. The most important mental model: the join type determines which rows survive when there's no match. INNER keeps only matched pairs; LEFT keeps all of the left; FULL keeps everything. Master these four, and you can handle virtually any data retrieval problem in a relational database.

Free Developer Tools

If you found this article helpful, check out DevToolkit — 40+ free browser-based developer tools with no signup required.

Popular tools: JSON Formatter · Regex Tester · JWT Decoder · Base64 Encoder

🛒 Get the DevToolkit Starter Kit on Gumroad — source code, deployment guide, and customization templates.