SQL Joins Explained: The Visual Guide with Examples (2026)

What Are SQL Joins?

SQL joins combine rows from two or more tables based on a related column between them. Without joins, you would need to store all your data in a single flat table, which leads to massive duplication and maintenance headaches.

Every join operates on two tables (left and right) and a join condition that defines how rows are matched. The type of join determines what happens to rows that do not match.

Here is the sample data we will use throughout this guide:

employees table

CREATE TABLE employees (
    id      INT PRIMARY KEY,
    name    VARCHAR(50),
    dept_id INT
);

INSERT INTO employees VALUES
(1, 'Alice',   10),
(2, 'Bob',     20),
(3, 'Charlie', 10),
(4, 'Diana',   30),
(5, 'Eve',     NULL);

departments table

CREATE TABLE departments (
    id   INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO departments VALUES
(10, 'Engineering'),
(20, 'Marketing'),
(30, 'Sales'),
(40, 'HR');

Notice that Eve has no department (NULL), and the HR department (id 40) has no employees. These edge cases make it easy to see how each join type behaves differently.

Here is a quick visual overview of all six join types:

  INNER JOIN          LEFT JOIN           RIGHT JOIN
  ┌─────┬─────┐      ┌─────┬─────┐      ┌─────┬─────┐
  │  A  │  B  │      │  A  │  B  │      │  A  │  B  │
  │  ┌──┼──┐  │      │█████┼──┐  │      │  ┌──┼█████│
  │  │██│██│  │      │█████│██│  │      │  │██│█████│
  │  └──┼──┘  │      │█████┼──┘  │      │  └──┼█████│
  └─────┴─────┘      └─────┴─────┘      └─────┴─────┘
  Only matching       All of A +          All of B +
  rows                matching B          matching A

  FULL OUTER JOIN     CROSS JOIN          SELF JOIN
  ┌─────┬─────┐      ┌─────┬─────┐      ┌──────────┐
  │█████┼█████│      │ A x B = every│    │  A joins │
  │█████│█████│      │ combination  │    │  itself  │
  │█████┼█████│      │ of rows      │    │  A ⟷ A   │
  └─────┴─────┘      └─────┴─────┘      └──────────┘
  All rows from       Cartesian           Table joined
  both tables         product             to itself

INNER JOIN: The Most Common Join

INNER JOIN returns only the rows where the join condition is satisfied in both tables. If a row in the left table has no match in the right table (or vice versa), it is excluded from the result.

  Table A           Table B
  ┌───────┐         ┌───────┐
  │       │         │       │
  │   ┌───┼─────────┼───┐   │
  │   │███│ RESULT  │███│   │
  │   └───┼─────────┼───┘   │
  │       │         │       │
  └───────┘         └───────┘
  Only the overlap is returned.

Syntax

SELECT columns
FROM table_a
INNER JOIN table_b ON table_a.key = table_b.key;

Example

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

Result

 id | employee | department
----+----------+-------------
  1 | Alice    | Engineering
  2 | Bob      | Marketing
  3 | Charlie  | Engineering
  4 | Diana    | Sales

Eve is missing because her dept_id is NULL (no match). HR is missing because no employee has dept_id = 40. INNER JOIN keeps only the intersection.

JOIN without a prefix defaults to INNER JOIN in all major databases. Writing INNER JOIN explicitly is a good practice for readability.

When to use: When you only want rows that exist in both tables. This is the default and most common join type.

LEFT JOIN (LEFT OUTER JOIN)

LEFT JOIN returns all rows from the left table and the matching rows from the right table. Where there is no match, the right side columns contain NULL.

  Table A           Table B
  ┌───────┐         ┌───────┐
  │███████│         │       │
  │███████┼─────────┼───┐   │
  │███████│ matched │███│   │
  │███████┼─────────┼───┘   │
  │███████│         │       │
  └───────┘         └───────┘
  All of A is returned, matched B where possible.

Syntax

SELECT columns
FROM table_a
LEFT JOIN table_b ON table_a.key = table_b.key;

Example

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

Result

 id | employee | department
----+----------+-------------
  1 | Alice    | Engineering
  2 | Bob      | Marketing
  3 | Charlie  | Engineering
  4 | Diana    | Sales
  5 | Eve      | NULL

Eve now appears with NULL for department. HR is still absent because departments is the right table and has no guarantee of appearing.

Finding rows with no match

A very common pattern is using LEFT JOIN + WHERE right_table.key IS NULL to find orphaned records:

-- Find employees not assigned to any department
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL;

Result

 name
------
 Eve

Common pitfall: Adding a WHERE filter on the right table (e.g., WHERE d.name = 'Engineering') silently converts a LEFT JOIN into an INNER JOIN because NULL values are filtered out. Move such conditions into the ON clause instead.

When to use: When you need all records from the primary table regardless of whether a related record exists. Examples: all customers including those with no orders, all products including those never sold.

RIGHT JOIN (RIGHT OUTER JOIN)

RIGHT JOIN is the mirror of LEFT JOIN. It returns all rows from the right table and the matching rows from the left table. Unmatched left-side columns are NULL.

  Table A           Table B
  ┌───────┐         ┌───────┐
  │       │         │███████│
  │   ┌───┼─────────┼███████│
  │   │███│ matched │███████│
  │   └───┼─────────┼███████│
  │       │         │███████│
  └───────┘         └───────┘
  All of B is returned, matched A where possible.

Syntax

SELECT columns
FROM table_a
RIGHT JOIN table_b ON table_a.key = table_b.key;

Example

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

Result

 employee | dept_id | department
----------+---------+-------------
 Alice    |      10 | Engineering
 Charlie  |      10 | Engineering
 Bob      |      20 | Marketing
 Diana    |      30 | Sales
 NULL     |      40 | HR

HR now appears with NULL for employee. Eve is excluded because employees is the left table. In practice, most developers prefer LEFT JOIN and simply swap the table order instead of using RIGHT JOIN, since the result is identical.

When to use: Rarely in practice. You can always rewrite a RIGHT JOIN as a LEFT JOIN by switching the table order. Use it when the query reads more naturally with the secondary table on the left.

FULL OUTER JOIN

FULL OUTER JOIN returns all rows from both tables. Where a row from one table has no match in the other, the missing side is filled with NULLs. This is the union of LEFT JOIN and RIGHT JOIN.

  Table A           Table B
  ┌───────┐         ┌───────┐
  │███████│         │███████│
  │███████┼─────────┼███████│
  │███████│ matched │███████│
  │███████┼─────────┼███████│
  │███████│         │███████│
  └───────┘         └───────┘
  Everything from both sides is returned.

Syntax

SELECT columns
FROM table_a
FULL OUTER JOIN table_b ON table_a.key = table_b.key;

Example

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
 Charlie  | Engineering
 Diana    | Sales
 Eve      | NULL
 NULL     | HR

Both Eve (no department) and HR (no employees) appear in the result. This is the most inclusive join type.

MySQL workaround

MySQL does not support FULL OUTER JOIN directly. Simulate it by combining LEFT and RIGHT joins with UNION:

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

UNION

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

When to use: Data reconciliation between two sources, finding orphaned records on both sides, merging datasets where either side may have gaps.

CROSS JOIN and SELF JOIN

CROSS JOIN

CROSS JOIN produces the cartesian product of two tables: every row from the left table is paired with every row from the right table. There is no ON clause.

  sizes: S, M, L         colors: Red, Blue

  CROSS JOIN result:
  ┌───────────┬───────────┐
  │ S         │ Red       │
  │ S         │ Blue      │
  │ M         │ Red       │
  │ M         │ Blue      │
  │ L         │ Red       │
  │ L         │ Blue      │
  └───────────┴───────────┘
  3 sizes x 2 colors = 6 rows

Syntax

SELECT columns
FROM table_a
CROSS JOIN table_b;

Example

CREATE TABLE sizes (size_name VARCHAR(5));
INSERT INTO sizes VALUES ('S'), ('M'), ('L');

CREATE TABLE colors (color_name VARCHAR(10));
INSERT INTO colors VALUES ('Red'), ('Blue');

SELECT s.size_name, c.color_name
FROM sizes s
CROSS JOIN colors c
ORDER BY s.size_name, c.color_name;

Result

 size_name | color_name
-----------+------------
 L         | Blue
 L         | Red
 M         | Blue
 M         | Red
 S         | Blue
 S         | Red

Warning: CROSS JOIN output grows multiplicatively. Joining a 1,000-row table to a 1,000-row table produces 1,000,000 rows. Use it only for small reference tables or deliberate combinations.

When to use: Generating all possible combinations (product variants, date/category grids for reporting), creating test data, and calendar table generation.

SELF JOIN

A self join is when a table is joined to itself. This is not a separate syntax; you use any JOIN type with different aliases for the same table.

CREATE TABLE staff (
    id         INT PRIMARY KEY,
    name       VARCHAR(50),
    manager_id INT
);

INSERT INTO staff VALUES
(1, 'Alice',   NULL),
(2, 'Bob',     1),
(3, 'Charlie', 1),
(4, 'Diana',   2),
(5, 'Eve',     2);

Example: find each employee's manager

SELECT
    e.name AS employee,
    m.name AS manager
FROM staff e
LEFT JOIN staff m ON e.manager_id = m.id;

Result

 employee | manager
----------+---------
 Alice    | NULL
 Bob      | Alice
 Charlie  | Alice
 Diana    | Bob
 Eve      | Bob

Alice has no manager (NULL). The key is using two different aliases (e and m) for the same table so SQL treats them as two separate tables.

Example: find colleagues in the same department

SELECT
    e1.name AS employee_1,
    e2.name AS employee_2
FROM employees e1
INNER JOIN employees e2
    ON e1.dept_id = e2.dept_id
    AND e1.id < e2.id;

Result

 employee_1 | employee_2
------------+------------
 Alice      | Charlie

The e1.id < e2.id condition prevents duplicate pairs (Alice-Charlie and Charlie-Alice) and prevents pairing an employee with themselves.

When to use: Hierarchical data (employee-manager, category-subcategory), finding duplicates within a table, comparing rows in the same table.

Advanced Join Patterns

Real-world queries often require more than a simple two-table join. Here are patterns you will use regularly.

Joining multiple tables

Chain JOIN clauses to combine three or more tables:

SELECT
    o.id AS order_id,
    c.name AS customer,
    p.name AS product,
    oi.quantity,
    oi.unit_price
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
WHERE o.order_date >= '2026-01-01'
ORDER BY o.id;

Each JOIN adds one more table. You can mix join types in the same query. Use INNER JOIN for required relationships and LEFT JOIN for optional ones:

SELECT
    o.id AS order_id,
    c.name AS customer,
    p.name AS product,
    d.code AS discount_code
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
INNER JOIN order_items oi ON o.id = oi.order_id
INNER JOIN products p ON oi.product_id = p.id
LEFT JOIN discounts d ON o.discount_id = d.id;

Result

 order_id | customer | product    | discount_code
----------+----------+------------+---------------
        1 | Alice    | Laptop     | SAVE10
        2 | Bob      | Desk Chair | NULL
        3 | Alice    | Monitor    | SAVE10
        4 | Charlie  | Laptop     | NULL

Orders without a discount code show NULL instead of being excluded.

JOIN with subquery

Use a subquery as a derived table in the FROM clause:

SELECT
    e.name AS employee,
    e.salary,
    dept_stats.avg_salary,
    e.salary - dept_stats.avg_salary AS diff_from_avg
FROM employees e
INNER JOIN (
    SELECT
        dept_id,
        AVG(salary) AS avg_salary,
        COUNT(*) AS team_size
    FROM employees
    WHERE dept_id IS NOT NULL
    GROUP BY dept_id
) dept_stats ON e.dept_id = dept_stats.dept_id
ORDER BY diff_from_avg DESC;

Result

 employee | salary | avg_salary | diff_from_avg
----------+--------+------------+---------------
 Alice    |  95000 |      82500 |         12500
 Charlie  |  70000 |      82500 |        -12500
 Bob      |  75000 |      75000 |             0
 Diana    |  80000 |      80000 |             0

The subquery runs first and produces a temporary result set. The outer query joins to it like any other table.

JOIN with CTE

CTEs (Common Table Expressions) make complex join queries more readable than nested subqueries:

WITH monthly_orders AS (
    SELECT
        customer_id,
        DATE_TRUNC('month', order_date) AS month,
        COUNT(*) AS order_count,
        SUM(total_amount) AS monthly_total
    FROM orders
    GROUP BY customer_id, DATE_TRUNC('month', order_date)
),
customer_segments AS (
    SELECT
        customer_id,
        CASE
            WHEN SUM(monthly_total) > 10000 THEN 'VIP'
            WHEN SUM(monthly_total) > 1000  THEN 'Regular'
            ELSE 'Occasional'
        END AS segment
    FROM monthly_orders
    GROUP BY customer_id
)
SELECT
    c.name,
    cs.segment,
    mo.month,
    mo.order_count,
    mo.monthly_total
FROM customers c
INNER JOIN customer_segments cs ON c.id = cs.customer_id
INNER JOIN monthly_orders mo ON c.id = mo.customer_id
WHERE cs.segment = 'VIP'
ORDER BY c.name, mo.month;

Result

 name  | segment | month      | order_count | monthly_total
-------+---------+------------+-------------+--------------
 Alice | VIP     | 2026-01-01 |           5 |       4200.00
 Alice | VIP     | 2026-02-01 |           8 |       6100.00
 Alice | VIP     | 2026-03-01 |           3 |       2800.00

Each CTE is defined once and can be referenced multiple times in the final query. This is far cleaner than nesting subqueries inside subqueries.

Skip the syntax and describe your join in plain English. AI2SQL generates multi-table joins from natural language descriptions.

Join Performance Tips

Joins are where most query performance problems occur. Here are concrete steps to keep them fast.

1. Index your join columns

-- If you frequently join on customer_id, index it
CREATE INDEX idx_orders_customer_id ON orders(customer_id);

-- Composite index for multi-column joins
CREATE INDEX idx_order_items_order_product
ON order_items(order_id, product_id);

Without an index, the database performs a full table scan for every row in the outer table. With an index, it does an index lookup, which is orders of magnitude faster on large tables.

2. Use EXPLAIN to diagnose slow joins

EXPLAIN ANALYZE
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

-- Look for:
-- "Seq Scan" (PostgreSQL) or "type: ALL" (MySQL) = missing index
-- "Index Scan" = index is being used (good)
-- "Nested Loop" vs "Hash Join" vs "Merge Join" = join strategy

If you see a sequential scan on a large table, adding an index on the join column almost always fixes it.

3. Filter before joining

-- OK: optimizer usually handles this
SELECT c.name, o.total_amount
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
WHERE o.order_date >= '2026-01-01'
  AND o.status = 'completed';

-- Better for complex multi-join queries: pre-filter with CTE
WITH recent_orders AS (
    SELECT customer_id, total_amount
    FROM orders
    WHERE order_date >= '2026-01-01'
      AND status = 'completed'
)
SELECT c.name, ro.total_amount
FROM customers c
INNER JOIN recent_orders ro ON c.id = ro.customer_id;

Modern query optimizers often handle this automatically, but for complex multi-join queries, pre-filtering with CTEs can make a significant difference.

4. Avoid functions on join columns

-- Bad: function on join column prevents index use
SELECT *
FROM orders o
JOIN customers c ON LOWER(o.customer_email) = LOWER(c.email);

-- Better: normalize data at insert time, join on indexed column
SELECT *
FROM orders o
JOIN customers c ON o.customer_email_lower = c.email_lower;

5. Use EXISTS for existence checks

-- JOIN approach (may produce duplicates, needs DISTINCT)
SELECT DISTINCT c.name
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

-- EXISTS approach (stops at first match, no duplicates)
SELECT c.name
FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

EXISTS is typically faster because it stops scanning as soon as it finds the first matching row.

6. Watch out for accidental cartesian products

-- Missing ON clause creates a CROSS JOIN (potentially millions of rows)
SELECT c.name, o.id
FROM customers c, orders o;  -- implicit cross join!

-- Always use explicit JOIN with ON
SELECT c.name, o.id
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

Quick reference table

Join Type	Returns	Use When
INNER JOIN	Matching rows only	You need data from both tables
LEFT JOIN	All left + matching right	Keep all records from primary table
RIGHT JOIN	All right + matching left	Rarely needed; rewrite as LEFT JOIN
FULL OUTER	All rows from both	Data reconciliation, finding gaps
CROSS JOIN	Every combination	Generating grids, test data
SELF JOIN	Table joined to itself	Hierarchies, finding duplicates

Try building joins with AI2SQL - describe the tables and the data you need, and get the correct SQL generated automatically. No syntax to memorize.

Frequently Asked Questions

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows where there is a match in both tables. LEFT JOIN returns all rows from the left table and the matched rows from the right table. If there is no match in the right table, the result contains NULL values for those columns. Use INNER JOIN when you only want matching data, and LEFT JOIN when you need all records from one table regardless of matches.

When should I use FULL OUTER JOIN?

Use FULL OUTER JOIN when you need all rows from both tables, whether or not they have matching rows in the other table. Common use cases include data reconciliation (comparing two data sources), finding orphaned records in either table, and merging datasets where either side may have missing entries. Note that MySQL does not support FULL OUTER JOIN directly; you need to combine LEFT JOIN and RIGHT JOIN with UNION.

How do I join more than two tables in SQL?

Chain multiple JOIN clauses one after another. For example: SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id JOIN products ON orders.product_id = products.id. Each JOIN adds another table to the result set. You can mix different join types (INNER, LEFT, etc.) in the same query. For readability, use table aliases and format each JOIN on its own line.

Why is my SQL JOIN returning duplicate rows?

Duplicate rows in JOIN results usually mean there are multiple matching rows in one or both tables. For example, if a customer has 3 orders and you JOIN customers to orders, that customer appears 3 times. Solutions include: using DISTINCT to remove duplicates, aggregating with GROUP BY, or checking if your join condition is specific enough. A missing or incorrect ON clause can also cause a cartesian product, multiplying rows.

Can AI write SQL joins for me?

Yes. Tools like AI2SQL let you describe the data you need in plain English, and the AI generates the correct JOIN query automatically. This is especially helpful for complex multi-table joins, self joins, and queries combining joins with GROUP BY or window functions. You describe what you want, connect your database, and get working SQL in seconds.