Combine queries (UNION, INTERSECT, EXCEPT)
You can use the UNION
, INTERSECT
, and EXCEPT
set operators to combine the results of two queries. These operators have the following syntax:
<query1> UNION [ALL] <query2>
<query1> INTERSECT [ALL] <query2>
<query1> EXCEPT [ALL] <query2>
Where:
-
<query1>
and<query2>
are queries to combine. The queries must be union compatible. They must return the same number of columns and the corresponding columns must have compatible data types, as described in the following article: UNION. -
UNION
adds the result of<query2>
to the result of<query1>
. The order, in whichUNION
return rows, is not determined. -
INTERSECT
returns all rows that are in both the<query1>
and<query2>
results. -
EXCEPT
returns all rows that are in the<query1>
result but not in the<query2>
result. -
ALL
specifies that duplicate rows should be included in the result. If the query does not contain theALL
modifier, theUNION
,INTERSECT
, andEXCEPT
operators eliminate duplicate rows.
To see how the set operators work, create three tables:
CREATE TABLE book_store1(
id INT PRIMARY KEY,
title VARCHAR (100)
);
CREATE TABLE book_store2(
id INT PRIMARY KEY,
title VARCHAR (100)
);
CREATE TABLE book_store3(
id INT PRIMARY KEY,
title VARCHAR (100)
);
INSERT INTO book_store1 (id, title) VALUES
(1, 'Hyperion'),
(2, '1984'),
(3, 'War and Peace'),
(4, 'The Time Machine');
INSERT INTO book_store2 (id, title) VALUES
(3, 'War and Peace'),
(4, 'The Time Machine'),
(5, 'Mrs. Dalloway' );
INSERT INTO book_store3 (id, title) VALUES
(1, 'Hyperion'),
(2, '1984'),
(5, 'Mrs. Dalloway' );
The UNION example
The following query uses UNION
to join the book_store1
and book_store2
tables:
SELECT * from book_store1 UNION SELECT * from book_store2;
The result:
id | title ----+------------------ 5 | Mrs. Dalloway 4 | The Time Machine 2 | 1984 3 | War and Peace 1 | Hyperion
Add the ALL
modifier and compare results:
SELECT * from book_store1 UNION ALL SELECT * from book_store2;
The result contains duplicate rows:
id | title ----+------------------ 1 | Hyperion 2 | 1984 3 | War and Peace 4 | The Time Machine 3 | War and Peace 4 | The Time Machine 5 | Mrs. Dalloway
The INTERSECT example
The following query uses INTERSECT
to return all rows that are in both the book_store1
and book_store2
tables:
SELECT * from book_store1 INTERSECT SELECT * from book_store2;
The result:
id | title ----+------------------ 4 | The Time Machine 3 | War and Peace
The EXCEPT example
The query below uses EXCEPT
to returns all rows that are in book_store1
but not in the book_store2
table.
SELECT * from book_store1 EXCEPT SELECT * from book_store2;
The result:
id | title ----+---------- 2 | 1984 1 | Hyperion
Combine set operators
You can combine set operators in a single query.
For example, the following query first adds rows from the book_store1
and book_store2
tables to the result, and then excludes book_store3
rows:
SELECT * from book_store1 UNION SELECT * from book_store2
EXCEPT SELECT * from book_store3;
The result:
id | title ----+------------------ 4 | The Time Machine 3 | War and Peace
Parentheses determine the order, in which operators are evaluated. Without parentheses, UNION
and EXCEPT
are executed from left to right. The expressions below are equivalent:
<query1> UNION <query2> EXCEPT <query3>
(<query1> UNION <query2>) EXCEPT <query3>
INTERSECT
has a higher priority than UNION
and EXCEPT
. The expressions below are equivalent:
<query1> UNION <query2> INTERSECT <query3>
<query1> UNION (<query2> INTERSECT <query3>)
Execute the following query with INTERSECT
:
SELECT * from book_store1 UNION SELECT * from book_store2
INTERSECT SELECT * from book_store3;
PostgreSQL executes the INTERSECT
first, its result is:
id | title ----+--------------- 5 | Mrs. Dalloway
After that, UNION
is executed to combine the result above with the book_store1
rows.
The result:
id | title ----+------------------ 5 | Mrs. Dalloway 4 | The Time Machine 2 | 1984 3 | War and Peace 1 | Hyperion
Add parentheses to the query:
(SELECT * from book_store1 UNION SELECT * from book_store2)
INTERSECT SELECT * from book_store3;
PostgreSQL executes the UNION
first, its result is:
id | title ----+------------------ 5 | Mrs. Dalloway 4 | The Time Machine 2 | 1984 3 | War and Peace 1 | Hyperion
After that, INTERSECT
returns all rows that are in both the previous result and book_store3
table.
The result:
id | title ----+--------------- 2 | 1984 1 | Hyperion 5 | Mrs. Dalloway
If you need to use additional clauses (for example, LIMIT
) in input queries, enclose queries in parentheses. If you use additional clauses in queries without parentheses, a syntax error may occur or a clause can be applied to the set operator output rather than an input query. For example, the following query is correct, but PostgreSQL applies LIMIT 10
to the UNION
result:
SELECT a FROM b UNION SELECT x FROM y LIMIT 10;
This query is equivalent to the following expression:
(SELECT a FROM b UNION SELECT x FROM y) LIMIT 10;
To apply LIMIT
to the second query, use parentheses:
SELECT a FROM b UNION (SELECT x FROM y LIMIT 10);