MySQL 交集的寫法

一直以為自己寫過這個記錄, 今天來這裡查卻找不到, 真是太神祕了。

參照這篇的作法, 用 UNION ALL + GROUP BY + HAVING COUNT(*) = 2 就可以做到交集的效果。

SELECT * FROM (
             SELECT DISTINCT col1 FROM t1 WHERE...
             UNION ALL
             SELECT DISTINCT col1 FROM t1 WHERE...
) AS tbl
GROUP BY tbl.col1
HAVING COUNT(*) = 2

附帶一提, 聯集自然是用 UNION, 差集可以用 sub query

SELECT * FROM T1 WHERE ... AND id NOT IN (SELECT id FROM T2 WHERE  ...)