Self Join and UNION

Self Join

Joining a table to itself — useful for hierarchical data.

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

Combines results of two queries (removes duplicates).

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

Keeps all rows including duplicates (faster).

SELECT product_id, 'sale' AS type, amount FROM sales
UNION ALL
SELECT product_id, 'return' AS type, amount FROM returns
ORDER BY product_id;

Pro Tip

Both queries in a UNION must have the same number of columns with compatible data types.