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

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 COLUMNS
FROM TABLE1
INTERSECT
SELECT COLUMNS
FROM TABLE2

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. Let’s see, how INTERSECT Operator is used to get the common rows from the selected tables.

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.

Using INTERSECT with BETWEEN Operator

We can make use of the INTERSECT operator along with the BETWEEN operator in SQL to obtain records within a predefined range.

Example:

Let’s bring the name, age, and designation of students who are between the ages of 25 to 30 from both of the above-mentioned tables named Employee1 and Employee1_Designation. Also, it will return just the common rows within this specified age range.

Query:

SELECT name, age, designation From Employee1
Where age between 25 and 30
INTERSECT
SELECT name, age, designation From Employee1_Designation
Where age between 25 and 30;

Output: 

Using INTERSECT with IN operator

Using SQL’s INTERSECT operator along with the IN operator returns common records that may exist in the given list of values. The IN operator is used to limit the records returned by a query based on a list of specified values.

For 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: 

Using INTERSECT with LIKE Operator

The LIKE operator finds a pattern matching in the string. We can find common rows in the table where a particular match is in a string with the help of the INTERSECT with LIKE operator.

For Example: 

If you want to retrieve the names of employees starting with “R”.

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:

FAQs