SQL INTERSECT

The INTERSECT is an operator in Structured Query Language that combines the rows of two SELECT statements and returns only those rows from the first SELECT statement, which are the same as the rows of the second SELECT statement.

In simple words, we can say that this operator shows common rows from both the SELECT statement.

This operator does not work in the MySQL database.

Syntax of INTERSECT operator:

SELECT Column_Name_1, Column_Name_2, …., Column_Name_N FROM Table_Name_1 INTERSECT SELECT 1st_Column, Column_Name_2, …., Column_Name_N FROM Table_Name_2 INTERSECT ……. INTERSECT SELECT Column_Name1, Column_Name_2, …., Column_Name_N FROM Table_Name_N ;

The data type and the number of fields must be the same for every SELECT statement connected with the INTERSECT SQL operator.

Example of INTERSECT operator in SQL

To understand the example of INTERSECT operator, we have to create the two different tables Old_Faculty_Info and New_Faculty_Info in SQL and insert the records of IIT Faculty in both tables.

The following CREATE statement creates the Old_Faculty_Info table in the IIT_College Database:

CREATE TABLE Old_Faculty_Info ( Faculty_ID INT NOT NULL PRIMARY KEY, Faculty_First_Name VARCHAR (100), Faculty_Last_Name VARCHAR (100), Faculty_Dept_Id INT NOT NULL, Faculty_Joining_Date Varchar (80), Faculty_City Varchar (80), Faculty_Salary INT );

The following INSERT queries insert the records of Faculties in the Old_Faculty_Info table:

INSERT INTO Old_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1001, Arush, Sharma, 4001, 2020-01-02, Delhi, 20000); INSERT INTO Old_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1002, Bulbul, Roy, 4002, 2019-12-31, Delhi, 38000 ); INSERT INTO Old_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1004, Saurabh, Sharma, 4001, 2020-10-10, Mumbai, 45000); INSERT INTO Old_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1005, Shivani, Singhania, 4001, 2019-07-15, Kolkata, 42000); INSERT INTO Old_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1006, Avinash, Sharma, 4002, 2019-11-11, Delhi, 28000); INSERT INTO Old_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary)VALUES (1007, Shyam, Besas, 4003, 2021-06-21, Lucknow, 35000);

The following SQL statement displays the records of the Old_Faculty_Info table on the screen:

SELECT * FROM Old_Faculty_Info;

Faculty_Id	Faculty_First_Name	Faculty_Last_Name	Faculty_Dept_Id	Faculty_Joining_Date	Faculty_City	Faculty_Salary
1001	Arush	Sharma	4001	2020-01-02	Delhi	20000
1002	Bulbul	Roy	4002	2019-12-31	Delhi	38000
1004	Saurabh	Roy	4001	2020-10-10	Mumbai	45000
1005	Shivani	Singhania	4001	2019-07-15	Kolkata	42000
1006	Avinash	Sharma	4002	2019-11-11	Delhi	28000
1007	Shyam	Besas	4003	2021-06-21	Lucknow	35000

The following CREATE statement creates the New_Faculty_Info table in theIIT_College Database:

CREATE TABLE New_Faculty_Info ( Faculty_ID INT NOT NULL PRIMARY KEY, Faculty_First_Name VARCHAR (100), Faculty_Last_Name VARCHAR (100), Faculty_Dept_Id INT NOT NULL, Faculty_Joining_Date Varchar (80), Faculty_City Varchar (80), Faculty_Salary INT );

The following INSERT queries insert the records of Faculties in the New_Faculty_Info table:

INSERT INTO New_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1010, Ankush, Roy, 4004, 2018-10-02, Delhi, 25000); INSERT INTO New_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1001, Arush, Sharma, 4001, 2020-01-02, Delhi, 20000); INSERT INTO New_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1009, Raj, Singhania, 4005, 2021-05-10, Noida, 40000); INSERT INTO New_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1005, Shivani, Singhania, 4001, 2019-07-15, Kolkata, 42000); INSERT INTO New_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary) VALUES (1008, Avinabh, Chetya, 4002, 2018-11-11, Banglore, 22000); INSERT INTO New_Faculty_Info (Faculty_ID, Faculty_First_Name, Faculty_Last_NameFaculty_Dept_Id, Faculty_Joining_Date, Faculty_City, Faculty_Salary)VALUES (1007, Shyam, Besas, 4003, 2021-06-21, Lucknow, 35000);

