SQL INTERSECT

SQL intersect operator is used to combine two or more SELECT statements, but it only displays the data similar to the SELECT statement.

The syntax for the INTERSECT operation:

SELECT COLUMN_NAME1, COLUMN_NAME2, COLUMN_NAME3 FROM TABLE_NAME1 INTERSECT SELECT COLUMN_NAME1, COLUMN_NAME2, COLUMN_NAME3 FROM TABLE_NAME2;

Rules to be followed using INTERSECT operator are as follows:

1. The number of Columns and Order of Columns must be same.
2. Data Type must be Compatible.

Let's understand the concept of SQL INTERSECT with the help of examples.

Consider the following tables along with the given records.

Table 1: Student

Table 2: Stud

Example 1: Execute a query to perform INTERSECT operation between Student table and Stud table.

SELECT * FROM STUDENT INTERSECT SELECT * FROM STUD;

In the above query, we have used two SELECT queries. The First SELECT query fetches the data from the Student table. It performs INTERSECT operation with the data fetched by the Second SELECT query that retrieves the data from the Stud table. Only similar records between these two tables are selected.

The output from the above query is:

Only common records between the Student Table and the Stud tables are displayed.

Example 2: Execute a query to perform INTERSECT operation between Student table and Stud table but display only those Student records from the Stud table where maths marks are equal to 100.

SELECT * FROM STUDENT INTERSECT SELECT * FROM STUD WHERE MATHS_MARKS = 100;

The output from the above query is:

Only those students' records are displayed whose math marks are equal to 100 from the Stud table and are common between both the Student and the Stud table.

Example 3: Execute a query to perform INTERSECT operation between Student table and Stud table but display only those Student records from the Student table where chemistry marks are greater than 80.

SELECT * FROM STUDENT WHERE CHEMISTRY_MARKS > 80 INTERSECT SELECT * FROM STUD;

The output from the above query is:

Only those students' records are displayed whose chemistry marks are greater than 80 from the Student Table and are common between both the Student and the Stud table.

Example 4: Execute a query to perform INTERSECT operation between Student table and Stud table and display only those Student records where physics marks are greater than 75 from the Student Table, and second select queries that display only those Student records where maths marks are greater than 90 from the Stud.

SELECT * FROM STUDENT WHERE PHYSICS_MARKS > 75 INTERSECT SELECT * FROM STUD WHERE MATHS_MARKS > 90;

The output from the above query is:

Only those students' records are displayed whose physics marks are greater than 75 from the Student Table, and math marks are greater than 90 from the Stud Table and are common between both the Student and the Stud table.