Skip to content

Oracle SELF & CROSS JOIN 🔄

Mentor's Note: Sometimes the data you need isn't in another table—it's in the same table! And sometimes you need every possible combination of two lists. 💡

🌟 The Scenario: The Office Map 🏢

  1. SELF JOIN: You have a table of employees. Some employees are managers of other employees. To see "Employee -> Manager," you join the table to itself.
  2. CROSS JOIN: You have a list of Colors and Sizes. You need to generate every possible T-Shirt option (Red-Small, Red-Medium, etc.).

💻 1. SELF JOIN (The Mirror) 🪞

You must use Aliases to give the same table two different names in your query.

-- Scenario: Find every employee's manager name
SELECT 
    E.first_name AS "Worker", 
    M.first_name AS "Manager"
FROM employees E
JOIN employees M ON E.manager_id = M.employee_id;

💻 2. CROSS JOIN (The Multiplier) ✖️

Combines every row of the first table with every row of the second table.

SELECT C.color_name, S.size_name
FROM colors C
CROSS JOIN sizes S;

🏗️ Architect's Note: The Cartesian Explosion 🛡️

If Table A has 1,000 rows and Table B has 1,000 rows, a CROSS JOIN generates 1,000,000 rows. - Architect's Warning: Never use a CROSS JOIN by mistake (e.g., forgetting an ON clause in an old syntax query). It can lock up your database!

📈 Learning Path