Joins in MySQL — Combining Data from Multiple Tables

Relational databases store data across multiple tables. Joins let you bring that data back together when you query it. This tutorial covers every type of join you can use in MySQL, when to use each, and how to write them efficiently.

Table of Contents

• The Problem Joins Solve
• Setting Up Sample Data
• INNER JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN
• CROSS JOIN
• SELF JOIN
• Joining Multiple Tables
• Join Optimization
• Quick Reference

The Problem Joins Solve

In a normalized database, related data lives in separate tables. Customers are in one table, orders in another. To see a customer's name next to their order, you need a join — it matches rows from two tables based on a related column (usually a foreign key).

Setting Up Sample Data

All examples use these two tables. Run this to set up the data in MySQL:

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    city VARCHAR(50)
) ENGINE=InnoDB;

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT,
    product VARCHAR(50),
    amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES customers(id)
) ENGINE=InnoDB;

INSERT INTO customers (name, city) VALUES
('Alice', 'New York'),
('Bob', 'London'),
('Charlie', 'Paris'),
('Diana', 'Berlin');

INSERT INTO orders (product, amount, customer_id) VALUES
('Laptop', 1200, 1),
('Mouse', 25, 1),
('Keyboard', 80, 2),
('Monitor', 300, 4),
('USB Hub', 40, 4);

Notice:

• Alice has two orders.
• Bob has one order.
• Diana has two orders.
• Charlie has zero orders.

INNER JOIN

Returns only rows where there is a match in both tables. If a row in either table has no match, it is excluded.

Use this when you only want records that exist on both sides — e.g., "show me customers who have placed orders and the orders they placed."

Syntax

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

INNER JOIN and JOIN mean the same thing. JOIN defaults to INNER JOIN.

Example

SELECT customers.name, orders.product, orders.amount
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

Result:

Charlie has no orders → no row in the result. An order with no matching customer → also excluded (there are none in our data).

LEFT JOIN

Returns all rows from the left table, and matching rows from the right table. If there is no match in the right table, the result contains NULL for right-side columns.

Use this when you want everything from the primary table, optionally showing related data — e.g., "list all customers and their orders, even if they haven't ordered anything."

Syntax

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

LEFT JOIN and LEFT OUTER JOIN mean the same thing.

Example

SELECT customers.name, orders.product, orders.amount
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

Result:

Charlie appears with NULL values because he has no matching orders.

Common Use Case: Finding Missing Records

LEFT JOIN makes it easy to find rows in one table that have no match in another.

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

Result:

This finds customers who have never placed an order: LEFT JOIN + WHERE right_table.key IS NULL.

RIGHT JOIN

Returns all rows from the right table, and matching rows from the left table. It is the mirror of LEFT JOIN.

In practice, RIGHT JOIN is rarely used. Most developers prefer LEFT JOIN because it reads more naturally — you read top-to-bottom, left-to-right. You can always rewrite a RIGHT JOIN as a LEFT JOIN by swapping the table order.

Syntax

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

Example

SELECT customers.name, orders.product, orders.amount
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id;

Result:

Same result as the INNER JOIN earlier — because every order has a matching customer. RIGHT JOIN only becomes different from INNER JOIN when the right table contains rows with no match in the left.

Same Thing as LEFT JOIN

-- These two queries produce the same result:
SELECT * FROM orders
RIGHT JOIN customers ON orders.customer_id = customers.id;

SELECT * FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

Recommendation: always use LEFT JOIN. It's more readable and less likely to confuse someone reading your code later.

FULL OUTER JOIN

Returns all rows from both tables, matched where possible. Rows without a match on either side get NULL values for the missing side.

MySQL does not support FULL OUTER JOIN syntax. You simulate it by combining LEFT JOIN and RIGHT JOIN with UNION.

Use this when you need the complete picture from both tables — e.g., "show me all customers and all orders, including orphans on either side."

MySQL Workaround

SELECT customers.name, orders.product, orders.amount
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
UNION
SELECT customers.name, orders.product, orders.amount
FROM customers
RIGHT JOIN orders ON customers.id = orders.customer_id;

