Understanding SQL INTERSECT Operator

Introduction

An essential tool for data manipulation is the SQL INTERSECT. In essence, INTERSECT joins two tables and yields a result set that contains the intersection (common records) of the two tables. We will study the fundamental syntax, applications, and sample INTERSECT examples on our example table in this post.

Overview

• Understand the purpose and basic syntax of the SQL INTERSECT clause.
• Identify scenarios where the INTERSECT clause can be applied effectively.
• Implement the INTERSECT clause to find common records between two tables.
• Utilize the INTERSECT clause in conjunction with WHERE and ORDER BY clauses for advanced querying.
• Recognize the advantages of using the INTERSECT clause for simplifying complex queries and enhancing performance.

What is SQL INTERSECT?

INTERSECT only returns the rows which are present in both the result sets (result sets from SELECT statements). It is used to find the common records – some real world scenarios where INTERSECT will be used are:

• Finding common employees between different departments.
• Finding common customers between different businesses.
• Patient records which are stored across different departments.

Basic Syntax of INTERSECT

The basic way to find the intersection of two tables is to put INTERSECT between two SELECT statements. The above code is the basic syntax to find the intersection. 

SELECT column1, column2, ...
FROM table1
INTERSECT
SELECT column1, column2, ...
FROM table2;

Creation of Sample Data

Let us now learn to create sample data:

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    salary DECIMAL(10, 2)
);

CREATE TABLE Contractors (
    contractor_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    hourly_rate DECIMAL(10, 2)
);

Creation of necessary tables:

INSERT INTO Employees (employee_id, name, department, salary) VALUES
(1, 'Alice', 'HR', 60000),
(2, 'Bob', 'Engineering', 80000),
(3, 'Charlie', 'Marketing', 50000),
(4, 'David', 'Engineering', 75000);

INSERT INTO Contractors (contractor_id, name, department, hourly_rate) VALUES
(101, 'Eve', 'Engineering', 50),
(102, 'Frank', 'HR', 45),
(103, 'Grace', 'Engineering', 55),
(104, 'Charlie', 'Marketing', 60);

Inserting necessary samples into the table:

Implementation of INTERSECT

We will now implement SQL INTERSECT.

Finding Common Names Between the Table

SELECT name FROM Employees
INTERSECT
SELECT name FROM Contractors;

The common names between the two tables, Employees and Contractors, will be located by this code. The common names Alice, Bob, and Charlie are visible in the result set, as seen in the image above.

Finding Common Departments

SELECT department FROM Employees
INTERSECT
SELECT department FROM Contractors;

We notice that the result set does not include the Sales department since it is not present in the Employees table.

Combining More Columns

SELECT name, department FROM Employees
INTERSECT
SELECT name, department FROM Contractors;

We see that the result set includes records with the same name and department from both tables.

INTERSECT with WHERE clause

SELECT name 
FROM Employees 
WHERE department = 'Engineering'
INTERSECT
SELECT name 
FROM Contractors 
WHERE department = 'Engineering';

This makes our filter more advanced, we can exactly find records which satisfy our condition. We can see that a common name with Engineering department present in both the tables is Bob. 

INTERSECT with ORDER BY clause

SELECT name 
FROM Employees 
WHERE salary > 50000
INTERSECT
SELECT name 
FROM Contractors 
WHERE hourly_rate > 50
ORDER BY name;

From the statement we can see that we are able to filter our names which have a salary above 50000 and hourly rate above 50 and also present in both the tables. After all these conditions we sort them using the ORDER BY clause. We can implement complex conditions with the help of INTERSECT and other clauses. 

Advantages of SQL INTERSECT

• Simplicity: Simplifies complex queries that need to find common records.
• Efficiency: Optimized for performance, especially with indexed columns.
• Readability: Improves query readability by clearly defining the intersection operation.

Conclusion

We have seen some of the use cases, sample examples of INTERSECT. We can see that it is a very useful tool from SQL. With a good understanding of INTERSECT we can get the best out of our data finding insights, cross verification of consistency, etc. 

Frequently Asked Questions

Badrinarayan M

Data science Trainee at Analytics Vidhya, specializing in ML, DL and Gen AI. Dedicated to sharing insights through articles on these subjects. Eager to learn and contribute to the field's advancements. Passionate about leveraging data to solve complex problems and drive innovation.