DbSchema Tutorial | SQL INTERSECT OPERATOR

Publish on DbSchema Blog >>>

Introduction

SQL (Structured Query Language) is a powerful tool for managing and manipulating relational databases. One of the lesser-known but highly useful operators in SQL is the INTERSECT operator. This article dives deep into the INTERSECT operator, providing a detailed explanation, its differences from other operators, and practical examples.

What is the SQL INTERSECT Operator?

The INTERSECT operator returns rows that are common between two or more result sets. Think of it as a filter that only lets through records that appear in all of the specified queries.

Syntax:

SELECT column1, column2, ...
FROM table1
INTERSECT
SELECT column1, column2, ...
FROM table2;

INTERSECT vs INNER JOIN

Though they can sometimes provide similar results, INTERSECT and INNER JOIN are fundamentally different.

Feature	INTERSECT	INNER JOIN
Purpose	Finds common rows between datasets.	Combines rows based on a condition.
Column Requirement	Columns must be of the same data type.	Columns can be different.
Result	A single set of columns with common data.	Multiple columns from both tables.
Duplication	Automatically removes duplicates from the result.	Can produce duplicate rows.

Detailed Examples with Results

Finding Common Rows in the Same Table

Consider a sample table named Students:

ID	Name	Age
1	Alice	20
2	Bob	22
3	Carol	22
4	Dave	23

To find students with the same age:

SELECT Age FROM Students WHERE Age = 22
INTERSECT
SELECT Age FROM Students WHERE Age = 23;

Result:

Age
22

Explanation:

Only the age 22 is common between the two queries.

INTERSECT with BETWEEN Operator

Using the same Students table:

SELECT Age FROM Students WHERE Age BETWEEN 20 AND 22
INTERSECT
SELECT Age FROM Students WHERE Age BETWEEN 21 AND 23;

Result:

Age
22

Explanation:

The age 22 falls within both specified age ranges.

INTERSECT with IN Operator

SELECT Age FROM Students WHERE Age IN (20, 22)
INTERSECT
SELECT Age FROM Students WHERE Age IN (22, 23);

Result:

Age
22

Explanation:

Once again, only the age 22 is common in both queries.

INTERSECT with LIKE Operator

SELECT Name FROM Students WHERE Name LIKE 'A%'
INTERSECT
SELECT Name FROM Students WHERE Name LIKE 'Al%';

Result:

Name
Alice

Explanation:

The name Alice matches both LIKE patterns.

Intersect with WHERE Clause

SELECT Name FROM Students WHERE Age > 20
INTERSECT
SELECT Name FROM Students WHERE Age < 23;

Result:

Name
Bob
Carol

Explanation:

Both Bob and Carol fit the age criteria defined in both queries.

SQL Intersect with 3 or More Tables

Suppose we have an additional table Teachers:

ID	Name	Age
1	Evan	22
2	Felicia	25
3	Gary	22
4	Helen	26

And another table Staff:

ID	Name	Age
1	Ian	22
2	Jane	28
3	Kyle	27
4	Laura	22

Now, to find the common ages among these three tables:

SELECT Age FROM Students
INTERSECT
SELECT Age FROM Teachers
INTERSECT
SELECT Age FROM Staff;

Result:

Age
22

Explanation:

The age 22 is common across all three tables.

SQL Intersect With Multiple Expressions

We’ll fetch both name and age:

SELECT Name, Age FROM Students
INTERSECT
SELECT Name, Age FROM Teachers;

Result:

Name	Age

Explanation:

There are no common name and age pairs between the two tables.

SQL Intersect Using ORDER BY Clause

(SELECT Name FROM Students)
INTERSECT
(SELECT Name FROM Teachers)
ORDER BY Name;

Result:

Name

Explanation:

No common names exist between the two tables.

Common Mistakes and Pitfalls:

1. Column Misalignment: Ensure that the order and data type of columns in both SELECT statements match.
2. Over-reliance on INTERSECT: Sometimes, a well-constructed JOIN or WHERE EXISTS might be more efficient than using INTERSECT.

FAQs:

1. How is INTERSECT different from UNION?

   • INTERSECT returns only the common rows between result sets. UNION combines the result sets and returns all distinct rows.

2. How does INTERSECT handle NULL values?

   • In the context of INTERSECT, two NULL values are considered equal.

Practice Questions:

1. Retrieve common names from Students, Teachers, and Staff tables.
2. From the Students table, find students whose names start with A and B, and intersect those results.
3. Using a hypothetical Products table, find products that have a price range intersecting between $10-$50 and $40-$80.
4. From the Students table, intersect results of students aged 22 with those whose names end with an e.

Conclusion

In conclusion, the INTERSECT operator is an incredibly useful tool to retrieve common data between result sets. It’s essential to understand its functionality and know when to use it for effective database querying.

Happy Querying !!