SQL - Self & Cross Join
Overview
SELF JOIN joins a table to itself. CROSS JOIN returns every combination of rows (cartesian product).
-- employees in same department (excluding self)
SELECT a.first_name AS emp, b.first_name AS teammate
FROM employees a
JOIN employees b ON a.department_id=b.department_id AND a.id<>b.id;
-- small cross product
SELECT p.name, c.name
FROM products p CROSS JOIN customers c;-- self join
SELECT a.first_name AS emp, b.first_name AS teammate
FROM employees a JOIN employees b ON a.department_id=b.department_id AND a.id<>b.id;
-- cross join
SELECT p.name, c.name FROM products p CROSS JOIN customers c;-- self join
SELECT a.first_name AS emp, b.first_name AS teammate
FROM employees a JOIN employees b ON a.department_id=b.department_id AND a.id<>b.id;
-- cross join
SELECT p.name, c.name FROM products p CROSS JOIN customers c;Prerequisite
Load the Standard Test Data.
Expected Output (live)
Self Join (same department)
| emp | teammate |
|---|---|
| Ada | Bob |
| Ada | Eli |
| Bob | Ada |
| Bob | Eli |
| Eli | Ada |
| Eli | Bob |
Cross Join (first 10 combinations)
| product | customer |
|---|---|
| Laptop Pro | Alice |
| Laptop Pro | Ben |
| Laptop Pro | Cara |
| Laptop Pro | Dan |
| Laptop Pro | Eva |
| Wireless Mouse | Alice |
| Wireless Mouse | Ben |
| Wireless Mouse | Cara |
| Wireless Mouse | Dan |
| Wireless Mouse | Eva |