Combine queries

Andrey Aksenov

Contents

Syntax overview
Prerequisites
Usage examples
- UNION
- INTERSECT
- EXCEPT
Combine set operations
- Order of evaluation
Apply modifiers to set operations

Using the operators UNION, INTERSECT, and EXCEPT, you can combine the outputs of multiple SELECT statements into a single result set. Additionally, UNION is used in recursive CTEs to combine the non-recursive and recursive terms into the final result set.

The UNION, INTERSECT, and EXCEPT operators have a similar syntax:

<query1> UNION [ALL] <query2>
<query1> INTERSECT [ALL] <query2>
<query1> EXCEPT [ALL] <query2>

<query1> and <query2> are queries whose result sets are combined by the operator. Each query must return the same number of columns with compatible data types. Learn more in UNION, CASE, and Related Constructs in the PostgreSQL documentation.

Set operators behave as follows:

UNION — combines the rows from <query1> and <query2>, removing duplicates (similar to DISTINCT).
INTERSECT — returns only the rows that are present in both <query1> and <query2>.
EXCEPT — returns rows from <query1> that are not present in <query2>.

By default, these operators eliminate duplicate rows. To retain duplicates, you can use the ALL keyword (for example, UNION ALL).

NOTE

Eliminating duplicate rows is the default for UNION, INTERSECT, and EXCEPT, even though ALL is the default for SELECT.

To follow the examples in this topic, connect to the Greengage DB master host using psql as described in Connect to Greengage DB via psql. Create a new database and connect to it:

CREATE DATABASE marketplace;
\c marketplace

Then, create three tables and populate them with data:

Create tables

CREATE TABLE warehouse1_products
(
    id       INT,
    name     VARCHAR(100),
    category VARCHAR(50)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id);

CREATE TABLE warehouse2_products
(
    id       INT,
    name     VARCHAR(100),
    category VARCHAR(50)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id);

CREATE TABLE warehouse3_products
(
    id       INT,
    name     VARCHAR(100),
    category VARCHAR(50)
)
    WITH (appendoptimized = true)
    DISTRIBUTED BY (id);

INSERT INTO warehouse1_products (id, name, category)
VALUES (1, 'Wireless mouse', 'Electronics'),
       (2, 'Bluetooth speaker', 'Electronics'),
       (3, 'Coffee maker', 'Home appliances'),
       (4, 'Desk lamp', 'Home appliances');

INSERT INTO warehouse2_products (id, name, category)
VALUES (3, 'Coffee maker', 'Home appliances'),
       (4, 'Desk lamp', 'Home appliances'),
       (5, 'Smartphone', 'Electronics');

INSERT INTO warehouse3_products (id, name, category)
VALUES (1, 'Wireless mouse', 'Electronics'),
       (2, 'Bluetooth speaker', 'Electronics'),
       (5, 'Smartphone', 'Electronics'),
       (6, 'Wireless headphones', 'Electronics');

This query uses UNION to combine products from two tables into a single list, eliminating duplicate rows:

SELECT id, name
FROM warehouse1_products

UNION

SELECT id, name
FROM warehouse2_products;

The result looks like this:

 id |       name
----+-------------------
  1 | Wireless mouse
  5 | Smartphone
  2 | Bluetooth speaker
  3 | Coffee maker
  4 | Desk lamp
(5 rows)

This query uses UNION ALL to combine products from both tables into one list, including all duplicates:

SELECT id, name
FROM warehouse1_products

UNION ALL

SELECT id, name
FROM warehouse2_products;

The result includes duplicate rows, such as the repeated Coffee maker and Desk lamp:

 id |       name
----+-------------------
  1 | Wireless mouse
  3 | Coffee maker
  4 | Desk lamp
  3 | Coffee maker
  4 | Desk lamp
  2 | Bluetooth speaker
  5 | Smartphone
(7 rows)

This query uses INTERSECT to return only the products that appear in both tables:

SELECT id, name
FROM warehouse1_products

INTERSECT

SELECT id, name
FROM warehouse2_products;

The result looks like this:

 id |     name
