SQL RIGHT, FULL and CROSS JOIN

RIGHT JOIN

RIGHT JOIN is the mirror image of LEFT JOIN: keep every row from the right table. Most people rewrite it as a LEFT JOIN with the tables swapped, which reads more naturally.

FULL OUTER JOIN

Keeps all rows from both sides, with NULLs where there's no match — useful for comparing two lists:

SELECT c.name AS category, p.name AS product
FROM categories c
FULL OUTER JOIN products p ON p.category_id = c.category_id
WHERE p.product_id IS NULL OR c.category_id IS NULL OR p.product_id IN (1, 4)
ORDER BY category, product;

category | product
NULL | Robot Kit
Books | SQL for Beginners
Electronics | Mechanical Keyboard

MySQL has no FULL JOIN; emulate it with a LEFT JOIN UNION a RIGHT JOIN.

CROSS JOIN

Pairs every row of one table with every row of the other — e.g. every category with every country for a report grid:

SELECT c.name AS category, x.country
FROM categories c
CROSS JOIN (SELECT DISTINCT country FROM customers WHERE country IN ('UK', 'Japan')) AS x
ORDER BY c.name, x.country;

category | country
Books | Japan
Books | UK
Electronics | Japan
Electronics | UK
Home | Japan
Home | UK
Toys | Japan
Toys | UK