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

STUDENT_ID	STUDENT_NAME	PHYSICS_MARKS	CHEMISTRY_MARKS	MATHS_MARKS	TOTAL_MARKS
1	NEHA	85	88	100	273
2	VISHAL	70	90	82	242
3	SAMKEET	75	88	96	259
4	NIKHIL	60	75	80	215
5	YOGESH	56	65	78	199
6	ANKITA	95	85	96	276
7	SONAM	98	89	100	287
8	VINEET	85	90	100	275
9	SANKET	86	78	65	229
10	PRACHI	90	80	75	245
101	SNEHA	85	88	100	273
103	VISHAL	75	88	96	259

Table 2: Stud

STUDENT_ID	STUDENT_NAME	PHYSICS_MARKS	CHEMISTRY_MARKS	MATHS_MARKS	TOTAL_MARKS
4	NIKHIL	60	75	80	215
6	ANKITA	95	85	96	276
7	SONAM	98	89	100	287
9	SANKET	86	78	65	229
101	SNEHA	85	88	100	273
102	SAMKEET	70	90	82	242
103	VISHAL	75	88	96	259
105	YOGESHWARI	56	65	78	199
106	VINAY	85	90	100	275
107	PRASHAKA	90	80	75	245
8	VINEET	85	90	100	275

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:

STUDENT_ID	STUDENT_NAME	PHYSICS_MARKS	CHEMISTRY_MARKS	MATHS_MARKS	TOTAL_MARKS
4	NIKHIL	60	75	80	215
6	ANKITA	95	85	96	276
7	SONAM	98	89	100	287
8	VINEET	85	90	100	275
9	SANKET	86	78	65	229
101	SNEHA	85	88	100	273
103	VISHAL	75	88	96	259

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:

STUDENT_ID	STUDENT_NAME	PHYSICS_MARKS	CHEMISTRY_MARKS	MATHS_MARKS	TOTAL_MARKS
7	SONAM	98	89	100	287
8	VINEET	85	90	100	275
101	SNEHA	85	88	100	273

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:

STUDENT_ID	STUDENT_NAME	PHYSICS_MARKS	CHEMISTRY_MARKS	MATHS_MARKS	TOTAL_MARKS
6	ANKITA	95	85	96	276
7	SONAM	98	89	100	287
8	VINEET	85	90	100	275
101	SNEHA	85	88	100	273
103	VISHAL	75	88	96	259

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:

STUDENT_ID	STUDENT_NAME	PHYSICS_MARKS	CHEMISTRY_MARKS	MATHS_MARKS	TOTAL_MARKS
6	ANKITA	95	85	96	276
7	SONAM	98	89	100	287
8	VINEET	85	90	100	275
101	SNEHA	85	88	100	273

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.