PostgreSQL INTERSECT Operator

Summary: in this tutorial, you’ll learn how to use the PostgreSQL INTERSECT operator to find common records of two result sets.

Getting started with PostgreSQL INTERSECT operator #

The INTERSECT operator returns the common records of result sets of two queries. In other words, it returns the intersection of two result sets.

Here’s the syntax of the INTERSECT operator:

SELECT column1, column2 
FROM table1;
INTERSECT
SELECT column1, column2 
FROM table2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

In this syntax:

• First, provide two SELECT statements that returns two result set for intersection.
• Second, use the INTERSECT operator between the two SELECT statements to find the intersection between the two result sets.

Both SELECT statements need to adhere to the following rules:

• Having the same number of columns in the result sets.
• The corresponding columns have compatible data types.

The INTERSECT operator will include distinct rows from both queries:

To include duplicate rows in the result, you can use the INTERSECT ALL operator:

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

Setting up sample tables #

Suppose we have two tables: slow_movings and flagships.

The slow_movings table stores the slow moving products and the flagships table stores the flagship products:

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 INTERSECT operator example #

The following statement uses the INTERSECT operator to find the slow-moving flagship products:

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

Try it

Output:

    name
------------
 Pixel 9
 Galaxy S24
 iPhone 16Code language: plaintext (plaintext)

PostgreSQL INTERSECT operator with the ORDER BY clause #

To sort the result set of a query that involves the INTERSECT operator, you place the ORDER BY clause in the last query:

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

For example, the following statement uses the INTERSECT ALL operator to find the slow-moving flagship products and sort them by name:

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

Try it

Output:

    name
------------
 Galaxy S24
 iPhone 16
 Pixel 9Code language: plaintext (plaintext)

PostgreSQL INTERSECT ALL operator example #

The following statement uses the INTERSECToperator to find the brands of slow-moving flagship products:

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

Output:

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

It returns three distinct brands.

To find all the brands including duplicate ones, you can use the INTERSECT ALL operator:

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

Try it

Output:

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

Summary #

• Use the PostgreSQL INTERSECT operator to return the intersection of two result sets.
• Use the INTERSECT ALL operator to include the duplicate records.
• Place the ORDER BY clause in the last query to sort the final result set returned by the INTERSECT operator.

Quiz #