SQL Intersect: Your Complete Guide to Using The Operator

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.

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

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.

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

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.

Want a Top Software Development Job? Start Here!

Full Stack Developer - MERN StackExplore Program

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!