PostgreSQL – INTERSECT Operator

PostgreSQL has an INTERSECT operator that is used to combine two or more result sets returned by the SELECT statement and provide with the common data among the tables into a single result set.

Syntax:
SELECT
    column_list
FROM
    A
INTERSECT
SELECT
    column_list
FROM
    B;

The below rules must be followed while using the INTERSECT operator with the SELECT statement:

  • The number of columns and their order in the SELECT clauses must be the same.
  • The data types of the columns must be compatible.

The Venn diagram of an INTERSET operator in PostgreSQL is as below:

Now let’s set up a sample database to observe the implementation of the INTERSECT operator. We will create a sample database for RAW employees(say, raw_agents) and set up three tables namely agent, op_chi (operational in CHINA), and op_pak (operational in Pakistan). To do so follow the below procedures: