DocsAdvancedUNION & INTERSECT

UNION & INTERSECT

Advanced

Concept

UNION combines the result sets of two or more SELECT statements into one, removing duplicate rows. UNION ALL does the same but keeps duplicates and is faster because it skips the deduplication step. Both require that the SELECT statements have the same number of columns with compatible data types.

INTERSECT returns only rows that appear in both result sets, and EXCEPT (or MINUS in Oracle) returns rows from the first set that do not appear in the second. Together with UNION, these set operations provide powerful ways to combine, compare, and contrast data from different sources or different conditions.

Column names in the result come from the first SELECT statement. ORDER BY applies to the combined result and must reference columns from the first SELECT or use positional notation. Each individual SELECT can have its own WHERE, JOIN, and GROUP BY logic.

Syntax

-- UNION (removes duplicates)
SELECT name, email FROM customers
UNION
SELECT name, email FROM newsletter_subscribers;

-- UNION ALL (keeps duplicates, faster)
SELECT product_id, 'ordered' AS source FROM order_items
UNION ALL
SELECT product_id, 'wishlisted' FROM wishlists;

-- INTERSECT
SELECT customer_id FROM orders
INTERSECT
SELECT customer_id FROM reviews;

-- EXCEPT
SELECT customer_id FROM customers
EXCEPT
SELECT customer_id FROM orders;

Practical Example

Using the ecommerce schema (customers, orders, order_items, products)

-- Combined activity feed: orders and reviews, sorted by date
SELECT
  'order' AS activity_type,
  customer_id,
  order_date AS activity_date,
  'Placed order #' || order_id AS description
FROM orders

UNION ALL

SELECT
  'review' AS activity_type,
  customer_id,
  review_date AS activity_date,
  'Reviewed ' || product_name AS description
FROM reviews

ORDER BY activity_date DESC
LIMIT 50;

-- Customers who ordered but never reviewed
SELECT c.customer_id, c.first_name, c.last_name
FROM customers c
WHERE c.customer_id IN (
  SELECT customer_id FROM orders
  EXCEPT
  SELECT customer_id FROM reviews
);

Common Pitfalls & Tips

1UNION removes duplicates (like DISTINCT), which requires sorting and comparison. Use UNION ALL when you know there are no duplicates or when duplicates are acceptable — it is significantly faster.
2All SELECTs in a UNION must have the same number of columns with compatible types. Mismatched columns cause a syntax error.
3ORDER BY at the end of a UNION applies to the entire combined result, not to individual SELECTs. To order individual parts, use a subquery.

Practice UNION & INTERSECT queries