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:

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:

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

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

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

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

Step 4: View the Inserted Data

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

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

Output:

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

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:

Output:

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:

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:

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

The following query shows the data of the Old_Employee_Info table.

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

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

The following query shows the data of the New_Employee_Info table.

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