SQL CROSS JOIN & SELF JOIN: Cartesian Products & Hierarchy Trees

Two specialized join variations solve distinct architectural challenges: CROSS JOIN creates Cartesian matrix combinations, while SELF JOIN models hierarchical relationships (like manager-employee organizational structures) within a single self-referencing table.

1. SQL CROSS JOIN (Cartesian Product)

A CROSS JOIN produces every possible pairing between rows of Table A and Table B. If you have 3 shirt sizes and 4 colors, a CROSS JOIN generates all (3 \times 4 = 12) product inventory combinations:

-- Generate all size and color product variations
SELECT s.size_name, c.color_name
FROM sizes s
CROSS JOIN colors c;

2. SQL SELF JOIN (Hierarchical Tree Queries)

In many tables, rows reference other rows in the same table. Consider an employees table where every employee has a manager_id pointing to another emp_id in the same table:

CREATE TABLE staff (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    manager_id INT
);

INSERT INTO staff VALUES
(1, 'Sandeep (Director)', NULL),
(2, 'Neha (Manager)', 1),
(3, 'Aman (Developer)', 2);

-- SELF JOIN: Query employee name alongside their manager's name
SELECT 
    e.emp_name AS employee,
    COALESCE(m.emp_name, 'Top Executive (No Manager)') AS manager
FROM staff e
LEFT JOIN staff m ON e.manager_id = m.emp_id;

Next, let us learn how to combine query result sets using SQL UNION & UNION ALL!