intro
Retrieve the common records between two query results using the SQL INTERSECT operator. Learn the syntax, use cases, and best practices.
SQL provides several set operations to manipulate and analyze data. One of these is the SQL INTERSECT operator, which allows you to retrieve common rows between two query results.
In this article, you will delve into the details of the INTERSECT SQL operator and understand its syntax and usage. Join us as you dig into the INTERSECT operator in SQL!
Introduction to the SQL INTERSECT Operator
In SQL, INTERSECT is an operator that retrieves the common rows between two query results. In particular, it combines two SELECT statements and returns only the dataset that is common between the two SELECT statements.
1
An Illustration of the SQL INTERCEPT Operator
Similar to SQL EXCEPT, the INTERSECT SQL operator can only be used under these two conditions:
INTERCEPT in SQL: Operator Syntax
Following is the syntax for the SQL INTERCEPT operator:
1
SELECT column1, column2, ...
2
FROM table1
3
[WHERE condition]
4
INTERSECT
5
SELECT column1, column2, ...
6
FROM table2;
7
[WHERE condition]
Breaking down each line of the syntax:
Note that this is just an example, but you can apply the INTERSECT SQL operator also on more than two SELECT statements.
SQL INTERSECT Use Cases
In this section, you will look at some of the possible use cases of the SQL INTERSECT operator. Let’s get right into it!
Example #1
Consider a scenario where we’re to find the job titles in a company held by both males and females. First, create the HR table and then fetch the output using the SQL INTERSECT operator.
Step A: First, add an HR table to your database and populate it with some data as below:
1
CREATE TABLE HR (
2
id INT,
3
name VARCHAR(20),
4
title VARCHAR(20),
5
gender VARCHAR(1),
6
age INT
7
);
8
9
INSERT INTO HR (id, name, title, gender, age)
10
VALUES
11
(1, 'Martin', 'Sales Representative', 'M', 23),
12
(2, 'Jessica', 'Receptionist', 'F', 21),
13
(3, 'David', 'Sales Representative', 'M', 27),
14
(4, 'Carter', 'Manager', 'M', 25),
15
(5, 'Kate', 'Sales Representative', 'F', 23),
16
(6, 'Josephine', 'Manager', 'F', 26),
17
(7, 'Joel', 'Business Development', 'M', 28);
1
Creating and Populating the HR Table with DbVisualizer
Step B: By running the SQL query below, you’re going to see that the Receptionist and Business Development titles are the only titles with no male and female employees, respectively.
1
SELECT * FROM HR;
1
A Complete List of Employees
Step C: Next, implement the SQL INTERSECT operation. The expected result is that:
1
SELECT title
2
FROM HR
3
WHERE Gender = 'M'
4
INTERSECT
5
SELECT title
6
FROM HR
7
WHERE Gender = 'F';
1
Successful SQL INTERCEPT Operation
You might have asked yourself that if the AND operator can help find common data values, why use the INTERSECT operator? Well, the reason is that the SQL INTERSECT works on rows, but the AND operator on the other hand works on columns.
If you use the ANDoperator here, it won’t work at all. This is because the AND operator will fetch the results where the same row will have both male and female and so it would return no results as shown:
1
The ‘AND’ Operator Fetching No Results
Example #2
Consider a scenario where you have to identify consistent sales records between two sources for the year 2023.
Step A: First, you will create the tables Sales_2023_SourceA and Sales_2023_SourceB with identical structures, then populate them with sample data as shown:
1
CREATE TABLE Sales_2023_SourceA (
2
SaleID INT,
3
Product VARCHAR(50),
4
Amount DECIMAL(10, 2),
5
SaleDate DATE
6
);
7
INSERT INTO Sales_2023_SourceA
8
VALUES
9
(1, 'Laptop', 1200.00, '2023-01-05'),
10
(2, 'Tablet', 300.00, '2023-02-10'),
11
(3, 'Smartphone', 800.00, '2023-03-15');
12
13
CREATE TABLE Sales_2023_SourceB (
14
SaleID INT,
15
Product VARCHAR(50),
16
Amount DECIMAL(10, 2),
17
SaleDate DATE
18
);
19
INSERT INTO Sales_2023_SourceB
20
VALUES
21
(1, 'Laptop', 1200.00, '2023-01-05');
22
(2, 'Tablet', 300.00, '2023-02-10');
23
(4, 'E-Reader', 150.00, '2023-04-20');
1
Creating and Populating the Tables in DbVisualizer
Step B: Next, use the SQL INTERSECT operation to find the sales records that are consistent between Sales_2023_SourceA and Sales_2023_SourceB.
1
SELECT SaleID, Product, Amount, SaleDate
2
FROM Sales_2023_SourceA
3
INTERSECT
4
SELECT SaleID, Product, Amount, SaleDate
5
FROM Sales_2023_SourceB;
The INTERSECT operation should return the rows that are common to both Sales_2023_SourceA and Sales_2023_SourceB. Since the SaleID, Product, Amount, and SaleDate for sales records 1 and 2 match in both sources, these records will be returned by the INTERSECT operation.
1
Successful Execution of the INTERSECT Operation
This query result in DbVisualizer above helps you identify that sales records 1 and 2 are consistent across both sales sources, while clearly capitalizing on the fact that there are discrepancies or exclusive records in either source (such as the E-Reader sale in Sales_2023_SourceB and the Smartphone sale in Sales_2023_SourceA).
Best Practices and Tips for Using the INTERSECT SQL Operator
Time to look at some of the best practices that should be taken into consideration when working with the SQLINTERSECT operator:
It’s a wrap!
Conclusion
The SQL INTERSECT operator offers a precise tool for comparing and identifying common data across different datasets. To better appreciate its capabilities, you need a tool that helps you manage databases and visually explore query results. This is where a full-featured database client like DbVisualizer comes in. In addition to being able to connect to several DBMSs, it offers advanced query optimization functionality, and full support for all database features, including INTERSECT SQL operations. Download DbVisualizer for free now!
Frequently Asked Questions
What is INTERSECT in SQL?
In SQL, INTERSECT is a set operation that allows for the comparison of two SELECT queries, returning only the rows that are common to both. It effectively filters the result set to include only those records that exist in both queries, ensuring that any data returned meets the criteria specified in both SELECT statements.
What are the requirements for using the SQL INTERSECT operator?
To use the SQL INTERSECT operator, the SELECT statements must have matching column names, compatible data types, and the same number of columns.
Can INTERSECT be used with more than two tables?
Yes, INTERSECT in SQL can be used to compare more than two tables by chaining multiple INTERSECT operations.
How many DBMSs support INTERCEPT?
The SQL INTERSECT operator is supported by several relational database management systems (DBMSs). Here’s a list of some DBMSs that support it:
