Skip to content
Related Articles

Related Articles

PostgreSQL – INTERSECT Operator
  • Last Updated : 28 Aug, 2020

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:

    My Personal Notes arrow_drop_up
Recommended Articles
Page :