iwantcoding.com
🔥 Daily 👥 Rooms 🏆 Top Log in Sign up

JOINs

By · Reviewed & last updated · Part of MySQL Tutorial

JOINs in MySQL come in four useful flavours: INNER, LEFT, RIGHT, and CROSS. Note: FULL OUTER is not directly supported — emulate it with UNION.

INNER / LEFT / anti-join / FULL OUTER emulation

EXAMPLE
-- 1) INNER JOIN — rows where both sides match
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';

-- 2) LEFT JOIN — keep all users
SELECT u.name, COALESCE(SUM(o.total), 0) AS revenue
FROM users u
LEFT JOIN orders o ON o.user_id = u.id AND o.status = 'paid'
GROUP BY u.id, u.name;

-- 3) USING (when column names match)
SELECT u.name, o.total
FROM users u
JOIN orders o USING (user_id);

-- 4) Self join
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

-- 5) Anti-join — never ordered
SELECT u.name
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;

-- Or NOT EXISTS
SELECT u.name
FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

-- 6) FULL OUTER emulation
SELECT a.id AS a_id, b.id AS b_id FROM a LEFT JOIN b ON a.k = b.k
UNION
SELECT a.id AS a_id, b.id AS b_id FROM a RIGHT JOIN b ON a.k = b.k;

-- 7) STRAIGHT_JOIN — force join order (rare; only when planner gets it wrong)
SELECT STRAIGHT_JOIN u.name, o.total FROM users u, orders o WHERE u.id = o.user_id;

-- 8) Top-3 orders per user (window function, MySQL 8+)
SELECT * FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) rn
    FROM orders o
) t
WHERE rn <= 3;

-- 9) Latest per user (correlated subquery pattern)
SELECT o.*
FROM orders o
WHERE o.created_at = (
    SELECT MAX(created_at) FROM orders WHERE user_id = o.user_id
);

-- 10) JSON join — extract from JSON columns
SELECT u.name, JSON_EXTRACT(u.profile, '$.country')
FROM users u
WHERE JSON_EXTRACT(u.profile, '$.verified') = true;

-- 11) INDEXED JOIN columns matter most
CREATE INDEX idx_orders_user ON orders(user_id);
CREATE INDEX idx_orders_user_created ON orders(user_id, created_at DESC);

EXPLAIN SELECT u.name, COUNT(o.id) FROM users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id;

-- 12) AVOID the implicit comma join — easy to forget the WHERE
SELECT * FROM users, orders WHERE users.id = orders.user_id;     -- works but error-prone
SELECT * FROM users JOIN orders ON users.id = orders.user_id;    -- preferred

Why it matters

MySQL doesn’t have FULL OUTER JOIN — emulate it with LEFT JOIN ... UNION ... RIGHT JOIN. Most queries don’t need it; if you do, write a comment explaining why.

Tip: Tweak the snippet with Try it Yourself », then sit the quiz at the bottom of the page.

Example

Example
SELECT u.name, p.title
FROM users u
JOIN posts p ON p.user_id = u.id
LEFT JOIN comments c ON c.post_id = p.id
WHERE u.id = 1;
Try it Yourself »

Exercise

Keep all left rows even when no match.

SELECT * FROM a JOIN b ON a.id = b.a_id;

Discussion

Loading…