In SQL, we can use "Union" to merge two tables. What are different ways to do "Intersection"?

Question

In SQL, there is an operator to "Union" two tables. In an interview, I was told that, say one table has just 1 field with 1, 2, 7, 8 in it, and another table also has just 1 field with 2, and 7 in it, how do I get the intersection. I was stunned at first, because I never saw it that way.

Later on, I found that it is actually a "Join" (inner join), which is just

select * from t1, t2 where t1.number = t2.number

(although the name "join" feels more like "union" rather than "intersect")

another solution seems to be

select * from t1 INTERSECT select * from t2

but it is not supported in MySQL. Are there different ways to get the intersection besides these two methods?

Answer 1

This page explains how to implement INTERSECT and MINUS in MySQL. To implement INTERSECT you should use an inner join:

SELECT t1.number
FROM t1
INNER JOIN t2
ON t1.number = t2.number

Your code does this too, but it is not recommended to write joins like that any more.

Answer 2

An intersect is just an inner join. So

select * from t1 INTERSECT select * from t2

can be rewritten for MySQL like

select * 
from t1 
inner join t2
on t1.col1 = t2.col1
and t1.col2 = t2.col2
and t1.col3 = t2.col3
...