SQL Server INTERSECT Operator

In SQL Server, the INTERSECT operator is a kind of set operator that is used to combine the results of two SELECT statements and return rows which is common between them.

In this article, We will explore the syntax, key concepts, and practical examples of using the INTERSECT operator. Whether you are managing customer data, tracking orders, and handling student enrollments.

Here are some points that show the importance of the INTERSECT operator:

• When you are trying to identify similar rows between 2 sets of data, the INTERSECT operator comes in handy.
• Instead of writing complex WHERE clauses to find common rows between 2 sets with multiple conditions, the INTERSECT operator is used as an alternative.
• It offers better performance compared to any other methods.
• It improves the readability of the query in SQL.

Prerequisites

Make sure you understand the SQL SELECT statement and how to prepare the relevant tables or views before using the INTERSECT operator. Also, you have to make sure the columns you want to compare have matching data sets.

Key Concepts

Only the rows that are present in both result sets are returned by the INTERSECT function when it runs on two SELECT statements. The primary prerequisite is that the two SELECT queries must have the same amount of columns and data types.

SQL Server INTERSECT Operator

The SQL Server INTERSECT operator is a powerful tool that allows database developers and analysts to retrieve the common elements from two or more result sets. Unlike other set operators like UNION or EXCEPT, INTERSECT focuses on finding the shared rows among multiple queries.

Syntax:

Here’s the basic syntax:

SELECT column1, column2, …

FROM table1

INTERSECT

SELECT column1, column2, …

FROM table2;

Examples of SQL Server INTERSECT Opeartor

Example 1: Retrieving Customer Orders Using SQL Server INTERSECT

Let’s look at an example where you wish to retrieve customer orders from two tables: customers and orders. The INTERSECT operator can be used as follows,

Step 1: Create Tables

-- Create Customers table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    CustomerName VARCHAR(50)
);

-- Create Orders table
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE
);

Step 2: Insert Sample Data

-- Insert data into Customers table
INSERT INTO Customers (CustomerID, CustomerName)
VALUES
    (1, 'Tony'),
    (2, 'Steve'),
    (3, 'Peter'),
    (4, 'Bruce'),
    (5, 'Clark');

Currently, data about customers is contained in the Customers table, while data about orders placed by customers is contained in the Orders table.

Output:

Output

-- Insert data into Orders table 
INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES   
(1, 1, '2022-01-01'),     
(2, 2, '2021-02-02'),     
(3, 5, '2020-03-03'),     
(4, 1, '2022-04-04'),     
(5, 3, '2020-05-05');

Output:

Output

Step 3: Perform INTERSECT Operation

-- Find customers who have placed orders
SELECT CustomerID
FROM Customers
WHERE CustomerID IS NOT NULL
INTERSECT
SELECT CustomerID
FROM Orders
WHERE CustomerID IS NOT NULL;

Output:

Output

The output gives common data of column CustomerId in both table.

It didn’t gives 104 because that is only in Customer table and not in Order table, also 101 repeat twice in Order table but it gives only one time.

Example 2: Identifying Enrolled Students Using SQL Server INTERSECT

Let’s look at another example where we want to identify students who are enrolled in at least one course. We have two tables, Students and Courses. Follow the steps to perform the INTERSECT operation:

Step 1: Create Tables

-- Create Students table
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(50)
);


-- Create Courses table
CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    StudentID INT,
    CourseName VARCHAR(50)
);

Step 2: Insert Sample Data

-- Insert data into Students table
INSERT INTO Students (StudentID, StudentName)
VALUES
    (1, 'Harry'),
    (2, 'Ron'),
    (3, 'Hermione'),
    (4, 'Draco');

Currently, information on students is contained in the Students table, and details about courses and student enrollments are contained in the Courses table.

Output:

Output

-- Insert data into Courses table 
INSERT INTO Courses (CourseID, StudentID, CourseName) VALUES    
 (11, 1, 'Herbology '),     
(12, 3, 'Astronomy'),     
(13, 4, 'Care of Magical Creatures'),   
(14, 2, 'Alchemy'),   
(15, 1, 'Defense Against the Dark Arts');

Output:

Output

Step 3: Perform INTERSECT Operation

-- Find students who are enrolled in at least one course
SELECT StudentID
FROM Students
WHERE StudentID IS NOT NULL
INTERSECT
SELECT StudentID
FROM Courses
WHERE StudentID IS NOT NULL;

Output:

Output

The output gives common data of column StudentId in both table.

1 repeat twice in Course table but it gives only one time.

Conclusion

In conclusion, SQL Server’s INTERSECT operator offers a strong tool for finding common records between two result sets. So, when you need to identify data that overlaps across two tables or queries, it is quite helpful. The examples that I provided i.e., dealing with customer orders or student enrollments is illustrating the real-world applications of this operator. Learning how to use the INTERSECT operator in SQL Server gives you a foundational ability for effective data analysis and a methodical way to find commonalities between various tables and queries.