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:
-- 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:
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:
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:
-- 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:
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:
The output gives common data of column StudentId in both table.
1 repeat twice in Course table but it gives only one time.
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.