These two JOINs are rare in daily QA work. But they come up in interviews constantly. And when you do need them, nothing else will do the job.
CROSS JOIN produces the Cartesian product — every row from the left table paired with every row from the right table. If table A has 10 rows and table B has 5 rows, you get 50 rows. No ON clause needed. No matching condition. Just pure multiplication.
-- Generate all possible size + color combinations
SELECT s.size_name, c.color_name
FROM sizes s
CROSS JOIN colors c;
-- sizes: S, M, L (3 rows)
-- colors: Red, Blue, Green (3 rows)
-- Result: 9 rows (3 x 3)
-- S-Red, S-Blue, S-Green, M-Red, M-Blue, M-Green...
-- QA use case: Generate test combinations
-- Test every payment method with every currency
SELECT pm.method_name, cur.currency_code
FROM payment_methods pm
CROSS JOIN currencies cur;
-- This gives you the complete test matrixCROSS JOIN on two large tables will destroy your database performance. 1000 rows x 1000 rows = 1,000,000 result rows. Always use it on small reference tables only. Never accidentally write one by forgetting the ON clause in a regular JOIN.
A self-join is when you JOIN a table to itself. The classic example: an employees table where each employee has a manager_id that references another employee in the same table. To show "Amit reports to Priya," you need to join employees to employees.
-- employees table:
-- id | name | manager_id
-- 1 | Priya | NULL (CEO, no manager)
-- 2 | Amit | 1 (reports to Priya)
-- 3 | Sneha | 1 (reports to Priya)
-- 4 | Rahul | 2 (reports to Amit)
-- Show each employee with their manager's name
SELECT
e.name AS employee,
m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- Result:
-- Priya | NULL (no manager)
-- Amit | Priya
-- Sneha | Priya
-- Rahul | Amit
-- Find employees who manage nobody (leaf nodes)
SELECT e.name
FROM employees e
LEFT JOIN employees sub ON e.id = sub.manager_id
WHERE sub.id IS NULL;
-- Find potential duplicate customers (same email, different IDs)
SELECT
a.customer_id AS id_1,
b.customer_id AS id_2,
a.email
FROM customers a
INNER JOIN customers b ON a.email = b.email AND a.customer_id < b.customer_id;In the duplicate detection query, the condition a.customer_id < b.customer_id prevents showing the same pair twice. Without it, you get (1,2) and (2,1) which is the same duplicate reported twice.
Q: Give a real-world example of a self-join.
A: Employee-manager hierarchy is the classic one. The employees table has a manager_id column that references another employee's id. A self-join with LEFT JOIN employees m ON e.manager_id = m.id lets you show each employee alongside their manager's name. Another common example: finding duplicate records in a table by joining it to itself on the duplicate column but with different primary keys.
Key Point: CROSS JOIN generates all possible combinations (Cartesian product). Self-join connects a table to itself — used for hierarchies and duplicate detection.