The joins so far combined two different tables: orders with customers, employees with departments. This chapter covers two special cases that complete the picture:
A SELF JOIN, where a table is joined with itself. It is the standard way to handle hierarchies (employee and manager) and to compare rows of the same table with each other (pairs, duplicates, "earns more than").
A CROSS JOIN, which has no join condition and pairs every row with every row. It is useful for generating combinations, and dangerous when it happens by accident.
Both build directly on the INNER JOIN and LEFT, RIGHT & FULL OUTER JOIN chapters. All examples use the shared company_db sample database (plus three tiny practice tables for the cross join section), and every output shown is real MySQL 8.4 output.
What is a SELF JOIN?
A SELF JOIN is a join in which a table is joined with itself. The table appears twice in the FROM clause under two different aliases, and SQL treats the two copies as if they were separate tables.
There is no SELF JOIN keyword. A self join is an ordinary INNER JOIN, LEFT JOIN or even CROSS JOIN whose two tables happen to be the same one.
Why Would a Table Need to Join Itself?
Look at the manager_id column of employees:
SELECT emp_id, name, manager_id
FROM employees
ORDER BY emp_id;+--------+--------------+------------+
| emp_id | name | manager_id |
+--------+--------------+------------+
| 1 | Aarav Sharma | NULL |
| 2 | Priya Iyer | 1 |
| 3 | Rohan Mehta | 1 |
| 4 | Sneha Kapoor | 1 |
| 5 | Vikram Singh | 4 |
| 6 | Ananya Das | 4 |
| 7 | Karan Patel | 1 |
| 8 | Meera Nair | 7 |
| 9 | Arjun Reddy | 1 |
| 10 | Ishita Gupta | 1 |
+--------+--------------+------------+
10 rows in set (0.00 sec)A manager is also an employee, so manager_id is a foreign key that points back into the same table (FOREIGN KEY (manager_id) REFERENCES employees(emp_id)). This is called a self-referencing or recursive relationship. It describes this reporting tree:
Aarav Sharma (1)
|-- Priya Iyer (2)
|-- Rohan Mehta (3)
|-- Sneha Kapoor (4)
| |-- Vikram Singh (5)
| |-- Ananya Das (6)
|-- Karan Patel (7)
| |-- Meera Nair (8)
|-- Arjun Reddy (9)
|-- Ishita Gupta (10)To print "Vikram Singh's manager is Sneha Kapoor", we need to read Vikram's row (to get manager_id = 4) and row 4 (to get the name). Two different rows of the same table must sit side by side in one result row. That is exactly what a join does, so we join the table to itself.
Employee and Manager: The Classic Self Join
Imagine two copies of the employees table: one called e (the employee) and one called m (the manager). Pair a row of e with the row of m whose emp_id equals e.manager_id:
e (as employee) m (as manager)
+--------+--------------+------------+ +--------+--------------+
| emp_id | name | manager_id | | emp_id | name |
+--------+--------------+------------+ +--------+--------------+
| 5 | Vikram Singh | 4 -----+----->| 4 | Sneha Kapoor |
| 8 | Meera Nair | 7 -----+----->| 7 | Karan Patel |
| 1 | Aarav Sharma | NULL | ? | | |
+--------+--------------+------------+ +--------+--------------+
ON e.manager_id = m.emp_idUsing a LEFT JOIN so that Aarav (who has no manager) is not lost:
SELECT e.emp_id,
e.name AS employee,
COALESCE(m.name, '-- (top boss)') AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id
ORDER BY e.emp_id;+--------+--------------+---------------+
| emp_id | employee | manager |
+--------+--------------+---------------+
| 1 | Aarav Sharma | -- (top boss) |
| 2 | Priya Iyer | Aarav Sharma |
| 3 | Rohan Mehta | Aarav Sharma |
| 4 | Sneha Kapoor | Aarav Sharma |
| 5 | Vikram Singh | Sneha Kapoor |
| 6 | Ananya Das | Sneha Kapoor |
| 7 | Karan Patel | Aarav Sharma |
| 8 | Meera Nair | Karan Patel |
| 9 | Arjun Reddy | Aarav Sharma |
| 10 | Ishita Gupta | Aarav Sharma |
+--------+--------------+---------------+
10 rows in set (0.00 sec)With an INNER JOIN instead, Aarav's row would be dropped (his manager_id is NULL, which matches nothing), and you would get 9 rows, as we saw in the SQL Joins chapter.
The direction of the ON condition is the most common source of confusion. Read it slowly: e.manager_id = m.emp_id means "m is the row whose ID is e's manager". If you accidentally write e.emp_id = m.manager_id, then m becomes e's subordinate, and the column you labelled "manager" actually shows reports.
Aliases Are Compulsory
Without aliases, MySQL cannot tell the two copies apart:
SELECT name
FROM employees
JOIN employees ON manager_id = emp_id;ERROR 1066 (42000): Not unique table/alias: 'employees'Choose aliases that describe the role each copy plays (e/m, emp/mgr, e1/e2). It makes the ON condition much easier to read correctly.
Aggregating Over a Self Join
How many direct reports does each manager have? Group by the manager copy:
SELECT m.name AS manager, COUNT(*) AS direct_reports
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
GROUP BY m.emp_id
ORDER BY direct_reports DESC, m.name;+--------------+----------------+
| manager | direct_reports |
+--------------+----------------+
| Aarav Sharma | 6 |
| Sneha Kapoor | 2 |
| Karan Patel | 1 |
+--------------+----------------+
3 rows in set (0.00 sec)To list every employee with their number of reports (0 for people who manage nobody), make the manager copy the left table of a LEFT JOIN and count a column of the right side, exactly as in the previous chapter:
SELECT m.name AS manager, COUNT(e.emp_id) AS direct_reports
FROM employees m
LEFT JOIN employees e ON e.manager_id = m.emp_id
GROUP BY m.emp_id
ORDER BY direct_reports DESC, m.emp_id;+--------------+----------------+
| manager | direct_reports |
+--------------+----------------+
| Aarav Sharma | 6 |
| Sneha Kapoor | 2 |
| Karan Patel | 1 |
| Priya Iyer | 0 |
| Rohan Mehta | 0 |
| Vikram Singh | 0 |
| Ananya Das | 0 |
| Meera Nair | 0 |
| Arjun Reddy | 0 |
| Ishita Gupta | 0 |
+--------------+----------------+
10 rows in set (0.00 sec)Comparing an Employee With Their Manager
Once an employee and their manager are in the same row, you can compare their columns in WHERE. The famous interview question is "find employees who earn more than their manager":
SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;Empty set (0.00 sec)In our company nobody out-earns their manager, so the correct answer is an empty set. Memorise the shape of the query anyway: it is asked in almost every SQL interview round. The same shape answers "who joined the company before their own manager?":
SELECT e.name AS employee, e.hire_date, m.name AS manager, m.hire_date AS manager_hired
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.hire_date < m.hire_date;+--------------+------------+--------------+---------------+
| employee | hire_date | manager | manager_hired |
+--------------+------------+--------------+---------------+
| Sneha Kapoor | 2017-11-20 | Aarav Sharma | 2018-04-12 |
+--------------+------------+--------------+---------------+
1 row in set (0.00 sec)And "employees who work in a different department from their manager":
SELECT e.name AS employee, e.dept_id, m.name AS manager, m.dept_id AS manager_dept
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.dept_id <> m.dept_id
ORDER BY e.emp_id;+--------------+---------+--------------+--------------+
| employee | dept_id | manager | manager_dept |
+--------------+---------+--------------+--------------+
| Sneha Kapoor | 20 | Aarav Sharma | 10 |
| Karan Patel | 30 | Aarav Sharma | 10 |
| Arjun Reddy | 40 | Aarav Sharma | 10 |
+--------------+---------+--------------+--------------+
3 rows in set (0.00 sec)Look carefully: Ishita reports to Aarav and has no department, which is certainly "different", yet she is missing. NULL <> 10 is UNKNOWN, so WHERE drops her. If she should be included, add OR e.dept_id IS NULL (the NULL Values chapter's rule applies inside joins too).
Going Up More Than One Level
Each extra self join climbs one more level of the hierarchy. Employee, manager, and manager's manager:
SELECT e.name AS employee,
m.name AS manager,
gm.name AS managers_manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id
LEFT JOIN employees gm ON m.manager_id = gm.emp_id
ORDER BY e.emp_id;+--------------+--------------+------------------+
| employee | manager | managers_manager |
+--------------+--------------+------------------+
| Aarav Sharma | NULL | NULL |
| Priya Iyer | Aarav Sharma | NULL |
| Rohan Mehta | Aarav Sharma | NULL |
| Sneha Kapoor | Aarav Sharma | NULL |
| Vikram Singh | Sneha Kapoor | Aarav Sharma |
| Ananya Das | Sneha Kapoor | Aarav Sharma |
| Karan Patel | Aarav Sharma | NULL |
| Meera Nair | Karan Patel | Aarav Sharma |
| Arjun Reddy | Aarav Sharma | NULL |
| Ishita Gupta | Aarav Sharma | NULL |
+--------------+--------------+------------------+
10 rows in set (0.00 sec)Both joins are LEFT JOINs. If the second were an INNER JOIN, everyone whose manager has no manager (Priya, Rohan, and so on) would disappear, the "once LEFT, stay LEFT" rule from the previous chapter.
The limitation is obvious: one self join per level, and you must know the depth in advance. Real hierarchies (org charts, folder trees, category trees, comment threads) can be any depth. For those, MySQL 8 offers recursive common table expressions (WITH RECURSIVE), covered in the Common Table Expressions (WITH) chapter.
Pairing Rows of the Same Table
A second big use of self joins: comparing rows with other rows of the same table, not through a foreign key but through a shared value. Which employees live in the same city?
A first, naive attempt joins on city only:
SELECT e1.name, e2.name, e1.city
FROM employees e1
JOIN employees e2 ON e1.city = e2.city
ORDER BY e1.city, e1.name, e2.name;+--------------+--------------+-----------+
| name | name | city |
+--------------+--------------+-----------+
| Aarav Sharma | Aarav Sharma | Bengaluru |
| Aarav Sharma | Rohan Mehta | Bengaluru |
| Rohan Mehta | Aarav Sharma | Bengaluru |
| Rohan Mehta | Rohan Mehta | Bengaluru |
| Priya Iyer | Priya Iyer | Chennai |
| Karan Patel | Karan Patel | Delhi |
| Arjun Reddy | Arjun Reddy | Hyderabad |
| Meera Nair | Meera Nair | Kochi |
| Ananya Das | Ananya Das | Kolkata |
| Sneha Kapoor | Sneha Kapoor | Mumbai |
| Sneha Kapoor | Vikram Singh | Mumbai |
| Vikram Singh | Sneha Kapoor | Mumbai |
| Vikram Singh | Vikram Singh | Mumbai |
+--------------+--------------+-----------+
13 rows in set (0.00 sec)Two problems: every person is paired with themselves (Priya-Priya), and every real pair appears twice (Aarav-Rohan and Rohan-Aarav). Excluding self-pairs with <> fixes only the first problem:
SELECT e1.name, e2.name, e1.city
FROM employees e1
JOIN employees e2 ON e1.city = e2.city AND e1.emp_id <> e2.emp_id
ORDER BY e1.city, e1.name, e2.name;+--------------+--------------+-----------+
| name | name | city |
+--------------+--------------+-----------+
| Aarav Sharma | Rohan Mehta | Bengaluru |
| Rohan Mehta | Aarav Sharma | Bengaluru |
| Sneha Kapoor | Vikram Singh | Mumbai |
| Vikram Singh | Sneha Kapoor | Mumbai |
+--------------+--------------+-----------+
4 rows in set (0.00 sec)The standard trick is e1.emp_id < e2.emp_id. It removes self-pairs (an ID is never less than itself) and keeps only one ordering of each pair (the one where the smaller ID comes first):
SELECT e1.name AS person_1, e2.name AS person_2, e1.city
FROM employees e1
JOIN employees e2 ON e1.city = e2.city AND e1.emp_id < e2.emp_id
ORDER BY e1.city;+--------------+--------------+-----------+
| person_1 | person_2 | city |
+--------------+--------------+-----------+
| Aarav Sharma | Rohan Mehta | Bengaluru |
| Sneha Kapoor | Vikram Singh | Mumbai |
+--------------+--------------+-----------+
2 rows in set (0.00 sec)Notice that this is a non-equi join: the condition mixes = with <. Ishita has a NULL city, so she pairs with nobody, not even herself.
The same pattern finds rows that share any value, for example employees with the same salary (a self-join alternative to the GROUP BY + HAVING duplicate finder from the GROUP BY & HAVING chapter, with the advantage that it shows who the duplicates are):
SELECT e1.name AS person_1, e2.name AS person_2, e1.salary
FROM employees e1
JOIN employees e2 ON e1.salary = e2.salary AND e1.emp_id < e2.emp_id;+------------+-------------+----------+
| person_1 | person_2 | salary |
+------------+-------------+----------+
| Priya Iyer | Rohan Mehta | 95000.00 |
+------------+-------------+----------+
1 row in set (0.00 sec)A word of caution: for n rows that all share one value, a pairing self join produces n(n−1)/2 rows, which grows quickly. 1,000 customers in the same city give 499,500 pairs.
What is a CROSS JOIN?
A CROSS JOIN returns the Cartesian product of two tables: every row of the first table combined with every row of the second. It has no join condition. If the tables have m and n rows, the result has exactly m × n rows.
SELECT columns
FROM table1
CROSS JOIN table2;
-- equivalent old style:
SELECT columns
FROM table1, table2;In the SQL Joins chapter we saw that every join is logically a Cartesian product followed by a filter. A cross join is simply the product without the filter.
Practice Tables
Our company_db has no natural "combinations" data, so for this section create three tiny tables in a separate practice database (so that the shared company_db stays unchanged):
CREATE DATABASE practice_db;
USE practice_db;
CREATE TABLE sizes (size_code VARCHAR(5) PRIMARY KEY);
INSERT INTO sizes VALUES ('S'), ('M'), ('L');
CREATE TABLE colors (color VARCHAR(10) PRIMARY KEY);
INSERT INTO colors VALUES ('Black'), ('White'), ('Navy'), ('Maroon');
CREATE TABLE report_months (month_no INT PRIMARY KEY, month_name CHAR(3) NOT NULL);
INSERT INTO report_months VALUES (1,'Jan'), (2,'Feb'), (3,'Mar'), (4,'Apr');Generating All Combinations
A clothing store sells T-shirts in 3 sizes and 4 colours. Which variants (SKUs) exist? Every size with every colour:
SELECT COUNT(*) FROM sizes CROSS JOIN colors;+----------+
| COUNT(*) |
+----------+
| 12 |
+----------+
1 row in set (0.00 sec)SELECT CONCAT(c.color, '-', s.size_code) AS sku
FROM colors c
CROSS JOIN sizes s
ORDER BY c.color, s.size_code;+----------+
| sku |
+----------+
| Black-L |
| Black-M |
| Black-S |
| Maroon-L |
| Maroon-M |
| Maroon-S |
| Navy-L |
| Navy-M |
| Navy-S |
| White-L |
| White-M |
| White-S |
+----------+
12 rows in set (0.00 sec)3 × 4 = 12, exactly as promised. Add a new colour and the query automatically produces 3 more SKUs. (The order is alphabetical, so L sorts before M and S. A real table would have a sort_order column for sizes.)
CROSS JOIN + LEFT JOIN: Filling the Gaps in a Report
This is the most valuable practical use of a cross join. Suppose we want a month-by-month order report for customers 101 and 102 for January to April 2024, including months with no orders (shown as 0). A plain GROUP BY on orders can only produce months that have orders. The trick:
CROSS JOIN customers with months to build the complete grid of (customer, month) cells, 2 × 4 = 8 cells.
LEFT JOIN the orders onto that grid, so empty cells survive with NULLs.
GROUP BY the cell, counting a right-side column and using COALESCE for the sum.
SELECT c.name, m.month_name,
COUNT(o.order_id) AS orders,
COALESCE(SUM(o.amount), 0) AS total
FROM company_db.customers c
CROSS JOIN report_months m
LEFT JOIN company_db.orders o
ON o.customer_id = c.customer_id
AND MONTH(o.order_date) = m.month_no
AND YEAR(o.order_date) = 2024
WHERE c.customer_id IN (101, 102)
GROUP BY c.customer_id, m.month_no
ORDER BY c.customer_id, m.month_no;+-------------+------------+--------+---------+
| name | month_name | orders | total |
+-------------+------------+--------+---------+
| Rahul Verma | Jan | 1 | 2500.00 |
| Rahul Verma | Feb | 1 | 1200.00 |
| Rahul Verma | Mar | 1 | 5600.00 |
| Rahul Verma | Apr | 0 | 0.00 |
| Kavya Menon | Jan | 1 | 4800.00 |
| Kavya Menon | Feb | 0 | 0.00 |
| Kavya Menon | Mar | 1 | 3100.00 |
| Kavya Menon | Apr | 0 | 0.00 |
+-------------+------------+--------+---------+
8 rows in set (0.00 sec)Every cell of the grid is present, including Rahul's empty April and Kavya's empty February and April. (We are inside practice_db, so the company_db tables are written with the database name in front: company_db.customers. MySQL lets one query join tables from different databases on the same server.) Notice how the order conditions sit in the ON clause of the LEFT JOIN, for the reasons explained in the previous chapter.
Cross Joining a Table With Itself
A cross join can also be a self join. Suppose every department must hold one meeting with every other department. All possible pairs:
SELECT COUNT(*) FROM departments d1 CROSS JOIN departments d2;+----------+
| COUNT(*) |
+----------+
| 25 |
+----------+
1 row in set (0.00 sec)25 = 5 × 5 includes each department paired with itself and every pair twice. Applying the < trick from the self join section leaves 5 × 4 / 2 = 10 unique meetings:
SELECT d1.dept_name AS team_a, d2.dept_name AS team_b
FROM departments d1
CROSS JOIN departments d2
WHERE d1.dept_id < d2.dept_id
ORDER BY d1.dept_id, d2.dept_id;+-------------+-----------+
| team_a | team_b |
+-------------+-----------+
| Engineering | Sales |
| Engineering | HR |
| Engineering | Marketing |
| Engineering | Research |
| Sales | HR |
| Sales | Marketing |
| Sales | Research |
| HR | Marketing |
| HR | Research |
| Marketing | Research |
+-------------+-----------+
10 rows in set (0.00 sec)This is how fixtures for a round-robin tournament are generated: n teams give n(n−1)/2 matches.
CROSS JOIN + WHERE = INNER JOIN
Add the join condition as a WHERE filter on a cross join and you are back to an inner join:
SELECT e.name, d.dept_name
FROM employees e
CROSS JOIN departments d
WHERE e.dept_id = d.dept_id
ORDER BY e.emp_id
LIMIT 3;+--------------+-------------+
| name | dept_name |
+--------------+-------------+
| Aarav Sharma | Engineering |
| Priya Iyer | Engineering |
| Rohan Mehta | Engineering |
+--------------+-------------+
3 rows in set (0.00 sec)MySQL goes even further: in MySQL, JOIN, INNER JOIN and CROSS JOIN are syntactic equivalents. A CROSS JOIN may have an ON clause, and an INNER JOIN without ON is a cross join:
SELECT e.name, d.dept_name
FROM employees e
CROSS JOIN departments d ON e.dept_id = d.dept_id
ORDER BY e.emp_id
LIMIT 3;+--------------+-------------+
| name | dept_name |
+--------------+-------------+
| Aarav Sharma | Engineering |
| Priya Iyer | Engineering |
| Rohan Mehta | Engineering |
+--------------+-------------+
3 rows in set (0.00 sec)That last query is legal in MySQL but not in standard SQL, and it is confusing to read. Use CROSS JOIN only when you really want every combination, and JOIN ... ON otherwise, so your intent is clear to readers and portable to other databases.
The Accidental Cross Join
Most cross joins in real life are bugs: a forgotten or incomplete join condition. They are dangerous because they do not raise an error; they just return far too many rows. With the comma style, three tables and no WHERE:
SELECT COUNT(*) FROM employees e1, employees e2, orders o;+----------+
| COUNT(*) |
+----------+
| 800 |
+----------+
1 row in set (0.00 sec)10 × 10 × 8 = 800 rows from tables with 28 rows in total. Now imagine an orders table with 10 lakh rows cross-joined with a customers table of 1 lakh rows: 1011 combinations. Such a query can run for hours, fill the temporary disk and slow down the whole server.
Warning signs of an accidental cross join:
Row counts that are suspiciously large, or exactly equal to the product of table sizes.
Totals that are multiples of the correct value.
N tables in FROM but fewer than N − 1 join conditions.
In
EXPLAIN, a join with no condition between two tables.
SELF JOIN vs CROSS JOIN at a Glance
Point | SELF JOIN | CROSS JOIN |
|---|---|---|
What it is | A table joined with itself (any join type) | Cartesian product of two tables |
Keyword | None; same table twice with aliases |
|
Join condition | Yes, usually | No |
Result size | Depends on matches | Always m × n |
Typical uses | Hierarchies (employee-manager), comparing rows, pairs, duplicates | All combinations, report grids, test data, pairings |
Classic trap | Reversed ON direction; self-pairs and duplicate pairs | Accidental cross join from a missing condition |
Common Mistakes
Missing aliases in a self join (error 1066).
Reversing the hierarchy direction:
e.emp_id = m.manager_idgives subordinates, not managers.Using INNER JOIN for the top of the hierarchy and losing the CEO (NULL manager). Use LEFT JOIN.
Forgetting
e1.id < e2.idwhen pairing rows, producing self-pairs and every pair twice.Hard-coding many self joins for deep hierarchies instead of using a recursive CTE.
Accidental cross joins from comma-separated tables without a join condition.
Writing CROSS JOIN ... ON in MySQL; it works but misleads readers and is not portable.
Interview Questions
What is a self join? Give an example.
A join of a table with itself, using two aliases. Example: SELECT e.name, m.name FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id lists each employee with their manager.
Is SELF JOIN a separate join type with its own keyword?
No. It is an inner, outer or cross join where both sides are the same table.
Write a query to find employees who earn more than their managers.
SELECT e.name FROM employees e JOIN employees m ON e.manager_id = m.emp_id WHERE e.salary > m.salary;
How do you avoid duplicate and self pairs when pairing rows of a table?
Add e1.id < e2.id to the join condition. It excludes a row paired with itself and keeps only one ordering of each pair.
What is a CROSS JOIN and how many rows does it return?
The Cartesian product of two tables, with no join condition. It returns m × n rows for tables of m and n rows.
What is the difference between CROSS JOIN and INNER JOIN?
An inner join keeps only row pairs that satisfy the join condition; a cross join keeps all pairs. A cross join filtered by the join condition in WHERE gives the same result as the inner join. In MySQL the two keywords are even syntactically interchangeable.
Give a practical use of CROSS JOIN.
Generating all product variants (sizes × colours), building a complete customer × month grid that is then LEFT JOINed to facts so missing combinations show as 0, or generating round-robin pairings.
How do you get the full chain of managers for an employee at any depth?
A fixed number of self joins handles a fixed depth only. For arbitrary depth use a recursive CTE (WITH RECURSIVE).
Summary
A self join joins a table to itself using two aliases; there is no special keyword.
It handles self-referencing relationships such as
employees.manager_id → employees.emp_id.Use a LEFT JOIN to keep rows whose parent is NULL (the top of the hierarchy), and check the direction of the ON condition.
Self joins compare rows: employee vs manager, same city, same salary. Use
e1.id < e2.idto get each pair once.Each self join climbs one level; arbitrary depth needs a recursive CTE.
A cross join returns all m × n combinations, with no condition.
Useful for combinations and report grids (CROSS JOIN, then LEFT JOIN the facts).
Cross join + WHERE on the join condition = inner join; in MySQL CROSS JOIN and INNER JOIN are interchangeable keywords.
Accidental cross joins from missing conditions are a serious performance bug.
Joins combine tables side by side, adding columns. In the next chapter, UNION & Set Operations, we combine query results vertically, stacking rows, which we already used briefly to emulate FULL OUTER JOIN.