Hiprup

What are the different types of JOINs in MySQL?

JOINs combine rows from two tables based on a related column. The choice of JOIN decides which rows survive when there's no match.

  • INNER JOIN — only rows with a match in both tables. The default and most common.

  • LEFT JOIN — all rows from the left table; matched columns from the right, NULL where no match.

  • RIGHT JOIN — mirror of LEFT JOIN; rare since you can flip table order.

  • FULL OUTER JOIN — all rows from both sides; not supported in MySQL — emulate with UNION of LEFT and RIGHT.

  • CROSS JOIN — cartesian product; every row of A paired with every row of B.

  • SELF JOIN — joining a table to itself with aliases; for hierarchies and pair comparisons.

-- INNER JOIN: only matching rows
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- Users without orders are EXCLUDED

-- LEFT JOIN: all users, even without orders
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- Users without orders show NULL for o.total

-- Find users WITHOUT orders
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- SELF JOIN: employees and their managers
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Simulate FULL OUTER JOIN in MySQL
SELECT * FROM table_a LEFT JOIN table_b ON a.id = b.id
UNION
SELECT * FROM table_a RIGHT JOIN table_b ON a.id = b.id;

-- CROSS JOIN (Cartesian product)
SELECT colors.name, sizes.name
FROM colors CROSS JOIN sizes;
-- 3 colors × 4 sizes = 12 rows

INNER JOIN returns only matching rows (users WITH orders). LEFT JOIN returns all left-table rows (users without orders show NULL). WHERE o.id IS NULL finds unmatched rows (users with no orders).

SELF JOIN uses two aliases (e, m) for the same table. FULL OUTER is simulated with UNION of LEFT + RIGHT. CROSS JOIN produces all combinations.

Know all 6 types: INNER, LEFT, RIGHT, FULL OUTER (simulated), CROSS, SELF. The LEFT JOIN + WHERE IS NULL pattern for finding unmatched rows is very commonly asked.

SELF JOIN for employee/manager hierarchy. MySQL has no native FULL OUTER JOIN — simulate with UNION.

What are the different types of JOINs in MySQL? | Hiprup