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 INTERSECT 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:

My Personal Notes arrow_drop_up

Save