Combine queries (UNION, INTERSECT, EXCEPT)

Eugenia Kuzina

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 which UNION 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 the ALL modifier, the UNION, INTERSECT, and EXCEPT 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);
Found a mistake? Seleсt text and press Ctrl+Enter to report it