Self Join & UNION

Self Join joins a table with itself (e.g., employees/managers).

UNION

Combines results from two SELECTs. UNION ALL keeps duplicates.

Syntax

MYSQL

1
2
3
4
5
6
7

SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

SELECT name, email FROM customers
UNION
SELECT name, email FROM vendors;

Self Joins and UNION

SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

-- Self join: employees and managers
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- UNION removes duplicates
SELECT name FROM customers
UNION
SELECT name FROM suppliers
ORDER BY name;

-- UNION ALL keeps duplicates
SELECT city FROM customers
UNION ALL
SELECT city FROM suppliers;

-- UNION with different tables
SELECT id, "order" AS src FROM orders
UNION
SELECT id, "refund" AS src FROM refunds;

Try It Yourself

Practice

1

Exercise

Find employees with no manager.

Answer

SELECT e.name FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
WHERE m.id IS NULL;

Quick Quiz

1

UNION vs UNION ALL?

Same

UNION removes duplicates

UNION ALL is slower

UNION removes duplicates; UNION ALL keeps all rows.

Retry Quiz

Interview Questions

Use cases for self joins?

Hierarchical data (org charts), comparing rows (sequential data), finding relationships within the same table.

Previous

INNER, LEFT, RIGHT JOIN

Tutorial Complete

Back to MySQL