MySQL JOIN

How JOINs work

A JOIN combines rows from two or more tables based on a related column. Instead of storing everything in one large table, relational databases split data into separate tables and use JOINs to bring it back together at query time.

The basic pattern is:

SELECT columns
FROM table_a
JOIN table_b ON table_a.column = table_b.column;

The example schema

All examples on this page use three tables: customers, orders, and products.

CREATE TABLE customers (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  email VARCHAR(100)
);

CREATE TABLE orders (
  id INT PRIMARY KEY,
  customer_id INT,
  product_id INT,
  quantity INT,
  order_date DATE
);

CREATE TABLE products (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(8,2)
);

INSERT INTO customers VALUES
  (1, 'Alice Nguyen', 'alice@example.com'),
  (2, 'Bob Okafor',   'bob@example.com'),
  (3, 'Carol Santos', 'carol@example.com'),
  (4, 'Dana Kim',     'dana@example.com'),
  (5, 'Evan Park',    'evan@example.com');

INSERT INTO products VALUES
  (1, 'Wireless Mouse',      29.99),
  (2, 'Mechanical Keyboard', 89.99),
  (3, 'USB-C Hub',           49.99),
  (4, 'Monitor Stand',       39.99);

INSERT INTO orders VALUES
  (101, 1, 2, 1, '2025-11-01'),
  (102, 1, 3, 2, '2025-11-08'),
  (103, 2, 1, 1, '2025-11-10'),
  (104, 3, 4, 1, '2025-11-12'),
  (105, 3, 1, 2, '2025-11-15');
-- Dana (4) and Evan (5) have no orders

Notice that customers 4 and 5 have no orders. This distinction will matter for LEFT JOIN.

INNER JOIN

INNER JOIN returns only rows where the ON condition matches in both tables. It is the default JOIN type, so JOIN and INNER JOIN are equivalent.

SELECT customers.name, orders.id AS order_id, orders.order_date
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

Expected output:

| name | order_id | order_date | |------|----------|------------| | Alice Nguyen | 101 | 2025-11-01 | | Alice Nguyen | 102 | 2025-11-08 | | Bob Okafor | 103 | 2025-11-10 | | Carol Santos | 104 | 2025-11-12 | | Carol Santos | 105 | 2025-11-15 |

Dana and Evan are absent because they have no matching rows in orders.

LEFT JOIN

LEFT JOIN returns all rows from the left table, plus matched rows from the right table. Where there is no match, the right-table columns come back as NULL.

Use this when you want every row from the left table regardless of whether a match exists.

SELECT customers.name, orders.id AS order_id
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

Expected output:

| name | order_id | |------|----------| | Alice Nguyen | 101 | | Alice Nguyen | 102 | | Bob Okafor | 103 | | Carol Santos | 104 | | Carol Santos | 105 | | Dana Kim | NULL | | Evan Park | NULL |

Dana and Evan now appear, with NULL for order_id. To find customers who have never placed an order, filter on the NULL:

SELECT customers.name
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
WHERE orders.id IS NULL;

RIGHT JOIN

RIGHT JOIN is the mirror of LEFT JOIN. It returns all rows from the right table, plus matched rows from the left.

SELECT customers.name, orders.id AS order_id
FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.id;

This produces the same result as the LEFT JOIN example above. In practice, RIGHT JOIN is rarely used because you can always rewrite it as a LEFT JOIN by swapping the table order. Most teams pick one convention (usually LEFT JOIN) and stick to it for readability.

Joining on multiple conditions

Add conditions to the ON clause with AND when a single column is not enough to define the relationship, or when you want to filter at join time rather than in WHERE.

-- Match orders placed by the customer in November 2025
SELECT customers.name, orders.id, orders.order_date
FROM customers
INNER JOIN orders
  ON customers.id = orders.customer_id
  AND orders.order_date >= '2025-11-10';

Filtering in ON vs WHERE behaves differently for outer joins. For INNER JOIN the results are equivalent, but for LEFT JOIN an ON filter keeps unmatched rows (with NULLs), while a WHERE filter removes them.

Joining more than two tables

Chain additional JOINs to bring in more tables. Each JOIN operates on the result set produced so far.

SELECT
  customers.name,
  orders.order_date,
  products.name  AS product,
  orders.quantity
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
INNER JOIN products  ON orders.product_id  = products.id;

Expected output:

| name | order_date | product | quantity | |------|------------|---------|----------| | Alice Nguyen | 2025-11-01 | Mechanical Keyboard | 1 | | Alice Nguyen | 2025-11-08 | USB-C Hub | 2 | | Bob Okafor | 2025-11-10 | Wireless Mouse | 1 | | Carol Santos | 2025-11-12 | Monitor Stand | 1 | | Carol Santos | 2025-11-15 | Wireless Mouse | 2 |

Self JOIN

A self JOIN joins a table to itself. The most common use case is a table where a row can reference another row in the same table, such as an employees table with a manager_id column.

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  manager_id INT  -- references employees.id
);

To list each employee alongside their manager's name:

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

You must use aliases (e and m) because MySQL needs to distinguish between the two references to the same table. LEFT JOIN is used here so that the top-level manager (who has no manager) still appears in the results.

CROSS JOIN

CROSS JOIN returns every combination of rows from two tables (the cartesian product). A 5-row table crossed with a 4-row table produces 20 rows.

SELECT customers.name, products.name AS product
FROM customers
CROSS JOIN products;

This is intentional when you need all combinations, for example generating a schedule grid, pairing every size with every color, or creating test data. In most other queries, a CROSS JOIN appearing by accident is a bug.

Common mistakes

Missing ON clause

Forgetting the ON clause in a regular JOIN produces an accidental cartesian product:

-- Returns every customer paired with every order (almost certainly wrong)
SELECT * FROM customers, orders;

Always include an explicit ON condition.

Ambiguous column names

When two joined tables share a column name, MySQL cannot tell which one you mean:

-- Error: Column 'id' in field list is ambiguous
SELECT id, name FROM customers JOIN orders ON customers.id = orders.customer_id;

Prefix ambiguous columns with the table name or alias:

SELECT customers.id, customers.name, orders.id AS order_id
FROM customers
JOIN orders ON customers.id = orders.customer_id;

Joining on NULL

NULL does not equal NULL in SQL. If a column used in the ON clause contains NULL, those rows will never match:

-- Rows where either customer_id or id is NULL will not join
FROM orders
JOIN customers ON orders.customer_id = customers.id

If NULLs are valid and should match, use <=> (the NULL-safe equality operator in MySQL) or filter/coalesce before joining.

Quick reference

| JOIN type | Returns | |-----------|---------| | INNER JOIN | Rows with a match in both tables | | LEFT JOIN | All rows from the left table, NULLs where no match on the right | | RIGHT JOIN | All rows from the right table, NULLs where no match on the left | | CROSS JOIN | Every combination of rows from both tables | | Self JOIN | A table joined to itself using aliases |