INTERSECT in SQL: Usage and Implementation

In this blog, we will understand the INTERSECT operator in SQL, from syntax to usage. We will perform the implementation of INTERSECT in SQL using a real-life example and order the results as per the user’s requirements.

Table of Contents

• What is INTERSECT in SQL?
• Syntax and Example of INTERSECT in SQL
• Uses of INTERSECT in SQL
• INTERSECT with BETWEEN Operator
• INTERSECT with IN Operator
• INTERSECT with LIKE Operator
• Fetching Common Rows from the Same Table
• Ordering the Result
• FAQs

Watch this YouTube video tutorial to understand the basics of SQL:

What is INTERSECT in SQL?

INTERSECT in SQL is an operator used to find the common data between the tables or datasets. It combines two select statements and gives the common output between both datasets. Think of it as finding the shared information between two datasets and giving you a new result with only common records.

Things to remember while using INTERSECT in SQL:

• The number of expressions in both SELECT statements must be the same.
• The data types of the corresponding columns in each SELECT statement must be the same.
• The INTERSECT operator fetches only the common records of the SELECT statements.

Enroll in our Database Certification Courses to learn more about databases.

Syntax and Example of INTERSECT in SQL

The syntax for the INTERSECT operator in SQL is as follows:

Syntax:

SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
INTERSECT
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]

Note that the formatting of both tables is the same.

Example: To show the example of INTERSECT in SQL, we first have to create two tables. Here, we have taken the Employee1 table, which includes the employee information, and the Location table, which shows the different locations. 

CREATE TABLE Employee1(
   Emp_ID INT, 
   NAME VARCHAR(20), 
   AGE INT, 
   Designation VARCHAR(20), 
   PRIMARY KEY(Emp_ID)
);
INSERT INTO Employee1 VALUES
(1, 'Suresh', 24, 'Developer'),
(2, 'Ramdev', 26, 'Engineer'),
(3, 'Kapil', 23, 'TRA'),
(4, 'Pankaj', 25, 'Digital Marketing'),
(5, 'Raghav', 21, 'TCW'),
(6, 'Udit', 30, 'CRM');
select * from Employee1

Output: 

CREATE TABLE Employee1_designation(
   Emp_ID INT, 
   NAME VARCHAR(20) , 
   Designation VARCHAR(20), 
   AGE INT, 
   PRIMARY KEY(Emp_ID)
);
INSERT INTO Employee1_Designation VALUES
(1, 'Arun', 'Developer', 18),
(2, 'Ramdev', 'Engineer', 26),
(3, 'Ikbal', 'Marketing', 19),
(4, 'Suresh', 'TRA', 25),
(5, 'Ramkumar', 'Software Tester', 26),
(6, 'Udit', 'CRM', 23);
select *from Employee1_designation

Output:

We have created both tables. Now, we will see how the INTERSECT operator in SQL is used to give the common rows.

SELECT NAME, AGE, Designation FROM Employee1_designation
INTERSECT 
SELECT NAME, AGE, Designation FROM Employee1;

Output: 

Uses of INTERSECT in SQL

INTERSECT in SQL is used for data integrity and various other uses. Let us understand each of the uses in detail:

• Finding Common Elements: Imagine having a list of favorite movies from different people. INTERSECT helps locate movies preferred by different people, streamlining their movie night choices.
• Refining Queries: When you need specific data that meets criteria in multiple queries, INTERSECT narrows down results to precisely match those criteria.
• Removing Redundancy: If you have overlapping information from separate queries, INTERSECT assists in presenting only the unique common entries, avoiding repetition.
• Analyzing Shared Traits: It simplifies pinpointing shared attributes among various datasets, aiding in better understanding relationships between different sets of data.

Intellipaat provides a Microsoft SQL Certification Course for its learners.

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:

Let us retrieve the name, age, and designation of students aged between 25 and 30 from both the tables named Employee1 and Employee1_Designation (mentioned above), returning only the common rows within the specified age range.

Query:

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: 

INTERSECT with IN operator

The INTERSECT operator in SQL can also be used with the IN operator to find the common records that exist 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 designation of employees as ‘CRM’ as their designation in both the tables named Employee1 and Employee1_Designation (mentioned above). 

Query:

SELECT NAME, AGE, Designation FROM Employee1_designation
WHERE Designation IN('CRM')
INTERSECT
SELECT NAME, AGE, Designation FROM Employee1
WHERE Designation IN('CRM');

Output: 

INTERSECT with LIKE Operator

The LIKE operator is used to find the pattern matching in a string. We can use the INTERSECT with LIKE operator to find the common rows in the table where a particular match is in a string.

Example: 

The query below retrieves the names that start with ‘R’ using the wildcard ‘%’ in the LIKE operator from the common names of both tables.

Query:

SELECT NAME, AGE, Designation FROM Employee1_Designation
WHERE NAME LIKE 'r%'
INTERSECT
SELECT NAME, AGE, Designation FROM Employee1
WHERE NAME LIKE 'r%'

Output:

Fetching Common Rows from the Same Table

We can fetch the common rows from the same table too. Let us understand it with the help of an example.

Example:

CREATE Table Emp_details(
ID INT,
   Name VARCHAR(20),
   Designation VARCHAR(20),
   Age INT,
   Gender VARCHAR(1),
);
INSERT INTO Emp_details VALUES (1, 'Priya', 'TCW', 20, 'M');
INSERT INTO Emp_details VALUES (2, 'Sarthak', 'TL', 23, 'M');
INSERT INTO Emp_details VALUES (3, 'John', 'Executive',27, 'M');
INSERT INTO Emp_details VALUES (4, 'Poonam', 'Manager', 25,' F');
INSERT INTO Emp_details VALUES (5, 'Jenny', 'Sales Representative', 22,' F');
INSERT INTO Emp_details VALUES (6, 'Rachel', 'Manager', 29 , 'M');
INSERT INTO Emp_details VALUES (7, 'Ram', 'BDA',26 , 'M');
SELECT * FROM Emp_details;

Output:

Query to execute the INTERSECT operator in SQL to fetch common rows from the same table:

SELECT Designation
FROM Emp_details
WHERE Gender = 'M'
INTERSECT
SELECT Designation
FROM Emp_details
WHERE Gender = 'F'

Output:

To ace your interview preparation, go through these top Basic SQL Interview Questions prepared by industry experts.

Ordering the result

We can arrange the output in a particular order by any column name using the ORDER BY clause while using the INTERSECT operator in SQL.

Let us understand the use of the ORDER BY clause in SQL:

Example:

SELECT Designation
FROM Emp_details
WHERE Gender = 'M'
INTERSECT
SELECT Designation
FROM Emp_details
WHERE Gender = 'F'
ORDER BY Designation

Output:

Still have any doubts, get all your doubts cleared by visiting our Community Page for SQL.

FAQs