Self Joins & Cross Joins

While most JOINs combine data from different tables, sometimes you need to join a table to itself — or combine all possible pairs of rows. That’s where self joins and cross joins come in.

Self Join

A self join is when a table is joined to itself. This is useful for comparing rows within the same table.

For example, to find pairs of students who are the same age:

SELECT a.name, b.name 
FROM students a
JOIN students b ON a.age = b.age AND a.id < b.id;

This compares each student to others in the same table, and avoids duplicates by only including pairs where a.id < b.id.

Cross Join

A cross join returns the Cartesian product of two tables — every row from the first table paired with every row from the second.

Example:

SELECT * FROM students CROSS JOIN courses;

If there are 5 students and 3 courses, this returns 15 rows — one for each combination.

Cross joins can be useful for generating all possible pairings, though they can produce large results quickly.

Use the Tables tab to explore how this works in context, especially when pairing students with courses or with each other.

‍