SQL - INTERSECT

In mathematical set theory, the intersection of two sets is a collection of values that are common to both sets.

In real-time scenarios, there will be a huge number of tables in a database that contains information. The user may find it challenging to gather common information from various tables. So we use the INTERSECT operator to accomplish that. It helps to retrieve the common data from various tables.

The SQL INTERSECT Operator

The INTERSECT operator in SQL is used to retrieve the records that are identical/common between the result sets of two or more tables.

Let us consider the below tables as an example to get a better understanding −

If we perform the intersection operation on both tables described above using the INTERSECT operator, it returns the common records which are Dev and Aarohi.

MySQL database does not support the INTERSECT operator. Instead of this, we can use the DISTINCT operator along with the INNER JOIN clause to retrieve common records from two or more tables.

Syntax

Following is the SQL syntax of INTERSECT operator in Microsoft SQL Server −

 SELECT column1, column2,..., columnN FROM table1, table2,..., tableN INTERSECT SELECT column1, column2,..., columnN FROM table1, table2,..., tableN 

There are some mandatory rules for INTERSECT operations such as the number of columns, data types, and other columns must be the same in both SELECT statements for the INTERSECT operator to work correctly.

Example

First of all, let us create a table named STUDENTS using the following query −

 CREATE TABLE STUDENTS( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, SUBJECT VARCHAR(20) NOT NULL, AGE INT NOT NULL, HOBBY VARCHAR(20) NOT NULL, PRIMARY KEY(ID) ); 

Let's insert some values into the table using the following query −

 INSERT INTO STUDENTS VALUES (1, 'Naina', 'Maths', 24, 'Cricket'), (2, 'Varun', 'Physics', 26, 'Football'), (3, 'Dev', 'Maths', 23, 'Cricket'), (4, 'Priya', 'Physics', 25, 'Cricket'), (5, 'Aditya', 'Chemistry', 21, 'Cricket'), (6, 'Kalyan', 'Maths', 30, 'Football'); 

The table produced is as shown below −

Now, let us create another table named STUDENTS_HOBBY using the following query −

 CREATE TABLE STUDENTS_HOBBY( ID INT NOT NULL, NAME VARCHAR(20) NOT NULL, HOBBY VARCHAR(20) NOT NULL, AGE INT NOT NULL, PRIMARY KEY(ID) ); 

Once the table is created, let us insert some values to the table using the query below −

 INSERT INTO STUDENTS_HOBBY VALUES (1, 'Vijay', 'Cricket', 18), (2, 'Varun', 'Football', 26), (3, 'Surya', 'Cricket', 19), (4, 'Karthik', 'Cricket', 25), (5, 'Sunny', 'Football', 26), (6, 'Dev', 'Cricket', 23); 

The table created is as follows −

Now, we are retrieving the common records from both the tables using the following query −

 SELECT NAME, AGE, HOBBY FROM STUDENTS_HOBBY INTERSECT SELECT NAME, AGE, HOBBY FROM STUDENTS; 

Output

When we execute the above query, the output is obtained as follows −

INTERSECT with BETWEEN Operator

We can use the INTERSECT operator with the BETWEEN operator in SQL to find records that fall within a specified range.

Example

Now, let us retrieve the name, age, and hobby of students aged between 25 and 30 from both the 'STUDENTS' and 'STUDENTS_HOBBY' tables, returning only the common rows within the specified age range −

 SELECT NAME, AGE, HOBBY FROM STUDENTS_HOBBY WHERE AGE BETWEEN 25 AND 30 INTERSECT SELECT NAME, AGE, HOBBY FROM STUDENTS WHERE AGE BETWEEN 20 AND 30; 

Output

The output for the above query is produced as given below −

INTERSECT with IN Operator

We can also use the INTERSECT operator with the IN operator in SQL to find the common records that exists in the specified list of values. The IN operator is used to filter a result set based on a list of specified values.

Example

The following SQL query returns the name, age, and hobby of students who have 'Cricket' as their hobby in both 'STUDENTS' and 'STUDENTS_HOBBY' tables −

 SELECT NAME, AGE, HOBBY FROM STUDENTS_HOBBY WHERE HOBBY IN('Cricket') INTERSECT SELECT NAME, AGE, HOBBY FROM STUDENTS WHERE HOBBY IN('Cricket'); 

Output

When we execute the above query, the output is obtained as follows −

INTERSECT with LIKE Operator

The LIKE operator is used to perform pattern matching on a string. The INTERSECT operator can also be used with the LIKE operator in SQL to find the common rows that matches with the specified pattern.

Example

The query below retrieves the names that start with 'V' using the wildcard '%' in the LIKE operator from the common names of both tables −

 SELECT NAME, AGE, HOBBY FROM STUDENTS_HOBBY WHERE NAME LIKE 'v%' INTERSECT SELECT NAME, AGE, HOBBY FROM STUDENTS WHERE NAME LIKE 'v%'; 

Output

The output for the above query is produced as given below −