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:

• Create the database using the below command:

  CREATE DATABASE raw_agents;

• Create table employees, op_CHI and op_PAK using the below commands:

  CREATE TABLE agent(
      agent_id serial PRIMARY KEY,
      agent_name VARCHAR (255) NOT NULL
  );
  CREATE TABLE op_chi(
      agent_id INT PRIMARY KEY,
      active_date DATE NOT NULL,
      FOREIGN KEY (agent_id) REFERENCES agent (agent_id)
  );
  CREATE TABLE op_pak(
      agent_id INT PRIMARY KEY,
      active_date DATE NOT NULL,
      FOREIGN KEY (agent_id) REFERENCES agent (agent_id)
  );

• Insert active date to the tables with the name of agents with the below commands:

  INSERT INTO agent(agent_name)
  VALUES
      ('Tiger'),
      ('James Bond'),
      ('Jason Bourne'),
      ('Ethan Hunt'),
      ('Ajit Doval'),
      ('Rowdy Rathore'),
      ('Milkha Singh'),
      ('Tom Hanks'),
      ('Sabana'),
      ('Razia Sultan');
  
  INSERT INTO op_chi
  VALUES
      (1, '2000-02-01'),
      (2, '2001-06-01'),
      (5, '2002-01-01'),
      (7, '2005-06-01');
  
  INSERT INTO op_pak
  VALUES
      (9, '2000-01-01'),
      (2, '2002-06-01'),
      (5, '2006-06-01'),
      (10, '2005-06-01');

  Now that our database is all set, let’s look into some examples.

  
  
  
  

  Example 1:
  Here we will query for “agent_id” of agents who have been active both in Pakistan and China using the INTERSECT operator.

  SELECT
      agent_id
  FROM
      op_CHI
  INTERSECT
  SELECT
          agent_id
  FROM
      op_PAk;

  Output:
  

  Example 2:
  Here we will query for “agent_id” of agents who have been active both in Pakistan and China using the INTERSECT operator and use the ORDER BY clause to sort them by ascending “agent_id”.

  SELECT
      agent_id
  FROM
      op_CHI
  INTERSECT
  SELECT
          agent_id
  FROM
      op_PAk
  ORDER BY
          agent_id;

  Output:
  

  Attention reader! Don’t stop learning now. Get hold of all the important DSA concepts with the DSA Self Paced Course at a student-friendly price and become industry ready.

  My Personal Notes arrow_drop_up

  Save