INTERSECT Operator in SQL

For Complete YouTube Video: Click Here

In this class, we will understand INTERSECT Operator in SQL.

We have already discussed SQL Query 2.

The understanding of SQL Query 2 is essential.

We will discuss this class based on the assumption that you have understood the SQL query 2 class.

Table of Contents

To understand this class, let us consider the query as shown below.

Write a query to find the names of the departments located in HOUSTON and SUGARLAND.

To get the output for this query, we will use the following tables from the company database.

The cartesian product of both the tables is as shown below.

Before understanding the use of intersection in SQL, we consider the ‘intersection’ in sets.

For example, A and B are two sets.

A = {1, 2, 3, 4}, B = {3, 4, 5}.

A intersection B = {3}

We use the intersection to find the common elements of A and B.

In our case, the common element is 3.

Considering the above query to find the departments located in Houston and Sugarland, generally, we will write the query as shown below.

SELECT D.dname FROM department D, dept_locations L WHERE D.dnumber = L.dnumber AND L.dlocation = ‘Houston’ AND L.dlocation = ‘Sugarland’;

The output of the query is as shown below.

SQL selects no rows.

From the above tables, we can see that the Research department is in Houston and Sugarland.

We can use this kind of query with INTERSECT.

The query is as shown below.

SELECT D.dname FROM department D, dept_locations L WHERE D.dnumber = L.dnumber AND L.dlocation = ‘Houston’

INTERSECT

SELECT D.dname FROM department D, dept_locations L WHERE D.dnumber = L.dnumber AND L.dlocation = ‘Sugarland’;

First, the query with dlocation = ‘Houston’ will get executed.

The output of the query is Research and Headquarters.

Next, the query with dlocation = ‘Sugarland’ is executed.

The output of the query is Research.

Now, the INTERSECT of both queries will get produced as the output.

The output is the Research department.

The output on the SQL is as shown below.