Movatterモバイル変換

[0]ホーム
URL:

You don’t need a vector database - just use Postgres for everything. Read the case study on switching from Pinecone to Neon
PostgreSQL Tutorial
PostgreSQL Tutorial

PostgreSQL UNION

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

Introduction to PostgreSQL UNION operator

TheUNION operator allows you to combine the result sets of two or moreSELECT statements into a single result set.

Here’s the basic syntax of theUNION operator:

SELECT select_listFROM AUNIONSELECT select_listFROM 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.

TheUNION operator removes all duplicate rows from the combined data set. To retain the duplicate rows, you use theUNION ALL instead.

Here’s the syntax of theUNION ALL operator:

SELECT select_listFROM AUNION ALLSELECT select_listFROM B;

The following Venn diagram illustrates how theUNION works:

PostgreSQL UNION with ORDER BY clause

TheUNION andUNION 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 theORDER BY clause after the second query:

SELECT select_listFROM AUNIONSELECT select_listFROM BORDER BY sort_expression;

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

Setting up sample tables

The following statements create two tablestop_rated_films andmost_popular_films, and insert data into these tables:

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 thetop_rated_films table:

SELECT * FROM top_rated_films;

Output:

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 themost_popular_films table:

SELECT * FROM most_popular_films;

Output:

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 PostgreSQLUNION operator.

1) Basic PostgreSQL UNION example

The following statement uses theUNION operator to combine data from the queries that retrieve data from thetop_rated_films andmost_popular_films:

SELECT * FROM top_rated_filmsUNIONSELECT * FROM most_popular_films;

Output:

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 theUNION operator removes two duplicate rows.

2) PostgreSQL UNION ALL example

The following statement uses theUNION ALL operator to combine result sets from queries that retrieve data fromtop_rated_films andmost_popular_films tables:

SELECT * FROM top_rated_filmsUNION ALLSELECT * FROM most_popular_films;

Output:

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 theUNION ALL operator retains the duplicate rows.

3) PostgreSQL UNION ALL with ORDER BY clause example

To sort the result returned by theUNION operator, you place theORDER BY clause after the second query:

SELECT * FROM top_rated_filmsUNION ALLSELECT * FROM most_popular_filmsORDER 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 theUNION to combine result sets of two queries and return distinct rows.
  • Use theUNION ALL to combine the result sets of two queries but retain the duplicate rows.

Last updated on

Was this page helpful?
Thank you for your feedback!
[8]ページ先頭
©2009-2025 Movatter.jp