SELF JOIN, CROSS JOIN & Set Operations

SELF JOIN

CREATE TABLE employees (
  id         integer PRIMARY KEY,
  name       text,
  manager_id integer REFERENCES employees(id),
  salary     numeric(10,2)
);

-- 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;

-- employees earning 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, each pair once
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;

Use LEFT JOIN for the manager lookup so the person at the top, who has no manager, is not dropped.

CROSS JOIN

Produces every combination — the Cartesian product.

SELECT s.size, c.color FROM sizes s CROSS JOIN colors c;
SELECT s.size, c.color FROM sizes s, colors c;    -- same thing, older syntax

It is genuinely useful for building a complete grid that you then LEFT JOIN real data onto, so empty cells show as zero instead of vanishing:

SELECT d.day::date, dept.code, count(s.id)
FROM generate_series(current_date - 6, current_date, interval '1 day') AS d(day)
CROSS JOIN departments dept
LEFT JOIN students s
       ON s.department = dept.code AND date_trunc('day', s.created_at) = d.day
GROUP BY 1, 2 ORDER BY 1, 2;

A forgotten ON clause silently becomes a cross join. Two tables of 100,000 rows produce ten billion rows — the usual reason a query never finishes.

UNION and UNION ALL

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

Rules: each SELECT needs the same number of columns with compatible types; column names come from the first branch; ORDER BY and LIMIT apply to the combined result and go at the 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 sort or hash)	Faster

Use UNION ALL unless you actually need de-duplication.

INTERSECT and EXCEPT

-- rows present in both
SELECT email FROM students
INTERSECT
SELECT email FROM newsletter_subscribers;

-- rows in the first but not the second
SELECT email FROM students
EXCEPT
SELECT email FROM newsletter_subscribers;

Both have ALL variants that keep duplicates. These are a clean way to compare two sets without writing a join and a NOT EXISTS.

Set operations and NULL

Set operations treat NULL as equal to NULL when removing duplicates, unlike a normal = comparison. That is often exactly what you want when reconciling data, and it is a common source of confusion when you expect NULL = NULL behaviour everywhere.