Master MySQL Joins: From Inner to Full Outer – Practical Examples & Tips

Introduction

Relational databases such as MySQL store data in separate tables and let you combine them with join operations. Joins are far more efficient than running multiple queries and manually merging results.

Basic Example: Creating Sample Tables

We create two tables: categories (id, name) and items (id, name, category_id). The category_id column in items references categories.id.

CREATE TABLE categories (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(250) NOT NULL
);

CREATE TABLE items (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(512),
    category_id INT NULL
);

Sample data:

-- categories
id | name
---+--------
1  | Produce
2  | Deli

-- items
id | name   | category_id
---+--------+------------
1  | Apples | 1
2  | Cheese | 2

Inner Join (INNER JOIN)

To display each product with its category name, use an inner join that returns only rows where the join condition matches.

SELECT *
FROM items
JOIN categories ON items.category_id = categories.id;

Common mistake: omitting the ON clause causes a CROSS JOIN , producing a Cartesian product (m × n rows) and often severe performance issues.

Correct Inner Join Example

SELECT *
FROM items
JOIN categories ON items.category_id = categories.id; -- explicit ON condition

Aliasing Columns

Both tables have a name column, which can cause ambiguity. Use column aliases to give each a unique name.

SELECT i.id, i.name, i.category_id, c.name AS category_name
FROM items AS i
JOIN categories AS c ON i.category_id = c.id;

Left Join (LEFT JOIN) and Right Join (RIGHT JOIN)

We add a product without a category ("Bread") and a new category without products ("Dairy").

-- updated categories
id | name
---+--------
1  | Produce
2  | Deli
3  | Dairy

-- updated items
id | name   | category_id
---+--------+------------
1  | Apples | 1
2  | Cheese | 2
3  | Bread  | NULL

Left Join

Returns all rows from the left table ( items) and matches from the right table ( categories) when available.

SELECT i.id, i.name, i.category_id, c.name AS category_name
FROM items i
LEFT JOIN categories c ON i.category_id = c.id;

Right Join

Returns all rows from the right table ( categories) and matches from the left table when they exist.

SELECT i.id, i.name, i.category_id, c.name AS category_name
FROM items i
RIGHT JOIN categories c ON i.category_id = c.id;

Full Outer Join (Simulated)

MySQL does not support FULL OUTER JOIN directly. Simulate it by combining a left join and a right join with UNION ALL.

SELECT i.id, i.name, i.category_id, c.name AS category_name
FROM items i
LEFT JOIN categories c ON i.category_id = c.id
UNION ALL
SELECT i.id, i.name, i.category_id, c.name AS category_name
FROM items i
RIGHT JOIN categories c ON i.category_id = c.id
WHERE i.id IS NULL; -- keep only unmatched category rows

Filtering Unassociated Data

Find items without a category:

SELECT i.id, i.name, i.category_id, c.name AS category_name
FROM items i
LEFT JOIN categories c ON i.category_id = c.id
WHERE c.id IS NULL;

Find categories without items:

SELECT i.id, i.name, i.category_id, c.name AS category_name
FROM items i
RIGHT JOIN categories c ON i.category_id = c.id
WHERE i.id IS NULL;

Only Unassociated Records (Full Outer + WHERE)

SELECT *
FROM (
    SELECT i.id, i.name, i.category_id, c.name AS category_name
    FROM items i
    LEFT JOIN categories c ON i.category_id = c.id
    UNION ALL
    SELECT i.id, i.name, i.category_id, c.name AS category_name
    FROM items i
    RIGHT JOIN categories c ON i.category_id = c.id
    WHERE i.id IS NULL
) AS all_items_all_categories
WHERE id IS NULL OR category_id IS NULL;

Summary

INNER JOIN

(or plain JOIN) returns only rows with matching keys. LEFT JOIN returns all rows from the left table and fills missing right‑side columns with NULL. RIGHT JOIN returns all rows from the right table and fills missing left‑side columns with NULL. FULL OUTER JOIN returns all rows from both tables; in MySQL it can be simulated with LEFT JOIN + RIGHT JOIN + UNION.

The WHERE … IS NULL clause can filter the result set to show only unassociated records.