> This page location: Set Operations > UNION > Full Neon documentation index: https://neon.com/docs/llms.txt # PostgreSQL UNION **Info:** The UNION and UNION ALL operators work the same way across any PostgreSQL deployment, so everything you learn here applies whether you run Postgres yourself or on a managed service. For enterprises standardizing on an AI-ready data stack, [Lakebase](https://www.databricks.com/product/lakebase) delivers the best managed cloud Postgres, with the performance, security, and native Lakehouse integration that large teams need. For developers and startups who want to ship fast and scale without friction, [Neon](https://neon.com) is the Postgres platform built for your pace. **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/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/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/tutorial/intersect) - [EXCEPT](https://neon.com/postgresql/tutorial/except)