Introduction to PostgreSQL INTERSECT operator

Like theUNION andEXCEPT operators, the PostgreSQLINTERSECT operator combines result sets of twoSELECT statements into a single result set. TheINTERSECT operator returns a result set containing rows available in both results sets.

Here is the basic syntax of theINTERSECT operator:

SELECT select_listFROM AINTERSECTSELECT select_listFROM B;

To use theINTERSECT operator, the columns that appear in theSELECT statements must follow these rules:

• The number of columns and their order in queries must be the same.
• Thedata types of the columns in the queries must be compatible.

The following diagram illustrates how theINTERSECT operator combines the result sets A and B. The final result set is represented by the yellow area where circle A intersects circle B.

PostgreSQL INTERSECT with ORDER BY clause

If you want to sort the result set returned by theINTERSECT operator, you place theORDER BY after the final query:

SELECT select_listFROM AINTERSECTSELECT select_listFROM BORDER BY sort_expression;

Setting up sample tables

We’ll create two tablestop_rated_films andmost_popular_films for demonstration:

CREATE TABLE top_rated_films(  titleVARCHAR NOT NULL,  release_yearSMALLINT);CREATE TABLE most_popular_films(  titleVARCHAR NOT NULL,  release_yearSMALLINT);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);SELECT * FROM top_rated_films;SELECT * FROM most_popular_films;

The contents of thetop_rated_films table:

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

The contents of themost_popular_films table:

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

PostgreSQL INTERSECT operator examples

Let’s explore some examples of using theINTERSECT operator.

1) Basic INTERSECT operator example

The following example uses theINTERSECT operator to retrieve the popular films that are also top-rated:

SELECT *FROM most_popular_filmsINTERSECTSELECT *FROM top_rated_films;

Output:

title      | release_year-----------------+-------------- The Godfather   |         1972 The Dark Knight |         2008(2 rows)

The result set returns one film that appears on both tables.

2) Using the INTERSECT operator with ORDER BY clause example

The following statement uses theINTERSECT operator to find the most popular films which are also the top-rated films and sort the films by release year:

SELECT *FROM most_popular_filmsINTERSECTSELECT *FROM top_rated_filmsORDER BY release_year;

Output:

title      | release_year-----------------+-------------- The Godfather   |         1972 The Dark Knight |         2008(2 rows)

Summary

• Use the PostgreSQLINTERSECT operator to combine two result sets and return a single result set containing rows appearing in both.
• Place theORDER BY clause after the second query to sort the rows in the result set returned by theINTERSECT operator.

Last updated onJuly 1, 2024

On this page

• Introduction to PostgreSQL INTERSECT operator
• Setting up sample tables
• PostgreSQL INTERSECT operator examples
• Summary