Self joins, CROSS JOIN & FULL OUTER emulation

Join a table to itself, build combinations with CROSS JOIN, emulate FULL OUTER JOIN and use USING.

You already know INNER, LEFT and RIGHT joins. This lesson covers the rest of the toolbox: self joins for hierarchies and comparisons within one table, CROSS JOIN for combinations, emulating FULL OUTER JOIN, non-equality joins, and the USING shorthand.

Self joins#

A self join joins a table to itself using two different aliases. The classic case is an employee table where manager_id points at another employee:

SQL

CREATE TABLE staff (
    id         INT PRIMARY KEY,
    name       VARCHAR(30) NOT NULL,
    title      VARCHAR(30) NOT NULL,
    salary     INT NOT NULL,
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES staff (id)
);

INSERT INTO staff VALUES
    (1, 'Asha',   'CEO',              250000, NULL),
    (2, 'Rohan',  'CTO',              180000, 1),
    (3, 'Meera',  'Engineer',         120000, 2),
    (4, 'Karan',  'Engineer',         190000, 2),
    (5, 'Vikram', 'Sales Director',   150000, 1),
    (6, 'Neha',   'Account Manager',   90000, 5);

Show each person with their manager's name:

SQL

SELECT e.name AS employee, e.title, m.name AS manager
FROM staff AS e
LEFT JOIN staff AS m ON m.id = e.manager_id
ORDER BY e.id;

Output

+----------+-----------------+---------+
| employee | title           | manager |
+----------+-----------------+---------+
| Asha     | CEO             | NULL    |
| Rohan    | CTO             | Asha    |
| Meera    | Engineer        | Rohan   |
| Karan    | Engineer        | Rohan   |
| Vikram   | Sales Director  | Asha    |
| Neha     | Account Manager | Vikram  |
+----------+-----------------+---------+

Think of e and m as two copies of the same list. For each employee row e, we look up the row m whose id is the employee's manager_id. A LEFT JOIN keeps Asha, who has no manager.

Self joins also compare rows with each other. Who earns more than their own manager?

SQL

SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM staff e
JOIN staff m ON m.id = e.manager_id
WHERE e.salary > m.salary;

Output

+-------+--------+---------+----------------+
| name  | salary | manager | manager_salary |
+-------+--------+---------+----------------+
| Karan | 190000 | Rohan   |         180000 |
+-------+--------+---------+----------------+

How many direct reports does each manager have?

SQL

SELECT m.name AS manager, COUNT(e.id) AS direct_reports
FROM staff m
JOIN staff e ON e.manager_id = m.id
GROUP BY m.id, m.name
ORDER BY direct_reports DESC, manager;

Output

+---------+----------------+
| manager | direct_reports |
+---------+----------------+
| Asha    |              2 |
| Rohan   |              2 |
| Vikram  |              1 |
+---------+----------------+

A self join goes one level at a time. To walk a whole tree of any depth (everyone under the CEO, however deep), you'll use a recursive CTE in the CTEs lesson.

Finding pairs#

Self joins with < produce each pair once, which is handy for spotting duplicates or matching things up:

SQL

SELECT a.name AS person_1, b.name AS person_2, a.title
FROM staff a
JOIN staff b ON a.title = b.title AND a.id < b.id;

Output

+----------+----------+----------+
| person_1 | person_2 | title    |
+----------+----------+----------+
| Meera    | Karan    | Engineer |
+----------+----------+----------+

Using a.id < b.id (rather than <>) avoids both pairing a row with itself and listing (Meera, Karan) as well as (Karan, Meera).

CROSS JOIN: every combination#

A CROSS JOIN pairs every row of one table with every row of another. This is the Cartesian product:

SQL

CREATE TABLE sizes   (size VARCHAR(3));
CREATE TABLE colours (colour VARCHAR(10));
INSERT INTO sizes   VALUES ('S'), ('M'), ('L');
INSERT INTO colours VALUES ('Black'), ('White');

SELECT s.size, c.colour, CONCAT('TSHIRT-', s.size, '-', UPPER(c.colour)) AS sku
FROM sizes s
CROSS JOIN colours c
ORDER BY c.colour, FIELD(s.size, 'S', 'M', 'L');

Output

+------+--------+----------------+
| size | colour | sku            |
+------+--------+----------------+
| S    | Black  | TSHIRT-S-BLACK |
| M    | Black  | TSHIRT-M-BLACK |
| L    | Black  | TSHIRT-L-BLACK |
| S    | White  | TSHIRT-S-WHITE |
| M    | White  | TSHIRT-M-WHITE |
| L    | White  | TSHIRT-L-WHITE |
+------+--------+----------------+

3 sizes × 2 colours = 6 rows. Cross joins are useful for generating combinations (product variants, a calendar × store grid for reports), but row counts multiply fast: two 10,000-row tables give 100 million rows. An accidental cross join, usually from a missing ON clause, is a common cause of queries that never finish.