Step 4: View the Inserted Data

The following SELECT statement displays the data of the New_Faculty_Info table.

SELECT * FROM New_Faculty_Info;

Faculty_Id	Faculty_First_Name	Faculty_Last_Name	Faculty_Dept_Id	Faculty_Joining_Date	Faculty_City	Faculty_Salary
1010	Ankush	Roy	4004	2018-10-02	Delhi	25000
1001	Arush	Sharma	4001	2020-01-02	Delhi	20000
1009	Raj	Singhania	4005	2021-05-10	Noida	40000
1005	Shivani	Singhania	4001	2019-07-15	Kolkata	42000
1008	Avinabh	Chetya	4002	2018-11-11	Banglore	22000
1007	Shyam	Besas	4003	2021-06-21	Lucknow	35000

The following query shows the common records of Faculty from both tables:

SELECT * FROM Old_Faculty_InfoINTERSECT SELECT * FROM New_Faculty_Info;

Output:

Faculty_Id	Faculty_First_Name	Faculty_Last_Name	Faculty_Dept_Id	Faculty_Joining_Date	Faculty_City	Faculty_Salary
1001	Arush	Sharma	4001	2020-01-02	Delhi	20000
1005	Shivani	Singhania	4001	2019-07-15	Kolkata	42000
1007	Shyam	Besas	4003	2021-06-21	Lucknow	35000

INTERSECT operator with WHERE clause

The SQL WHERE clause can also be used with the INTERSECT operator for filtering the records from one or both tables.

Syntax of INTERSECT with WHERE clause

SELECT Column_Name_1, Column_Name_2 …., Column_Name_N FROM Table_Name_1 [WHERE condition] INTERSECT SELECT Column_Name1, Column_Name_2 …., Column_Name_N FROM Table_Name_2 [WHERE condition];

Example of INTERSECT with WHERE Clause

The following query shows those records of Faculties from the above tables whose salary is greater than and equal to 35000:

SELECT * FROM Old_Faculty_Info WHERE Faculty_Salary >= 35000 INTERSECT SELECT * FROM New_Faculty_Info Faculty_Salary >= 35000;

Output:

Faculty_Id	Faculty_First_Name	Faculty_Last_Name	Faculty_Dept_Id	Faculty_Joining_Date	Faculty_City	Faculty_Salary
1005	Shivani	Singhania	4001	2019-07-15	Kolkata	42000
1007	Shyam	Besas	4003	2021-06-21	Lucknow	35000

INTERSECT operator with ORDER BY clause

The SQL ORDER BY clause can also be used with the INTERSECT operator to show the records based on the specified group.

The Syntax of INTERSECT operator with ORDER BY clause is shown in the following block:

SELECT Column_Name_1, Column_Name_2 …., Column_Name_N FROM Table_Name_1 ORDER BY Column_Name; INTERSECT SELECT Column_Name1, Column_Name_2 …., Column_Name_N FROM Table_Name_2 ORDER BY Column_Name;

Example

To understand the example of Intersect operator with Order By clause, we have to create the two tables in SQL.

The following CREATE statement creates the Old_Employee_Info table in the IT_Company database:

CREATE TABLE Old_Employee_Info ( Employee_ID INT NOT NULL, Employee_Name VARCHAR (100), Employee_Gender Varchar(20), Employee_Age INT NOT NULL DEFAULT 18, Employee_Salary INT NOT NULL );

The following INSERT queries insert the records of employees in the Old_Employee_Info table:

