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.

SQLINTERSECT_1

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.

FREE Course: Introduction to Data Analytics

Mastery to Data Analytics Basics is a Click Away!Start Learning
FREE Course: Introduction to Data Analytics

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:

SQLINTERSECT_2

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:

SQLINTERSECT_3

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:

SQLINTERSECT_4

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:

SQLINTERSECT_5.

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:

SQLINTERSECT_6.

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.

Post Graduate Program in Business Analysis

In partnership with Purdue UniversityVIEW COURSE
Post Graduate Program in Business Analysis

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:

SQLINTERSECT_7.

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:

SQLINTERSECT_8.

As you can see in the output, it was alphabetically ordered based on the title.

Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Certification Program. Enroll now!

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!

About the Author

SimplilearnSimplilearn

Simplilearn is one of the world’s leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.

View More
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.