Postgres Lab Starting database… Playground

PostgreSQL UNION, INTERSECT and EXCEPT

Joins combine tables side by side (more columns). Set operations stack query results on top of each other (more rows). Both queries must return the same number of columns with compatible types; column names come from the first query.

  • UNION: rows from both, duplicates removed.
  • UNION ALL: rows from both, duplicates kept. Faster, because nothing needs to be compared.
  • INTERSECT: rows that appear in both.
  • EXCEPT: rows in the first that are not in the second.

A single ORDER BY at the very end sorts the combined result.

Syntax

SELECT a, b FROM t1
UNION [ALL] | INTERSECT | EXCEPT
SELECT a, b FROM t2
ORDER BY a;

Examples

Edit the code and run it. Output is printed the way psql prints it.

9.1One contact list from two tables

SELECT name, email, 'customer' AS kind FROM customers
UNION ALL
SELECT first_name || ' ' || last_name, email, 'employee' FROM employees
ORDER BY kind, name;

The second query’s columns line up by position with the first. The constant column kind labels where each row came from.

9.2UNION vs UNION ALL

SELECT city FROM customers WHERE country = 'India'
UNION
SELECT location FROM departments
ORDER BY 1;

Cities appearing in both lists (Mumbai, Hyderabad, Bengaluru) are returned once. ORDER BY 1 means “sort by the first column”.

9.3INTERSECT and EXCEPT

-- products sold in both January and February 2024
SELECT oi.product_id FROM order_items oi JOIN orders o ON o.id = oi.order_id
WHERE o.order_date BETWEEN '2024-01-01' AND '2024-01-31'
INTERSECT
SELECT oi.product_id FROM order_items oi JOIN orders o ON o.id = oi.order_id
WHERE o.order_date BETWEEN '2024-02-01' AND '2024-02-29';

-- customer ids that have never ordered
SELECT id FROM customers
EXCEPT
SELECT customer_id FROM orders;

INTERSECT keeps product 2 and 4, sold in both months. EXCEPT is another way to write an anti-join.

Exercises

Write a query and check it. Any query that returns the right rows passes.

Exercise 9.1

Return a single column place listing every distinct customer city and department location (no NULLs).
Hint

UNION removes duplicates; filter NULL cities.

Solution
SELECT city AS place FROM customers WHERE city IS NOT NULL UNION SELECT location FROM departments;

Exercise 9.2

Using EXCEPT, list the ids of products that were never ordered.
Hint

SELECT id FROM products EXCEPT SELECT product_id FROM order_items

Solution
SELECT id FROM products EXCEPT SELECT product_id FROM order_items;