LEFT JOIN and RIGHT JOIN
LEFT JOIN
Returns all rows from the left table, NULLs for unmatched right rows.
-- All users, even those with no orders
SELECT u.username, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id;RIGHT JOIN
Returns all rows from the right table, NULLs for unmatched left rows.
SELECT p.name, c.name AS category
FROM products p
RIGHT JOIN categories c ON p.category_id = c.id;Find Unmatched (Anti-join)
-- Users who never placed an order
SELECT u.username
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE o.id IS NULL;