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.