INSERT INTO Old_Employee_Info (Employee_ID, Employee_Name, Employee_Gender, Employee_Age, Employee_Salary) VALUES (1001, Arush, Male, 18, 35000); INSERT INTO Old_Employee_Info (Employee_ID, Employee_Name, Employee_Gender, Employee_Age, Employee_Salary) VALUES (1002, Bulbul, Female, 18, 42000); INSERT INTO Old_Employee_Info (Employee_ID, Employee_Name, Employee_Gender, Employee_Age, Employee_Salary) VALUES (1004, Saurabh, Male, 20, 45000); INSERT INTO Old_Employee_Info (Employee_ID, Employee_Name, Employee_Gender, Employee_Age, Employee_Salary) VALUES (1005, Shivani, Female, 25, 28000); INSERT INTO Old_Employee_Info (Employee_ID, Employee_Name, Employee_Gender, Employee_Age, Employee_Address, Employee_Salary) VALUES (1006, Avinash, Male, 22, 38000); INSERT INTO Old_Employee_Info (Employee_ID, Employee_Name, Employee_Gender, Employee_Age, Employee_Salary) VALUES (1007, Shyam, Male, 18, 20000);

The following query shows the data of the Old_Employee_Info table.

SELECT * FROM Old_Employee_Info;

Employee_ID	Employee_Name	Employee_Gender	Employee_Age	Employee_Saalary
1001	Arush	Male	18	35000
1002	Bulbul	Female	18	42000
1004	Saurabh	Male	20	45000
1005	Shivani	Female	25	28000
1006	Avinash	Male	22	38000
1007	Shyam	Male	18	20000

The following CREATE statement creates the New_Employee_Info table in the IT_Company database:

CREATE TABLE New_Employee_Info ( Employee_ID INT NOT NULL, Employee_Name VARCHAR (100), Employee_Gender Varchar(20), Employee_Age INT NOT NULL DEFAULT 18, Employee_Salary INT NOT NULL );

The following INSERT queries insert the records of employees in the New_Employee_Info table:

INSERT INTO New_Employee_Info (Employee_ID, Employee_Name, Employee_Gender, Employee_Age, Employee_Salary) VALUES (1007, Anuj, Male, 22 49000); INSERT INTO New_Employee_Info (Employee_ID, Employee_Name, Employee_Gender, Employee_Age, Employee_Salary) VALUES (1010, Saket, Male, 29, 69000); INSERT INTO New_Employee_Info (Employee_ID, Employee_Name, Employee_Gender, Employee_Age, Employee_Salary) VALUES (1005, Shivani, Female, 25, 28000); INSERT INTO New_Employee_Info (Employee_ID, Employee_Name, Employee_Gender, Employee_Age, Employee_Address, Employee_Salary) VALUES (1006, Avinash, Male, 22, 38000); INSERT INTO New_Employee_Info (Employee_ID, Employee_Name, Employee_Gender, Employee_Age, Employee_Salary) VALUES (1009, Shyamo, Female, 18, 25000);

The following query shows the data of the New_Employee_Info table.

SELECT * FROM New_Employee_Info;

Employee_ID	Employee_Name	Employee_Gender	Employee_Age	Employee_Saalary
1007	Anuj	Male	22	49000
1010	Saket	Male	29	69000
1005	Shivani	Female	25	28000
1006	Avinash	Male	22	38000
1009	Female	Male	18	25000

The following query shows the record of common employees from both the tables in descending order:

SELECT * FROM Old_Employee_Info ORDER BY Employee_Salary DESCINTERSECT SELECT * FROM New_Employee_Info ORDER BY Employee_Salary DESC;

Employee_ID	Employee_Name	Employee_Gender	Employee_Age	Employee_Saalary
1006	Avinash	Male	22	38000
1005	Shivani	Female	25	28000

SQL INTERSECT

SQL INTERSECT

Example of INTERSECT operator in SQL

Step 4: View the Inserted Data

INTERSECT operator with WHERE clause

Example of INTERSECT with WHERE Clause

INTERSECT operator with ORDER BY clause

Example

SQL INSERT Multiple Rows

JavaScript form validation

You may also like