What is the purpose of “intersect” in SQL?

5 months ago

1 minute

In SQL, the INTERSECT operator is used to find the common rows between the result sets of two or more SELECT statements, and return the intersection of these common rows. In other words, the INTERSECT operator compares the result sets of two queries and returns the rows that exist in both result sets.

For instance, let’s say we have two tables, A and B, with Table A containing the following data:

1.
ID
Name

1. John
2. Alice
3. Bob

表B中包含的数据如下：
Table B contains the following data:

id
name
2
Alice
3
Bob
4
Mary

If we execute the following SQL query:

SELECT * FROM A
INTERSECT
SELECT * FROM B;

The result will be:

2
Alice

3
Bob

This is because there are rows with behavior IDs 2 and 3 present in both tables A and B. The INTERSECT operator only returns common rows and automatically removes duplicates, so there will be no duplicate rows in the result set.