> This page location: Set Operations > UNION
> Full Neon documentation index: https://neon.com/docs/llms.txt

# PostgreSQL UNION

**Summary**: in this tutorial, you will learn how to use the PostgreSQL `UNION` operator to combine result sets of multiple queries into a single result set.

## Introduction to PostgreSQL UNION operator

The `UNION` operator allows you to combine the result sets of two or more [`SELECT`](https://neon.com/postgresql/postgresql-tutorial/postgresql-select) statements into a single result set.

Here's the basic syntax of the `UNION` operator:

```sql
SELECT select_list
FROM A
UNION
SELECT select_list
FROM B;
```

In this syntax, the queries must conform to the following rules:

- The number and the order of the columns in the select list of both queries must be the same.
- The data types of the columns in select lists of the queries must be compatible.

The `UNION` operator removes all duplicate rows from the combined data set. To retain the duplicate rows, you use the `UNION ALL` instead.

Here's the syntax of the `UNION ALL` operator:

```sql
SELECT select_list
FROM A
UNION ALL
SELECT select_list
FROM B;
```

The following Venn diagram illustrates how the `UNION` works:

![](https://neon.com/postgresqltutorial/PostgresQL-UNION.png)

### PostgreSQL UNION with ORDER BY clause

The `UNION` and `UNION ALL` operators may order the rows in the final result set in an unspecified order. For example, it may place rows from the second result set before/after the row from the first result set.

To sort rows in the final result set, you specify the [`ORDER BY`](https://neon.com/postgresql/postgresql-tutorial/postgresql-order-by) clause after the second query:

```sql
SELECT select_list
FROM A
UNION
SELECT select_list
FROM B
ORDER BY sort_expression;
```

Note that if you use the `ORDER BY` clause in the first query, PostgreSQL will issue an error.

## Setting up sample tables

The following statements create two tables `top_rated_films` and `most_popular_films`, and insert data into these tables:

```sql
CREATE TABLE top_rated_films(
  title VARCHAR NOT NULL,
  release_year SMALLINT
);

CREATE TABLE most_popular_films(
  title VARCHAR NOT NULL,
  release_year SMALLINT
);

INSERT INTO top_rated_films(title, release_year)
VALUES
   ('The Shawshank Redemption', 1994),
   ('The Godfather', 1972),
   ('The Dark Knight', 2008),
   ('12 Angry Men', 1957);

INSERT INTO most_popular_films(title, release_year)
VALUES
  ('An American Pickle', 2020),
  ('The Godfather', 1972),
  ('The Dark Knight', 2008),
  ('Greyhound', 2020);
```

The following statement retrieves data from the `top_rated_films` table:

```sql
SELECT * FROM top_rated_films;
```

Output:

```text
          title           | release_year
--------------------------+--------------
 The Shawshank Redemption |         1994
 The Godfather            |         1972
 The Dark Knight          |         2008
 12 Angry Men             |         1957
(4 rows)
```

The following statement retrieves data from the `most_popular_films` table:

```sql
SELECT * FROM most_popular_films;
```

Output:

```text
       title        | release_year
--------------------+--------------
 An American Pickle |         2020
 The Godfather      |         1972
 The Dark Knight    |         2008
 Greyhound          |         2020
(4 rows)
```

## PostgreSQL UNION examples

Let's take some examples of using the PostgreSQL `UNION` operator.

### 1) Basic PostgreSQL UNION example

The following statement uses the `UNION` operator to combine data from the queries that retrieve data from the `top_rated_films` and `most_popular_films`:

```sql
SELECT * FROM top_rated_films
UNION
SELECT * FROM most_popular_films;
```

Output:

```text
          title           | release_year
--------------------------+--------------
 An American Pickle       |         2020
 The Dark Knight          |         2008
 Greyhound                |         2020
 The Shawshank Redemption |         1994
 The Godfather            |         1972
 12 Angry Men             |         1957
(6 rows)
```

The result set includes six rows because the `UNION` operator removes two duplicate rows.

### 2) PostgreSQL UNION ALL example

The following statement uses the `UNION ALL` operator to combine result sets from queries that retrieve data from `top_rated_films` and `most_popular_films` tables:

```sql
SELECT * FROM top_rated_films
UNION ALL
SELECT * FROM most_popular_films;
```

Output:

```text
          title           | release_year
--------------------------+--------------
 The Shawshank Redemption |         1994
 The Godfather            |         1972
 The Dark Knight          |         2008
 12 Angry Men             |         1957
 An American Pickle       |         2020
 The Godfather            |         1972
 The Dark Knight          |         2008
 Greyhound                |         2020
(8 rows)
```

The output indicates that the `UNION ALL` operator retains the duplicate rows.

### 3) PostgreSQL UNION ALL with ORDER BY clause example

To sort the result returned by the `UNION` operator, you place the `ORDER BY` clause after the second query:

```sql
SELECT * FROM top_rated_films
UNION ALL
SELECT * FROM most_popular_films
ORDER BY title;
```

Output:

```
          title           | release_year
--------------------------+--------------
 12 Angry Men             |         1957
 An American Pickle       |         2020
 Greyhound                |         2020
 The Dark Knight          |         2008
 The Dark Knight          |         2008
 The Godfather            |         1972
 The Godfather            |         1972
 The Shawshank Redemption |         1994
(8 rows)
```

## Summary

- Use the `UNION` to combine result sets of two queries and return distinct rows.
- Use the `UNION ALL` to combine the result sets of two queries but retain the duplicate rows.

---

## Related docs (Set Operations)

- [INTERSECT](https://neon.com/postgresql/postgresql-tutorial/postgresql-intersect)
- [EXCEPT](https://neon.com/postgresql/postgresql-tutorial/postgresql-except)