PostgreSQL Set Operations

Set operations combine the results of two SELECT queries into one result. JOINs place tables side by side, adding columns. Set operations stack results on top of each other, adding rows.

  Query A rows
  ------------
  Query B rows
  ------------
  = One combined result

Imagine two class attendance sheets. One lists students who joined the art club. The other lists students who joined the music club. Set operations answer questions such as "Who joined at least one club?" and "Who joined both?"

Before You Start

Two rules and one small data set prepare every example on this page.

Rules for Combining Queries

• Both queries must return the same number of columns.
• Matching columns must have compatible data types.
• Column names in the final result come from the first query.

Sample Data

art_club	music_club
Asha	Ravi
Ravi	Mina
Tara	Asha

CREATE TABLE art_club   (name TEXT);
CREATE TABLE music_club (name TEXT);

INSERT INTO art_club   VALUES ('Asha'), ('Ravi'), ('Tara');
INSERT INTO music_club VALUES ('Ravi'), ('Mina'), ('Asha');

The Four Operators

Each operator answers a different question about two lists.

UNION

UNION merges both results and removes duplicate rows. The output lists every student who joined at least one club.

SELECT name FROM art_club
UNION
SELECT name FROM music_club;

Result: Asha, Ravi, Tara, Mina. Asha and Ravi appear once each.

  (Asha Ravi Tara)  U  (Ravi Mina Asha)
        = Asha Ravi Tara Mina

UNION ALL

UNION ALL keeps every row, including duplicates. It runs faster, because the database skips the duplicate check.

SELECT name FROM art_club
UNION ALL
SELECT name FROM music_club;

Result: Asha, Ravi, Tara, Ravi, Mina, Asha. Six rows appear. Choose UNION ALL when you know duplicates cannot exist or when you want to count them.

INTERSECT

INTERSECT returns only rows that appear in both results. The output lists students who joined both clubs.

SELECT name FROM art_club
INTERSECT
SELECT name FROM music_club;

Result: Asha and Ravi.

   Art       Music
  ( Tara ( Asha  ) Mina )
         ( Ravi  )
        overlap only

EXCEPT

EXCEPT returns rows from the first query that the second query does not contain. The output lists students who joined art but skipped music.

SELECT name FROM art_club
EXCEPT
SELECT name FROM music_club;

Result: Tara. Order matters here. Swap the two queries, and the answer becomes Mina.

Comparison at a Glance

Operator	Question It Answers	Duplicates
UNION	Who appears in either list?	Removed
UNION ALL	Give me everything from both lists	Kept
INTERSECT	Who appears in both lists?	Removed
EXCEPT	Who appears only in the first list?	Removed

Tips and Uses

Sorting, labels, and real tasks show how the operators work in practice.

Sorting a Combined Result

Place one ORDER BY at the very end. It sorts the full combined result.

SELECT name FROM art_club
UNION
SELECT name FROM music_club
ORDER BY name;

Adding a Label Column

Track where each row came from by adding a fixed text column to each query.

SELECT name, 'Art' AS club FROM art_club
UNION ALL
SELECT name, 'Music' AS club FROM music_club;

Real-World Uses

• Merge yearly sales tables such as sales_2024 and sales_2025 into one report.
• Find customers who bought online but never visited a store, using EXCEPT.
• Find products sold in two regions at once, using INTERSECT.