Summary -

In this topic, we described about the INTERSECT Operator with detailed example.

INTERSECT operator is used combine two or more SELECT statements from different tables to get matched DISTINCT rows in all tables. In INTERSECT operator the number of columns and DATA TYPE of columns should be same in all SELECT statements. While using INTERSECT operator we get first SELECT statement column names in the result set.

Syntax -

SELECT Expression1 [AS Alias_name], Expressio2,… 
FROM table1 [WHERE condition]
INTERSECT
SELECT Expression1, Expressio2,… FROM table2 [WHERE condition];

Example –

let us consider employee_details and employee_info tables are as follows -

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000
010 Employee6 Analyst Employee5 2019-07-11 27000.00 1000
011 Employee8 Salesman Employee5 2019-09-09 10000.00 1000

employee_info -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000
010 Employee6 Analyst Employee5 2019-07-11 27000.00 1000
011 Employee8 Salesman Employee5 2019-09-09 10000.00 1000
012 Employee4 Engineer Employee6 2019-10-09 28000.00 2000
013 Employee5 Engineer Employee7 2019-07-16 22000.00 1000

Scenario – Getting matching DISTINCT rows of multiple columns from both existing tables by using INTERSECT operator.

Requirement - Getting matching DISTINCT rows multiple columns like emp_name, designation from both employee_details and employee_info existing tables by using INTERSECT operator. The query was as follows -

SELECT emp_name, designation FROM employee_details
INTERSECT
SELECT  emp_name, designation FROM employee_info;

By executing above query we can get matched DISTINCT rows of emp_name, designation columns from both employee_details and employee_info existing tables. The output was as follows –

emp_name designation
Employee1 Director
Employee2 Director
Employee3 Manager
Employee4 Manager
Employee5 Analyst
Employee6 Analyst
Employee7 Clerk
Employee8 Salesman
Employee9 Salesman

Scenario – Getting matching DISTINCT rows of single column from both existing tables by using INTERSECT operator.

Requirement - Getting matching DISTINCT rows single column like emp_id from both employee_details and employee_info existing tables by using INTERSECT operator. The query was as follows – 

SELECT emp_id  FROM employee_details 
INTERSECT 
SELECT  emp_id AS ID FROM employee_info;

By executing above query we can get matched DISTINCT rows of emp_id column from both employee_details and employee_info existing tables. In the second SELECT statement we use alias name for emp_id as id but we get emp_id in result set because it takes first SELECT statement column names in result set by default. The output was as follows –

emp_id
001
002
003
004
005
006
007
008
009
010
011

Scenario – Getting matching DISTINCT rows of multiple columns from both existing tables by using INTERSECT operator with JOIN.

Requirement - Getting matching DISTINCT rows multiple columns like emp_name, designation from both employee_details and employee_info existing tables by using INTERSECT operator with JOIN consider we have column name emp_id as id in employee_info for this query. The query was as follows – 

SELECT emp_name,designation FROM employee_details LEFT JOIN 
employee_info ON employee_details.emp_id = employee_info.id
INTERSECT
SELECT emp_name,designation FROM employee_details RIGHT JOIN 
employee_info ON employee_details.emp_id = employee_info.id;

By executing above query we can get matched DISTINCT rows of emp_name, designation columns from both employee_details and employee_info existing tables. The output was as follows –

emp_name designation
Employee1 Director
Employee2 Director
Employee3 Manager
Employee4 Manager
Employee5 Analyst
Employee6 Analyst
Employee7 Clerk
Employee8 Salesman
Employee9 Salesman

Scenario – Getting matching DISTINCT rows of single column from both existing tables by using INTERSECT operator with WHERE condition.

Requirement - Getting matching DISTINCT rows single column like emp_id from both employee_details and employee_info existing tables by using INTERSECT operator with WHERE condition. The query was as follows – 

SELECT emp_name AS NAME FROM employee_details WHERE dept_id = 1000
UNION ALL
SELECT emp_name FROM employee_info WHERE dept_id =1000;

By executing above query we can get matched DISTINCT rows of emp_id column from both employee_details and employee_info existing tables whose dept_id is 1000. The output was as follows -

NAME
Employee1
Employee3
Employee5
Employee6
Employee7