SELF JOIN, CROSS JOIN & UNION

SELF JOIN

A self join joins a table to itself using two aliases. It is how you model hierarchies stored in one table.

CREATE TABLE employees (
  id        INT PRIMARY KEY,
  name      VARCHAR(100),
  manager_id INT NULL,
  salary    DECIMAL(10,2),
  FOREIGN KEY (manager_id) REFERENCES employees(id)
);

-- each employee with their manager
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

Use LEFT JOIN so the CEO, who has no manager, is not dropped.

-- employees who earn more than their manager
SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

-- pairs of students in the same department (avoid duplicate pairs with <)
SELECT a.name_en, b.name_en, a.department
FROM students a
JOIN students b ON a.department = b.department AND a.id < b.id;

CROSS JOIN

A cross join produces every combination — the Cartesian product. 10 rows × 5 rows = 50 rows.

SELECT s.size, c.color
FROM sizes s CROSS JOIN colors c;

Useful for generating a full grid (all sizes × all colours, every day × every branch) that you then LEFT JOIN real data onto, so empty cells show as zero instead of disappearing.

A missing ON clause turns a join into an accidental cross join. On two tables of 100,000 rows that is 10 billion rows — the usual cause of a query that never finishes.

UNION and UNION ALL

UNION stacks result sets vertically.

SELECT name_en, 'student' AS role FROM students
UNION
SELECT name, 'employee' FROM employees;

Rules:

• Every SELECT must have the same number of columns, in compatible types.
• Column names come from the first SELECT.
• ORDER BY and LIMIT apply to the combined result and go at the very end.

(SELECT name_en, cgpa FROM students WHERE department = 'CSE' ORDER BY cgpa DESC LIMIT 3)
UNION ALL
(SELECT name_en, cgpa FROM students WHERE department = 'EEE' ORDER BY cgpa DESC LIMIT 3)
ORDER BY cgpa DESC;

	UNION	UNION ALL
Duplicates	Removed	Kept
Speed	Slower (needs a sort/hash)	Faster

Use UNION ALL unless you actually need de-duplication.

INTERSECT and EXCEPT

MySQL 8.0.31+ supports INTERSECT (rows in both) and EXCEPT (rows in the first but not the second). On older versions, emulate them with INNER JOIN and LEFT JOIN … IS NULL.