Set theory, which is let A and B be two sets. The set consisting of all the elements belonging to set A and set B is called the intersection of set A and set B, denoted as A∩B.
The INTERSECT operator is a set operator that only returns different rows of two queries or more. In the MySQL database, INTERSECT can be used to query the intersection content.
- The Syntax of the INTERSECT Operator
(SELECT column_list
FROM table_1)
INTERSECT
(SELECT column_list
FROM table_2);
The INTERSECT operator compares the result set of two queries and returns different rows that are output by the two queries.
To use the INTERSECT operator for two queries, follow these rules:
The order and number of columns in the select list of the query must be the same.
The following graph illustrates the INTERSECT operator：
The result set produced by the left query is (1,2,3).
The correct query returns the result set (2,3,4).
The INTERSECT operator returns different rows, including the two result sets (2,3).
It should be noted that the SQL standard has three set operators, including UNION, INTERSECT, and MINUS.
INTERSECT is simulated in MySQL
Unfortunately, MySQL does not support the INTERSECT operator. However, you can simulate the INTERSECT operator.
Set Up the Sample Table
The following statement creates tables t1 and t2 and then inserts data into the two tables.
CREATE TABLE t1 (
id INT PRIMARY KEY);
CREATE TABLE t2 LIKE t1;
INSERT INTO t1(id) VALUES(1),(2),(3);
INSERT INTO t2(id) VALUES(2),(3),(4);
The following query returns the rows in the t1 table.
SELECT id FROM t1;
id
1
2
3
The following query returns row t2 in the table:
SELECT id
FROM t2;
id
---
2
3
4
- Simulate INTERSECT using DISTINCT and INNER JOIN clauses
The following statement uses the DISTINCToperator and INNER JOIN clauses to return different rows in the two tables:
SELECT DISTINCT
id
FROM t1
INNER JOIN t2 USING(id);
id
----
2
3
The INNER JOIN clause returns rows from the left and right tables. The DISTINCT operation will delete duplicate rows.
2. Simulate INTERSECT using IN and subqueries
The following statement uses the IN operator and a subquery to return the two result sets' intersection.
SELECT DISTINCT id
FROM t1
WHERE id IN (SELECT id FROM t2);
id
----
2
3
The subquery returns the first result set. The outer query uses the IN operator to select values that only exist in the first result set. The DISTINCT operator can ensure that only different values are being chosen.
No comments