PostgreSQL - INTERSECT Operator

The PostgreSQL INTERSECT operator is used to combine two or more result sets returned by SELECT statements and return the common data among the tables into a single result set. This is useful for identifying overlapping data between tables.

Let us better understand the INTERSECT Operator of PostgreSQL from this article.

Syntax

SELECT
    column_list
FROM A
INTERSECT
SELECT
    column_list
FROM B;

Rules for Using INTERSECT

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

• Column Consistency: The number of columns and their order in the SELECT clauses must be the same. 
• Data Type Compatibility: The data types of the columns must be compatible. 

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

PostgreSQL INTERSECT Operator Examples

Now let's set up a sample database to observe the implementation of the INTERSECT operator. We will set up a database named 'raw_agents' and create three tables: 'agent', 'op_CHI' (operational in China), and 'op_PAk' (operational in Pakistan).

Create the database using the below command: 

CREATE DATABASE raw_agents;
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 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: Agents Active in Both China and Pakistan

Here we will query for "agent_id" of agents who have been active both in Pakistan and China using the INTERSECT operator.

Query: 

SELECT
    agent_id
FROM
    op_CHI
INTERSECT
SELECT
        agent_id
FROM
    op_PAk;

Output: 

Example 2: Sorted Results of Agents Active in Both China and Pakistan

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". 

Query:

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

Output: 

Important Points About PostgreSQL INTERSECT Operator

• The INTERSECT operator requires the number of columns and their order in the SELECT statements to be exactly the same. Any discrepancy will result in an error.
• The ORDER BY clause should be used only once at the end of the entire INTERSECT operation.
• PostgreSQL may use different execution plans for INTERSECT operations compared to simple SELECT queries.
• Columns compared using INTERSECT must have compatible data types. Even slight differences can cause issues. For instance, comparing a VARCHAR with a TEXT might cause an error if not explicitly cast.