A MySQL join combines rows according to an explicit relationship. Join predicates, join type, NULL behavior, cardinality, and indexes determine both correctness and cost.
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: 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: 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: every row from table A combined with every row from 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 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;You can chain multiple JOINs to query across all four tables in the shop database in a single statement.
-- 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;-- 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)SELECT 'MySQL Joins INNER LEFT RIGHT and CROSS Join Behavior' AS lesson, COUNT(*) AS rows_checked
FROM sample_table;SELECT *
FROM sample_table
WHERE important_value IS NULL;INNER JOIN returns only rows where there is a match in both tables. LEFT JOIN returns all rows from the left table and matching rows from the right - unmatched right rows appear as NULL.
Yes. Chain multiple JOINs: <code>SELECT * FROM a JOIN b ON a.id=b.aid JOIN c ON b.id=c.bid</code>. Each JOIN adds another table. Index all join columns for performance.
A self join joins a table to itself using aliases. Useful for hierarchical data: <code>SELECT e.name, m.name AS manager FROM employees e JOIN employees m ON e.manager_id = m.id</code>
Explore 500+ free tutorials across 20+ languages and frameworks.