A self join is a regular join where a table is joined to itself, using aliases to distinguish the two roles.
It allows queries to evaluate relationships between different rows within the same physical table.
Self joins are standard for querying hierarchical tree structures, such as organisational charts or category taxonomies.
In an employee table, each row records an employee_id and a manager_id pointing to another employee_id in the same table.
By joining the table to itself on manager_id equals employee_id, a query retrieves each employee alongside their manager name.
Self joins are also used to detect duplicate entries or compare sequential events, such as tracking customer purchases over time.
Joining on unequal operators, like less than, enables triangular self joins that compute pairwise combinations without self-pairings.
A CROSS JOIN produces a Cartesian product, combining every row of the first table with every row of the second table.
It requires no ON clause because every conceivable pairing between the two input tables is generated unconditionally.
If table A contains one thousand rows and table B contains one thousand rows, their CROSS JOIN yields one million rows.
CROSS JOINs are useful for generating complete grids of possibilities, such as pairing all products with all calendar dates.
Joining sales against this complete grid exposes zero-sales days that would otherwise be missing from transaction logs.
However, accidental cross joins caused by omitting a join predicate can quickly exhaust database memory and disk storage.
In multi-table queries, a missing ON condition creates a Cartesian product that severely degrades database server performance.
Analysts should always verify that self joins use distinct aliases and ensure cross joins are restricted to small dimension sets.