A self join uses the same table in multiple roles, while CROSS JOIN returns every combination of rows from its two inputs.
A customer and that customer’s manager can both be records in customers. A self join gives each role a separate alias. A cross join answers a different question, such as producing every customer-status combination for a complete report grid.
This reference adds detail to the main course. You can read it independently: the small database below supplies every table and row used in the examples. Start with the question each query answers, then compare the returned values with the data rather than memorising a line of syntax.
Set up the practice database
Run this setup once in a new practice database, before the examples. It uses four customers and six sales. Ana has two sales, Ben has one, Cara has two, and Dev has none.
Sale 106 has no customer link; sale 104 has an unknown amount. These deliberate test cases expose missing information and unmatched relationships.
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name VARCHAR(40) NOT NULL,
city VARCHAR(40),
manager_id INTEGER
);
CREATE TABLE sales (
id INTEGER PRIMARY KEY,
customer_id INTEGER,
amount DECIMAL(10,2),
status VARCHAR(20) NOT NULL,
created_on DATE NOT NULL
);
INSERT INTO customers (id,name,city,manager_id) VALUES
(1,'Ana','Leeds',NULL),(2,'Ben','Leeds',1),
(3,'Cara','Porto',1),(4,'Dev',NULL,2);
INSERT INTO sales (id,customer_id,amount,status,created_on) VALUES
(101,1,10,'paid','2026-01-01'),
(102,1,30,'pending','2026-01-02'),
(103,2,20,'paid','2026-01-02'),
(104,3,NULL,'pending','2026-02-01'),
(105,3,20,'paid','2026-02-02'),
(106,NULL,5,'paid','2026-02-03');An amount of zero would be a known value. The NULL amount here means the value is unknown. A customer with no matching sale differs from a sale whose customer_id is NULL.
The primary keys identify individual records even when amounts or cities repeat. Dates use year-month-day format. The examples specify sorting whenever order matters; insertion order alone does not guarantee display order.
How Self joins and cross joins works
A self join is not a special storage structure or a separate SQL keyword. It is an ordinary join whose inputs happen to reference the same table. customers AS person LEFT JOIN customers AS manager ON person.manager_id=manager.id gives the table two distinct roles. The aliases are essential because name and id exist on both sides. A left join keeps people with no manager, while an inner join would omit them.
The sample hierarchy has Ana at the top, Ben and Cara reporting to Ana, and Dev reporting to Ben. One self join resolves one manager level. It does not automatically traverse an entire hierarchy. Two joins can reach a fixed second level; a recursive query is the more general tool when depth varies. Data integrity rules are still needed to avoid dangling references or cycles, since a self join itself does not enforce a valid tree.
CROSS JOIN and Cartesian products
CROSS JOIN returns the Cartesian product. If the left input has four rows and the right has two, the result has eight combinations before filtering. It has no ON condition in the standard explicit form because every pairing is intended. This can create a complete dimension grid, such as every customer with paid and pending labels, that later receives measures through a left join. Empty input on either side produces no combinations.
An accidental missing join condition can create an unintended product. That can inflate counts and sums by the size of the other input. Estimate the expected product before using a cross join on large tables. A CROSS JOIN followed by a WHERE relationship can express a filtered product, but explicit INNER JOIN ... ON normally states a key relationship more clearly. Keep intentional combination generation distinct from ordinary relationship matching.
MySQL, PostgreSQL, SQL Server and SQLite
Self joins and explicit CROSS JOIN are supported by all four systems. Use a SELECT-based derived table or a real small dimension table for portable status lists; inline VALUES table syntax differs. SQL Server does not share the USING join syntax, so ON with qualified aliases remains the portable self-join form.
Check the engine-specific syntax and version requirements in PostgreSQL table expressions.
Practice task and worked solution
Generate a complete customer-and-status grid using the two distinct statuses in sales. Count the combinations to verify the product size.
Try the task before reading its worked solution. The supplied rows make the expected answer checkable by hand.
SELECT COUNT(*) AS combinations
FROM customers AS c
CROSS JOIN (SELECT DISTINCT status FROM sales) AS statuses;combinations
8The customer input has four rows and the derived status input has two distinct rows. Four times two yields eight combinations. DISTINCT is used on the status input to create a category dimension rather than cross joining all six sale records.
Change the self join from LEFT to INNER and predict that Ana disappears because she has no manager. For the cross-grid task, remove DISTINCT from the status input and predict twenty-four combinations rather than eight. That controlled increase shows why a two-category dimension differs from six transaction rows that happen to contain those category values.
SQL Self Joins and CROSS JOIN: Roles and Combinations Syntax
Self joins and cross joins
SELECT person.name AS customer,manager.name AS manager
FROM customers AS person
LEFT JOIN customers AS manager ON person.manager_id=manager.id
ORDER BY person.id;SQL Self Joins and CROSS JOIN: Roles and Combinations Summary
- A self join lists the same table twice under different aliases, each playing a role such as person and manager.
- Qualify every column with its alias, because both copies have the same column names.
- A
LEFTself join keeps rows whose reference isNULL, such as the person with no manager. CROSS JOINreturns every combination: 4 rows by 2 rows gives 8 rows.- Use
CROSS JOINdeliberately for grids, such as every customer for every category, and check its size first.
SQL Self Joins and CROSS JOIN: Roles and Combinations Examples
Each example shows the code, the output it produces and what each part does.
Each customer with their manager
The two table references hold different roles even though they read the same stored rows. Ana has no manager id and therefore gets a placeholder on the manager side. Dev’s manager reference two resolves to Ben, not directly to Ana.
SELECT person.name AS customer,manager.name AS manager
FROM customers AS person
LEFT JOIN customers AS manager ON person.manager_id=manager.id
ORDER BY person.id;customer | manager
Ana | NULL
Ben | Ana
Cara | Ana
Dev | BenCommon SQL Self Joins and CROSS JOIN: Roles and Combinations Mistakes
Unqualified columns in a self join
SELECT name from the self join is ambiguous because both roles expose name. Qualify person.name and manager.name. Another mistake is assuming a single manager join finds the top ancestor: it resolves exactly the one relationship stated by the ON condition.
SQL Self Joins and CROSS JOIN: Roles and Combinations in Real Projects
Organisation charts and completeness grids
An organisation report can show each person with their immediate manager without duplicating manager text in every record. A separate completeness report can cross join customers with required category labels to build a full grid, then populate available measures while keeping empty categories visible.
Use role names for self joins so a reader can follow person-to-manager relationships without guessing which alias owns each column. For cross joins, estimate the product and verify each dimension is deduplicated as intended. A complete reporting grid can be useful, but crossing raw transaction tables can create enormous unintended multiplicities.
SQL Self Joins and CROSS JOIN: Roles and Combinations FAQ
Does a self join duplicate a table physically?
No. It uses multiple references to the same stored table in one query; no data is copied. The database reads the same rows for each role.
Does CROSS JOIN need ON?
No. Its explicit purpose is all combinations, so the standard form has no ON condition. Writing FROM a, b without a WHERE condition also produces a cross join, which is how accidental Cartesian products usually happen.
Can a cross join be useful?
Yes. It can build complete grids and controlled combinations when the product is intentional and appropriately sized, such as every product for every day of a month, so a report shows zero-sales days instead of leaving gaps.
How do I find someone's manager's manager?
Add another self join: join customers AS manager on the person's manager_id, then customers AS senior on the manager's manager_id. For chains of unknown length, use a recursive CTE instead, as the recursive queries lesson shows.
How do I find pairs of rows in the same table?
Self join with a condition that avoids pairing a row with itself or listing each pair twice: FROM customers a JOIN customers b ON a.city = b.city AND a.id < b.id lists each pair of customers who share a city exactly once.
SQL Self Joins and CROSS JOIN: Roles and Combinations Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
Who is Dev’s immediate manager in the self-join output?
SQL Self Joins and CROSS JOIN: Roles and Combinations Quiz
2 questions to check what you learned.
How many rows result from crossing four customers with two statuses?
- Eight
- Six, by adding the two input counts
- Four, because CROSS JOIN returns the larger input only
Why are aliases required for a readable self join?
- They physically create two permanent copies of the original table
- They distinguish the separate roles of the same table
- They make one self join automatically traverse every hierarchy level