CROSS JOIN & SELF JOIN

SQL includes some advanced join types that are useful 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 equals rows_in_students × rows_in_days, which can grow very quickly.

Use a CROSS JOIN 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. This is useful for comparing or relating rows within the same table.

Example: Employees and Managers

Here is an example of a SELF JOIN on the employees table.

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 use aliases (employees and managers) to distinguish the two roles of the same table.

Why use CROSS JOIN or SELF JOIN?

These joins are valuable in specialized cases:

• CROSS JOIN → generating all possible matchups, building schedules, or creating grids/test cases
• SELF JOIN → modeling hierarchical relationships, comparing rows within the same table, or linking employees to managers

Want to learn more?

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

Learn moreSign In