Result:

This result looks the same as LEFT JOIN here because every order has a matching customer. It would differ if there were orphaned orders (orders with a customer_id that doesn't exist in customers).

UNION removes duplicate rows automatically. If you want to keep duplicates for some reason, use UNION ALL instead.

CROSS JOIN

Returns the Cartesian product — every row from table A paired with every row from table B. No ON clause needed.

If table A has 100 rows and table B has 50 rows, the result has 5,000 rows. Use this carefully.

Useful for generating combinations: size × color, product × warehouse, date × shift.

Syntax

SELECT columns
FROM table_a
CROSS JOIN table_b;

Example

SELECT customers.name AS customer, orders.product
FROM customers
CROSS JOIN orders;

Result (truncated — 4 customers × 5 orders = 20 rows):

Every customer is paired with every product — even if they never ordered it. This is different from INNER JOIN, which only pairs customers with products they actually ordered.

Implicit Cross Join

SELECT customers.name, orders.product
FROM customers, orders; -- comma = CROSS JOIN

This old-style syntax works but is easy to confuse with INNER JOIN when a WHERE clause is added. Explicit CROSS JOIN is clearer.

SELF JOIN

A join where a table is joined with itself. You must use table aliases to avoid ambiguity.

Use this when a table contains a reference to another row in the same table — e.g., employees with a manager_id that points to another employee, categories with a parent_id, or threaded comments.

Sample Table: employees

Run this to set up the employees table:

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    manager_id INT
) ENGINE=InnoDB;

INSERT INTO employees (name, manager_id) VALUES
('Carol', NULL),
('Dave', 1),
('Eve', 1),
('Frank', 2);

Carol (id=1) is the CEO. Dave and Eve report to Carol. Frank reports to Dave.

Example: Employee & Their Manager

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

Result:

The same table appears twice: once as e (employees) and once as m (managers). The join condition e.manager_id = m.id links each employee to their manager's row.

Example: All Employees (Including CEO) With Their Manager

Use LEFT JOIN so Carol (who has no manager) still appears.

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

Result:

Joining Multiple Tables

You are not limited to two tables. You can chain joins to pull data from three, four, or more tables in a single query.

Each new join uses the result of the previous join as its left side. The most common pattern is a star join: one central fact table (e.g., orders) joined to multiple dimension tables (e.g., customers, products).

Sample Tables

We add a products table and an order_items table to our existing schema. The original orders table stays unchanged.

Run this to add the new tables:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    category VARCHAR(50),
    price DECIMAL(10,2)
) ENGINE=InnoDB;

INSERT INTO products (name, category, price) VALUES
('Laptop', 'Electronics', 1200),
('Mouse', 'Accessories', 25),
('Keyboard', 'Accessories', 80),
('Monitor', 'Electronics', 300),
('USB Hub', 'Accessories', 40);

