In SQL, we can use "Union" to merge two tables. What are different ways to do "Intersection"?
- by Jian Lin
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?
