SQL SELF & CROSS JOIN 🔄¶

Prerequisites: INNER JOIN, Aliases

Mentor's Note: Sometimes the relationship isn't between two different things (like Customers and Orders), but within the same thing (like Employees and Managers). That's a SELF JOIN. And sometimes you want every possible combination (like Colors x Sizes). That's a CROSS JOIN. 💡

🌟 The Scenario: The Office Hierarchy 🏢¶

Imagine an Employee table. - Arjun reports to Priya. - Priya reports to Boss. - They are ALL in the same "Employees" table. - SELF JOIN: "Connect Arjun's 'Manager ID' to Priya's 'Employee ID' within the same list."

💻 1. SELF JOIN (The Mirror) 🪞¶

You treat the single table as if it were two separate tables by using Aliases.

SELECT 
    E.name AS "Employee", 
    M.name AS "Manager"
FROM employees E        -- 1st Copy: Represents Employees
JOIN employees M        -- 2nd Copy: Represents Managers
ON E.manager_id = M.id;

💻 2. CROSS JOIN (The Multiplier) ✖️¶

Returns the Cartesian Product. Every row from Table A is paired with Every row from Table B.

-- Scenario: Create a matrix of T-Shirt options
SELECT colors.color_name, sizes.size_name
FROM colors
CROSS JOIN sizes;

Result: - Red, Small - Red, Medium - Red, Large - Blue, Small - Blue, Medium...

⚠️ Warning: The Explosion 💥¶

If Table A has 1,000 rows and Table B has 1,000 rows, a CROSS JOIN will generate 1 Million rows (1000 x 1000). Use with caution!

🎨 Visual Logic: The Hierarchy¶

graph TD
    A[Employee Table] --> B{SELF JOIN}
    B -- "Alias E" --> C[Worker]
    B -- "Alias M" --> D[Boss]
    C -- "Reports To" --> D