CREATE TABLE order_items (
    id INT AUTO_INCREMENT PRIMARY KEY,
    order_id INT,
    product_id INT,
    quantity INT,
    FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;

INSERT INTO order_items (order_id, product_id, quantity) VALUES
(101, 1, 1),
(102, 2, 2),
(103, 3, 1),
(104, 4, 2),
(105, 5, 1);

products

order_items

Example: Three Tables Joined

Get order details with customer name and product category.

SELECT
    customers.name AS customer,
    products.name AS product,
    products.category,
    order_items.quantity
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
INNER JOIN order_items ON orders.id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.id;

Result:

The query starts with orders, adds customers via customer_id, then links through order_items to get products.

Example: Four Tables (Add Categories)

If you had a separate categories table, the chain gets longer:

SELECT
    customers.name,
    products.name,
    categories.name AS category
FROM orders
INNER JOIN customers ON orders.customer_id = customers.id
INNER JOIN order_items ON orders.id = order_items.order_id
INNER JOIN products ON order_items.product_id = products.id
INNER JOIN categories ON products.category_id = categories.id;

Each join follows the same pattern: pick the foreign key and match it to the target table's primary key.

Mixing Join Types

You can mix different join types in one query. For example, keep all customers even if they have no orders, but only show products that exist:

SELECT customers.name, products.name, order_items.quantity
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
LEFT JOIN order_items ON orders.id = order_items.order_id
LEFT JOIN products ON order_items.product_id = products.id;

Charlie still appears with NULLs, and any order referencing a missing product would also show NULLs for product info.

Join Optimization

As your tables grow, poorly written joins become slow. Here is what matters in MySQL.

1. Index Foreign Key Columns

Joins match rows using the ON condition. Without an index, MySQL does a full table scan for every matching row.

CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_product_id ON orders(product_id);

Index the column on the "many" side (the table you are joining in). MySQL uses indexes to look up matching rows quickly instead of scanning the whole table.

2. Only Fetch the Columns You Need

-- Slow: fetches all columns before joining
SELECT * FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

-- Faster: only the columns you actually use
SELECT customers.name, orders.amount FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

SELECT * forces MySQL to read and carry every column through the join pipeline. Be specific.

3. Filter Before Joining When Possible

If you only need records from a specific date range, filter that table before the join.

-- Better: filter orders before joining
SELECT customers.name, recent.product
FROM customers
INNER JOIN (
    SELECT * FROM orders WHERE order_date >= '2025-01-01'
) recent ON customers.id = recent.customer_id;

MySQL's optimizer often pushes WHERE conditions down into joins automatically, but being explicit never hurts.

4. Use STRAIGHT_JOIN to Force Join Order

MySQL's optimizer usually picks the right join order. When it doesn't, use STRAIGHT_JOIN to force the order you wrote:

-- Force MySQL to read customers first, then orders
SELECT customers.name, orders.product
FROM customers
STRAIGHT_JOIN orders ON customers.id = orders.customer_id;

STRAIGHT_JOIN is MySQL-specific. Use it sparingly — only after confirming the optimizer chose a bad plan via EXPLAIN.

5. Avoid Joins on Expressions or Functions

-- Slow: can't use index on customers.id
SELECT * FROM orders
INNER JOIN customers ON UPPER(customers.name) = orders.customer_name;

-- Fast: direct column comparison can use index
SELECT * FROM orders
INNER JOIN customers ON customers.id = orders.customer_id;

A function wrapper like UPPER(), DATE(), or CONCAT() on the joined column prevents index usage. Store the data in the format you need to compare.

6. Use EXPLAIN to Find Bottlenecks

EXPLAIN SELECT customers.name, orders.amount
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

MySQL's EXPLAIN output shows:

• type — how MySQL joins the tables (ALL = table scan, ref = index lookup, eq_ref = primary key lookup)
• key — which index is being used (NULL means no index)
• rows — estimated number of rows examined per table
• Extra — additional info like Using where, Using temporary, Using filesort

EXPLAIN FORMAT=JSON SELECT ...; -- more detailed output

If you see type: ALL on a large table, you need an index on the joined column. Run EXPLAIN ANALYZE in MySQL 8.0.18+ to get actual execution times.

7. MySQL Hash Joins (MySQL 8.0.18+)

MySQL 8.0.18 and later use hash joins automatically for equi-joins (joins using =) between large tables with no usable index. This is significantly faster than the old nested-loop approach for big unindexed joins.

You don't need to do anything special — the optimizer chooses a hash join when it estimates it will be faster. If you want to disable it temporarily for testing:

SET optimizer_switch = 'hash_join=off';

8. Be Careful With LEFT JOIN and WHERE

A common MySQL mistake: using LEFT JOIN but then filtering on the right table in WHERE, which silently converts it to an INNER JOIN.

-- Looks like LEFT JOIN, but WHERE turns it into INNER JOIN
SELECT customers.name, orders.product
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
WHERE orders.amount > 50;

The WHERE orders.amount > 50 clause removes Charlie (who has NULL for orders.amount), and also removes Alice's Mouse order (amount 25). To keep the LEFT JOIN behavior, move the filter to the join condition:

SELECT customers.name, orders.product
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id
    AND orders.amount > 50;

Now Charlie stays in the result (with NULLs), and only matching orders above 50 appear.