MySQL Joins INNER, LEFT, RIGHT, CROSS Join: Tutorial, Examples, FAQs & Interview Tips

What are Joins?

A JOIN combines rows from two or more tables based on a related column. The shop database has four related tables: customers, orders, order_items, and products. Joins let you query across all of them in a single statement.

INNER JOIN and LEFT JOIN

-- INNER JOIN: only rows with a match in BOTH tables
-- Returns customers who have placed at least one order
SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    o.order_id,
    o.total,
    o.status
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
ORDER BY c.customer_id;

-- LEFT JOIN: all rows from the LEFT table, matched rows from the right
-- Returns ALL customers, including those with no orders (NULL for order columns)
SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    COUNT(o.order_id)                       AS order_count,
    COALESCE(SUM(o.total), 0)               AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC;

RIGHT JOIN and FULL OUTER JOIN (via UNION)

-- RIGHT JOIN: all rows from the RIGHT table, matched rows from the left
-- Returns all orders, including those with no matching customer (unusual but possible)
SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    o.order_id,
    o.total
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

-- FULL OUTER JOIN: MySQL doesn't support FULL OUTER JOIN directly
-- Simulate it with LEFT JOIN UNION RIGHT JOIN
SELECT c.customer_id, c.first_name, o.order_id, o.total
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id

UNION

SELECT c.customer_id, c.first_name, o.order_id, o.total
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

CROSS JOIN and SELF JOIN

-- CROSS JOIN: every tl-row from tl-table A combined with every tl-row from tl-table B
-- (Cartesian product - use carefully, can produce huge result sets)
SELECT c.first_name, p.name AS product_name
FROM customers c
CROSS JOIN products p
LIMIT 20;

-- SELF JOIN: join a tl-table to itself
-- Example: find customers who share the same last name
SELECT
    a.customer_id AS id1,
    a.first_name  AS name1,
    b.customer_id AS id2,
    b.first_name  AS name2,
    a.last_name
FROM customers a
INNER JOIN customers b
    ON a.last_name = b.last_name
    AND a.customer_id < b.customer_id
ORDER BY a.last_name;

Multiple tl-table Joins

You can chain multiple JOINs to query across all four tables in the shop database in a single statement.

Joining customers, orders, order_items, and products

-- Full order detail: customer + order + items + product names
SELECT
    c.customer_id,
    CONCAT(c.first_name, ' ', c.last_name) AS customer_name,
    o.order_id,
    o.status,
    o.ordered_at,
    p.name                                 AS product_name,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price)          AS line_total
FROM customers c
INNER JOIN orders      o  ON c.customer_id  = o.customer_id
INNER JOIN order_items oi ON o.order_id     = oi.order_id
INNER JOIN products    p  ON oi.product_id  = p.product_id
WHERE o.status IN ('shipped', 'delivered')
ORDER BY o.ordered_at DESC, o.order_id, oi.item_id;

-- Find customers who have never placed an order (LEFT JOIN + NULL check)
SELECT c.customer_id, c.first_name, c.last_name, c.email
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

JOIN with WHERE vs ON

-- Filtering in ON vs WHERE behaves differently with LEFT JOIN

-- Filter in WHERE: excludes non-matching rows (effectively becomes INNER JOIN)
SELECT c.first_name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.status = 'delivered';   -- customers with no orders are excluded

-- Filter in ON: keeps all left-table rows, filters only the joined rows
SELECT c.first_name, o.order_id, o.status
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id
    AND o.status = 'delivered';  -- customers with no delivered orders still appear (NULL)

Previous Next

Key Takeaways

INNER JOIN returns only rows where there is a match in BOTH tables.
LEFT JOIN returns ALL rows from the left tl-table + matching rows from the right (NULL for no match).
RIGHT JOIN returns ALL rows from the right tl-table + matching rows from the left (NULL for no match).
FULL OUTER JOIN returns all rows from both tables (MySQL uses UNION of LEFT and RIGHT JOIN).
CROSS JOIN returns the Cartesian product - every tl-row from tl-table A combined with every tl-row from tl-table B.
Always use tl-table aliases (a, b) in multi-table joins for cleaner, faster queries.
Index the JOIN columns (foreign keys) for dramatically better performance on large tables.

Common Mistakes to Avoid

WRONG SELECT * FROM orders, customers WHERE orders.cid = customers.id

RIGHT SELECT * FROM orders INNER JOIN customers ON orders.cid = customers.id

Implicit joins (comma syntax) are harder to read and maintain. Always use explicit JOIN syntax.

WRONG LEFT JOIN without checking for NULLs

RIGHT WHERE right_table.id IS NULL to find unmatched rows

LEFT JOIN + WHERE right.id IS NULL is the standard way to find rows in tl-table A that have no match in tl-table B.

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 tl-table and matching rows from the right - unmatched right rows appear as NULL.

Can I join more than two tables in MySQL?

Yes. Chain multiple JOINs: SELECT * FROM a JOIN b ON a.id=b.aid JOIN c ON b.id=c.bid. Each JOIN adds another table. Index all join columns for performance.

What is a self join?

A self join joins a tl-table to itself using aliases. Useful for hierarchical data: SELECT e.name, m.name AS manager FROM employees e JOIN employees m ON e.manager_id = m.id

How do I find rows in one tl-table that do not exist in another?

Use LEFT JOIN + IS NULL: SELECT a.* FROM a LEFT JOIN b ON a.id = b.a_id WHERE b.a_id IS NULL. This is more efficient than NOT IN with subqueries.

MySQL Joins INNER, LEFT, RIGHT, CROSS Join: Tutorial, Examples, FAQs & Interview Tips

What are Joins?

Multiple tl-table Joins

Level Up Your My sql Skills

Frequently Asked Questions

Popular Tutorials

Ready to Level Up Your Skills?