SQL INTERSECT operator combines two select statements and returns only the dataset that is common in both the statements. To put it simply, it acts as a mathematical intersection. In mathematics, the intersection of A and B is the common data present in both A and B. Thus, when you provide two select queries to combine, the SQL INTERSECT will only return the common rows from both the SELECT queries. Similar to the UNION operator in SQL, even the INTERSECT operator has to abide by the following two rules for successful execution:
- The number and order of columns in both queries has to be the same
- The data types of corresponding columns from both the select queries must be compatible with each other
The diagram depicted below will give you a better understanding.
As the diagram shows, when you intersect query 1 (green) and query 2 (blue), the result will be the intersection of the two, the grey color in the above diagram.
How to Use the SQL INTERSECT Operator?
You can use the SQL INTERSECT operator to combine rows from two queries using the following syntax.
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
INTERSECT
SELECT column1 [, column2 ]
FROM table1 [, table2 ]
[WHERE condition]
As you can see in the above syntax, the number of columns and their data types need to be the same for the SQL INTERSECT to work efficiently.
SQL INTERSECT Operator Example
Using the above syntax, you can use the SQL INTERSECT Operator. Here’s an SQL Server INTERSECT example that will help you grasp this concept.
First, you will begin by creating two tables that you will use in the example and insert some values into them.
CREATE TABLE Customers(
ID INT PRIMARY KEY,
Name VARCHAR(20),
Country VARCHAR(20),
City VARCHAR(20)
);
INSERT INTO Customers VALUES (1, 'Aakash', 'INDIA', 'Mumbai');
INSERT INTO Customers VALUES (2, 'George', 'USA', 'New York');
INSERT INTO Customers VALUES (3, 'David', 'INDIA', 'Bangalore');
INSERT INTO Customers VALUES (4, 'Leo', 'SPAIN', 'Madrid');
INSERT INTO Customers VALUES (5, 'Rahul', 'INDIA', 'Delhi');
INSERT INTO Customers VALUES (6, 'Brian', 'USA', 'Chicago');
INSERT INTO Customers VALUES (7, 'Justin', 'SPAIN', 'Barcelona');
SELECT * FROM Customers;
Output:
This will create the Customers table, with ID, Name, Country, and City columns. The second table will be the Branches table, with the info about a store’s branches and include Branch_Code, Country, and City columns. Here’s how you can create the table.
CREATE TABLE Branches(
Branch_Code INT PRIMARY KEY,
Country VARCHAR(20),
City VARCHAR(20)
);
INSERT INTO Branches VALUES (101, 'INDIA', 'Mumbai');
INSERT INTO Branches VALUES (201, 'INDIA', 'Bangalore');
INSERT INTO Branches VALUES (301, 'USA', 'Chicago');
INSERT INTO Branches VALUES (401, 'USA', 'New York');
INSERT INTO Branches VALUES (501, 'SPAIN', 'Madrid');
SELECT * FROM Branches;
Output:
Now that both the tables are created, use the SQL INTERSECT operator to see how it operates to give the common rows.
SELECT Country, City
FROM Customers
INTERSECT
SELECT Country, City
FROM Branches
ORDER BY City;
Output:
The first query will select countries and cities from all the rows of the Customers table in the above example. Similarly, the second query will select all the countries and cities from the Branches table. When you use the SQL INTERSECT operator here, it will fetch and provide only the common values from both queries. Hence, the query does not include the city of Delhi and Barcelona in the output. That’s because those are the two cities that are not available in the Branches table.
Note: This example has also used the ORDER BY clause in the example to sort the results.
What Are the Uses of SQL INTERSECT Operator?
The SQL INTERSECT operator is useful in several situations. It is best when you want to fetch common rows between the results of two select statements or queries.
You might think that the AND operator can help find common data values, then why use the INTERSECT operator?
The reason is that the SQL INTERSECT works on rows, but the AND operator works on columns. You will go through this in detail while exploring the uses of the SQL INTERSECT operator. You have already seen the benefit of intersecting two tables in the previous example, where you used the operator on the Customers and Branches table to intersect and find the common cities. The other use is:
Finding Common Rows in the Same Table
For this example, let’s try to find the job titles in a company held by both males and females. You will start by creating the HR table and then fetch the output using the SQL INTERSECT operator.
CREATE Table HR(
ID INT,
Name VARCHAR(20),
Title VARCHAR(20),
Gender VARCHAR(1),
Age INT
);
INSERT INTO HR VALUES (1, 'George', 'Sales Representative', 'M', 23);
INSERT INTO HR VALUES (2, 'Divya', 'Receptionist', 'F', 21);
INSERT INTO HR VALUES (3, 'David', 'Sales Representative', 'M', 27);
INSERT INTO HR VALUES (4, 'Kailash', 'Manager', 'M', 25);
INSERT INTO HR VALUES (5, 'Jenifer', 'Sales Representative', 'F', 23);
INSERT INTO HR VALUES (6, 'Rachel', 'Manager', 'F', 26);
INSERT INTO HR VALUES (7, 'Aakash', 'Business Development', 'M', 28);
SELECT * FROM HR;
Output:
As you can see in the output, Receptionist and Business Development are the only two titles that don’t have male and female employees. Let’s see if the INTERSECT operator can fetch the results correctly.
SELECT title
FROM HR
WHERE Gender = 'M'
INTERSECT
SELECT title
FROM HR
WHERE Gender = 'F'
Output:
As you can see, the INTERSECT operator correctly fetched the expected results. If you have used the AND operator here, it wouldn’t have worked at all. That’s because the AND operator would have fetched the results where the same row would have had both male and female. Hence, you wouldn’t have got any output.
SELECT title
FROM HR
WHERE gender = 'M'
AND gender = 'F';
There will be no output for this query.
Ordering the Result of Output
You can use the ORDER BY clause to order the output in ascending order. You will first add a row with a title receptionist and gender male in our HR table to see it in action.
INSERT INTO HR VALUES (8, 'Brian', ‘Receptionist', 'M', 32);
SELECT * FROM HR
WHERE ID = 8;
Output:
As the output confirms, it added the new row to the table. Now, you will use the SQL INTERSECT operator with the ORDER BY clause and see how it orders the output.
SELECT title
FROM HR
WHERE Gender = 'M'
INTERSECT
SELECT title
FROM HR
WHERE Gender = 'F'
ORDER BY title;
Output:
As you can see in the output, it was alphabetically ordered based on the title.
Conclusion
In this article, you have learned about the SQL INTERSECT operator along with examples. It functions similarly to a mathematical intersection and fetches the rows with common column values. You can now tweak the codes and enlarge the tables by inserting more rows to play with the INTERSECT operator. If you want to learn more about such fundamental concepts, you can opt for Simplilearn’s SQL Certification Training Course. The course provides you with self-paced learning materials and in-depth knowledge of all the essential basic and advanced SQL concepts. Taking the course will get you a globally recognized certification and help you excel in SQL database management.
If you have any questions on this “SQL intersect” tutorial for us, feel free to share them in the comments at the bottom of this page. Our experts will revert on the same, at the earliest!