Set operations

The easiest way to show how a set operation works is to look at a query that uses one:

SELECT * FROM customers WHERE state='MD' 
INTERSECT
SELECT * FROM customers WHERE zip='21340';

This is obviously a trivial example—you could just put both WHERE clauses into a single line and get the same result more easily. But there are more complicated types of queries you can build using INTERSECT, INTERSECT ALL, EXCEPT, and EXCEPT ALL that are the easiest way to write what you're looking for. EXCEPT is shown later for helping determine if two queries give the same output for example.

SetOp changed to HashSetOp in PostgreSQL 8.4. Here are the new and old types of plans you can get when executing an INTERSECT or EXCEPT:

EXPLAIN ANALYZE SELECT * FROM customers WHERE state='MD' INTERSECT SELECT * FROM customers WHERE zip='21340';
QUERY PLAN
----------
 HashSetOp Intersect  (cost=0.00..1463.28 rows=1 width=268) (actual time=28.379...