CROSS JOIN & SELF JOIN

SQL includes some advanced join types that are powerful in specific scenarios:

• CROSS JOIN returns every possible pair of rows between two tables
• SELF JOIN joins a table to itself to compare rows within it

CROSS JOIN

A CROSS JOIN returns the Cartesian product of two tables, where every row in the first table is combined with every row in the second.

CROSS JOIN

SELECT students.name, days.day
FROM students
CROSS JOIN days;

Be cautious: the number of rows in the result equals rows_in_students × rows_in_days. It grows fast!

Use this when:

• Generating all possible pairs (e.g., schedules, combinations)
• Running pairwise comparisons
• Creating grids or test cases

SELF JOIN

A SELF JOIN occurs when a table is joined with itself, which is useful for comparing or relating rows within the same table.

Example: Employees and Managers

Here is an example of a SELF JOIN between the employees table and itself.

employees

id	name	manager_id
1	Alice	NULL
2	Bob	1
3	Carol	1
4	Dave	2

To find each employee's manager, use a SELF JOIN:

SELF JOIN to find managers

SELECT employees.name AS employee, managers.name AS manager
FROM employees
LEFT JOIN employees AS managers
ON employees.manager_id = managers.id;

Result:

employee	manager
Alice	NULL
Bob	Alice
Carol	Alice
Dave	Bob

We alias the same table as employees and managers to distinguish their roles.

Why These Joins Matter

Use these joins when:

• Building schedules or matchups (CROSS JOIN)
• Modeling hierarchical relationships (SELF JOIN)
• Comparing rows within a table

Want to learn more?

Join CodeFriends Plus membership or enroll in a course to start your journey.

Learn moreSign In