The old comma syntax FROM a, b WHERE a.id = b.a_id is an inner join written with the condition in WHERE. If you forget the WHERE, it becomes a cross join. Prefer explicit JOIN ... ON.

FULL OUTER JOIN (emulated)#

A full outer join returns all rows from both sides, matched where possible. MySQL doesn't support FULL OUTER JOIN, but you can build one with UNION:

SQL

CREATE TABLE crm_contacts  (email VARCHAR(50) PRIMARY KEY, name VARCHAR(30));
CREATE TABLE newsletter    (email VARCHAR(50) PRIMARY KEY, subscribed DATE);
INSERT INTO crm_contacts VALUES ('ada@x.com', 'Ada'), ('alan@x.com', 'Alan');
INSERT INTO newsletter   VALUES ('alan@x.com', '2026-01-10'), ('grace@x.com', '2026-02-01');

SELECT COALESCE(c.email, n.email) AS email, c.name, n.subscribed
FROM crm_contacts c LEFT JOIN newsletter n ON n.email = c.email
UNION
SELECT COALESCE(c.email, n.email), c.name, n.subscribed
FROM crm_contacts c RIGHT JOIN newsletter n ON n.email = c.email
ORDER BY email;

Output

+-------------+------+------------+
| email       | name | subscribed |
+-------------+------+------------+
| ada@x.com   | Ada  | NULL       |
| alan@x.com  | Alan | 2026-01-10 |
| grace@x.com | NULL | 2026-02-01 |
+-------------+------+------------+

UNION removes the duplicate copy of Alan, who matched in both halves. On large tables, a faster version uses UNION ALL and keeps only the unmatched rows in the second half (... RIGHT JOIN ... WHERE c.email IS NULL), which avoids the duplicate-removal step.

Non-equality joins#

ON can hold any condition, not just =. A range join matches values against bands:

SQL

CREATE TABLE salary_bands (band CHAR(1), min_salary INT, max_salary INT);
INSERT INTO salary_bands VALUES ('C', 0, 99999), ('B', 100000, 179999), ('A', 180000, 999999);

SELECT s.name, s.salary, b.band
FROM staff s
JOIN salary_bands b ON s.salary BETWEEN b.min_salary AND b.max_salary
ORDER BY s.salary DESC;

Output

+--------+--------+------+
| name   | salary | band |
+--------+--------+------+
| Asha   | 250000 | A    |
| Karan  | 190000 | A    |
| Rohan  | 180000 | A    |
| Vikram | 150000 | B    |
| Meera  | 120000 | B    |
| Neha   |  90000 | C    |
+--------+--------+------+

USING and NATURAL JOIN#

When the join columns have the same name in both tables, USING is a shorthand:

SQL

CREATE TABLE emp_badges (id INT PRIMARY KEY, badge VARCHAR(10));
INSERT INTO emp_badges VALUES (1, 'GOLD'), (3, 'BLUE');

SELECT id, name, badge
FROM staff
JOIN emp_badges USING (id);

Output

+----+-------+-------+
| id | name  | badge |
+----+-------+-------+
|  1 | Asha  | GOLD  |
|  3 | Meera | BLUE  |
+----+-------+-------+

With USING, the shared column appears once and you can refer to it unqualified (id). NATURAL JOIN goes one step further and joins on every column with the same name, automatically. Avoid it: adding an innocent column such as created_at to both tables silently changes the join.

Semi-joins: "has at least one match"#

Sometimes you only want to know whether a match exists, without repeating rows for every match. Joining would duplicate managers once per report. Instead use EXISTS (or IN), which you'll study in the next lesson:

SQL

SELECT m.name
FROM staff m
WHERE EXISTS (SELECT 1 FROM staff e WHERE e.manager_id = m.id);

Output

+--------+
| name   |
+--------+
| Asha   |
| Rohan  |
| Vikram |
+--------+

Summary of join types#

Join	Returns
`INNER JOIN`	matching pairs only
`LEFT` / `RIGHT JOIN`	all rows of one side + matches
FULL OUTER (emulated)	all rows of both sides
`CROSS JOIN`	every combination
Self join	rows matched with other rows of the same table
Anti-join (`LEFT JOIN ... IS NULL` / `NOT EXISTS`)	rows with no match
Semi-join (`EXISTS` / `IN`)	rows with at least one match, without duplicates

What's next#

That EXISTS was a sneak preview. Next: subqueries, which are queries inside queries, including correlated subqueries.

Self joins#

Finding pairs#

CROSS JOIN: every combination#

FULL OUTER JOIN (emulated)#

Non-equality joins#

USING and NATURAL JOIN#

Semi-joins: "has at least one match"#

Summary of join types#

What's next#

Quick quiz