----+--------------
  3 | Coffee maker
  4 | Desk lamp
(2 rows)

This query uses EXCEPT to return products that are in warehouse1_products but not in warehouse2_products:

SELECT id, name
FROM warehouse1_products

EXCEPT

SELECT id, name
FROM warehouse2_products;

The result looks as follows:

 id |       name
----+-------------------
  1 | Wireless mouse
  2 | Bluetooth speaker
(2 rows)

You can combine several set operators into a single query.

This query uses two UNION operators to combine products from three tables into a single list:

SELECT id, name
FROM warehouse1_products

UNION

SELECT id, name
FROM warehouse2_products

UNION

SELECT id, name
FROM warehouse3_products;

Output

 id |        name
----+---------------------
  3 | Coffee maker
  4 | Desk lamp
  5 | Smartphone
  6 | Wireless headphones
  2 | Bluetooth speaker
  1 | Wireless mouse
(6 rows)

This query first combines products from warehouse1_products and warehouse2_products using UNION, then removes any products that also appear in warehouse3_products using EXCEPT:

SELECT id, name
FROM warehouse1_products

UNION

SELECT id, name
FROM warehouse2_products

EXCEPT

SELECT id, name
FROM warehouse3_products;

Output

 id |     name
----+--------------
  3 | Coffee maker
  4 | Desk lamp
(2 rows)

This query first finds the common rows between warehouse2_products and warehouse3_products using INTERSECT, and then adds rows from warehouse1_products to the result using UNION:

SELECT id, name
FROM warehouse1_products

UNION

SELECT id, name
FROM warehouse2_products

INTERSECT

SELECT id, name
FROM warehouse3_products;

Output

 id |       name
----+-------------------
  3 | Coffee maker
  4 | Desk lamp
  1 | Wireless mouse
  2 | Bluetooth speaker
  5 | Smartphone
(5 rows)

Note that INTERSECT has higher precedence than UNION and EXCEPT.

Parentheses determine the order in which set operators are evaluated. Without parentheses, UNION and EXCEPT are processed from left to right. INTERSECT has higher precedence than UNION and EXCEPT. The following queries are equivalent:

Without parentheses

SELECT id, name
FROM warehouse1_products

UNION

SELECT id, name
FROM warehouse2_products

INTERSECT

SELECT id, name
FROM warehouse3_products;

With parentheses

SELECT id, name
FROM warehouse1_products

UNION

(SELECT id, name
FROM warehouse2_products

INTERSECT

SELECT id, name
FROM warehouse3_products);

Add parentheses to ensure that UNION is executed before INTERSECT:

(SELECT id, name
 FROM warehouse1_products

 UNION

 SELECT id, name
 FROM warehouse2_products)

INTERSECT

SELECT id, name
FROM warehouse3_products;

If you need to apply additional clauses (such as LIMIT) to individual queries within a set operation, enclose those queries in parentheses. Without parentheses, you may get a syntax error, or the clause might be applied to the combined result of the set operation instead of to the intended query.

This query combines all products from warehouse1_products and warehouse2_products using UNION ALL, then limits the total combined result to three rows:

(SELECT id, name
 FROM warehouse1_products

 UNION ALL

 SELECT id, name
 FROM warehouse2_products)
    LIMIT 3;

Output

 id |      name
----+----------------
  1 | Wireless mouse
  3 | Coffee maker
  4 | Desk lamp
(3 rows)

This query first selects three rows from warehouse1_products, then appends all rows from warehouse2_products using UNION ALL:

(SELECT id, name
 FROM warehouse1_products
 LIMIT 3)

UNION ALL

SELECT id, name
FROM warehouse2_products;

Output

 id |       name
----+-------------------
  3 | Coffee maker
  4 | Desk lamp
  2 | Bluetooth speaker
  5 | Smartphone
  3 | Coffee maker
  4 | Desk lamp
(6 rows)

CTE

Aggregate functions

Combine queries

Syntax overview

Prerequisites

Usage examples

UNION

INTERSECT

EXCEPT

Combine set operations

Order of evaluation

Apply modifiers to set operations