🔗 JoinsintermediateCommonly used

SQL JOIN types explained

Understand the difference between INNER, LEFT, RIGHT, FULL OUTER and CROSS JOIN.

INNER JOIN — matching rows onlyAll databases

SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- Returns: users WITH orders only

Only rows that match in BOTH tables. Non-matching rows from either table are excluded.

LEFT JOIN — all left + matching rightAll databases

SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- Returns: ALL users, NULL for orders where no match

All rows from the left table. NULL in right columns where there is no match.

RIGHT JOIN — matching left + all rightAll databases

SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
-- Returns: ALL orders, NULL for user where no match

All rows from the right table. Usually rewritten as a LEFT JOIN for clarity.

Most developers avoid RIGHT JOIN — swap the table order and use LEFT JOIN instead.

FULL OUTER JOIN — all rows from bothPostgreSQL

SELECT u.name, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;
-- Returns: all users + all orders, NULLs where no match

All rows from both tables. NULLs where there is no match on either side.

MySQL does not support FULL OUTER JOIN natively — simulate with UNION of LEFT and RIGHT JOINs.

CROSS JOIN — every combinationAll databases

SELECT u.name, p.name AS product
FROM users u
CROSS JOIN products p;
-- Returns: every user × every product

Returns the cartesian product — every row from the left combined with every row from the right.

When to use: Generating all combinations, like all users × all products for a recommendation matrix.

With 1000 users and 1000 products, CROSS JOIN returns 1,000,000 rows.

Common Mistakes

→Accidental CROSS JOIN — forgetting the ON clause in INNER/LEFT JOIN produces a cartesian product
→RIGHT JOIN is always avoidable — swap table order and use LEFT JOIN for clarity