PostgreSQL UNION Operator

Summary: in this tutorial, you’ll learn how to use the PostgreSQL UNION operator to combine result sets of two queries into a single result set.

Getting started with the PostgreSQL UNION operator #

The UNION operator allows you to combine the result sets of two or more queries into a single result set. Here’s the syntax of the UNION operator:

SELECT column1, column2 
FROM tableA;
UNION
SELECT column1, column2 
FROM tableB;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

First, provide two SELECT statements that you want to combine the result sets.
Second, use the UNION keyword between the two queries.

The UNION operator appends the result set of the second SELECT statement to the result set of the first SELECT statement. Additionally, it removes duplicate rows from the final result set:

Both SELECT statements must adhere to the following rules:

Return the same number of columns.
The column data types of the corresponding columns must be compatible.

Setting up sample tables #

Suppose we have two tables:

The slow_movings table stores the slow moving products
The flagships table stores the flagship products.

The slow_movings and flagships tables:

CREATE TABLE slow_movings (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  brand VARCHAR(50) NOT NULL
);

CREATE TABLE flagships (
  id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  brand VARCHAR(50) NOT NULL
);

INSERT INTO
  slow_movings (name, brand)
VALUES
  ('iPhone 16', 'Apple'),
  ('Galaxy S24', 'Samsung'),
  ('Pixel 9', 'Google'),
  ('iPhone 16 Plus', 'Apple'),
  ('Galaxy Z Flip 6', 'Samsung');

INSERT INTO
  flagships (name, brand)
VALUES
  ('iPhone 16', 'Apple'),
  ('Galaxy S24', 'Samsung'),
  ('Pixel 9', 'Google'),
  ('iPhone 16 Pro', 'Apple'),
  ('Galaxy S24 Ultra', 'Samsung');Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

PostgreSQL UNION operator example #

Suppose you want to get a list of all flagship and slow-moving products.

You can use two SELECT statements to retrieve data from the slow_movings and flagships tables. Then, use the UNION operator to append the result set of the second query to the result set of the first query:

SELECT
  name,
  brand
FROM
  slow_movings
UNION
SELECT
  name,
  brand
FROM
  flagships;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       name       |  brand
------------------+---------
 iPhone 16        | Apple
 iPhone 16 Plus   | Apple
 Galaxy Z Flip 6  | Samsung
 Galaxy S24 Ultra | Samsung
 iPhone 16 Pro    | Apple
 Galaxy S24       | Samsung
 Pixel 9          | GoogleCode language: plaintext (plaintext)

Using PostgreSQL UNION with ORDER BY clause #

To sort the result set returned by the UNION operator, you can use an ORDER BY clause. However, you need to place the ORDER BY clause at the end of the second query:

SELECT column1, column2 
FROM tableA;
UNION
SELECT column1, column2 
FROM tableB
ORDER BY column1, column2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following statement uses the UNION operator to combine the employee and contractor result sets and uses the ORDER BY clause to sort the list by names:

SELECT
  name,
  brand
FROM
  slow_movings
UNION
SELECT
  name,
  brand
FROM
  flagships
ORDER BY name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       name       |  brand
------------------+---------
 Galaxy S24       | Samsung
 Galaxy S24 Ultra | Samsung
 Galaxy Z Flip 6  | Samsung
 iPhone 16        | Apple
 iPhone 16 Plus   | Apple
 iPhone 16 Pro    | Apple
 Pixel 9          | GoogleCode language: plaintext (plaintext)

Adding columns to result sets #

Sometimes, you may want an additional column to label where a row comes from which result set. To do that, you specify the fixed value and a column alias as follows:

SELECT
  column1,
  column2,
  value AS column3
FROM
  table1
UNION
SELECT
  column1,
  column2,
  value AS column3
FROM
  table2
ORDER BY
  sort_expression;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, you can add the inventory_type column to the result set as follows:

SELECT
  name,
  brand,
  'Slow moving' inventory_type
FROM
  slow_movings
UNION
SELECT
  name,
  brand,
  'Flagship' inventory_type
FROM
  flagships
ORDER BY name;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

       name       |  brand  | inventory_type
------------------+---------+----------------
 Galaxy S24       | Samsung | Flagship
 Galaxy S24       | Samsung | Slow moving
 Galaxy S24 Ultra | Samsung | Flagship
 Galaxy Z Flip 6  | Samsung | Slow moving
 iPhone 16        | Apple   | Flagship
 iPhone 16        | Apple   | Slow moving
 iPhone 16 Plus   | Apple   | Slow moving
 iPhone 16 Pro    | Apple   | Flagship
 Pixel 9          | Google  | Flagship
 Pixel 9          | Google  | Slow movingCode language: plaintext (plaintext)

Including duplicate records with UNION ALL operator #

The following statement uses the UNION operator to combine brand names of flagship and slow moving products:

SELECT
  brand
FROM
  slow_movings
UNION
SELECT
  brand
FROM
  flagships
ORDER BY 
  brand;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

  brand
---------
 Apple
 Google
 SamsungCode language: plaintext (plaintext)

The final result set includes only three distinct brand names.

The reason is that the UNION operator removes one duplicate brands names. To keep the duplicate rows, you can use the UNION ALL operator.

PostgreSQL UNION ALL operator #

The UNION ALL operator appends the second query’s result set to the first query’s result set and keep the duplicate row in the final result set:

SELECT column1, column2 
FROM tableA;
UNION ALL
SELECT column1, column2 
FROM tableB;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

For example, the following statement uses the UNION ALL operator to retrieve all employee and contractor names:

SELECT
  brand
FROM
  slow_movings
UNION ALL
SELECT
  brand
FROM
  flagships
ORDER BY 
  brand;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Try it

Output:

  brand
---------
 Apple
 Apple
 Apple
 Apple
 Google
 Google
 Samsung
 Samsung
 Samsung
 SamsungCode language: plaintext (plaintext)

Summary #

Use the UNION operator to append the result set of a query to the result set of another query and remove the duplicate rows in the final result set.
Use the UNION ALL operator to keep the duplicate rows
Place the ORDER BY clause at the end of the second query to sort the rows in the final result set.

Quiz #

Was this tutorial helpful ?