mysql求交集：UNION ALL合併查詢，inner join內連線查詢，IN/EXISTS子查詢

 

1. 兩個要求交集的表（列）的結構要一致,對應的欄位數,欄位型別都應該相同；將兩個資料的資料列用 UNION ALL 關鍵字合併；將上面的所有需要比較的列 GROUP BY ；最後 HAVING COUNT(任意一列，不能多列)>1,那麼就是交集。

   SELECT a.* FROM(    SELECT * from teacher    UNION ALL    SELECT * from student)a GROUP BY a.id,a.name,a.sex HAVING COUNT(a.sex)>1

    

2. 內連線inner join。將兩個資料列，條件必須涉及需要比較的列，這裡沒有比較sex列，所以比2步多了一行資料。

   SELECT * FROM student AS a JOIN teacher AS b ON a.name =b.name AND a.ID=b.ID或者SELECT * FROM student AS a inner  JOIN teacher AS b ON a.name =b.name AND a.ID=b.ID

    
3. 帶IN關鍵字的查詢。需要比較多少列資料是相同的就必須有多少個IN。這個時候一定要控制列，列太多不建議用。

   SELECT * FROM student AS a where a.name in (select name from teacher )AND a.ID in (select ID from teacher);

    
4. 帶EXISTS關鍵字的子查詢。這個語句說白了也是通過判斷teacher中是否有滿足兩個相等的條件，來得出來的。SELECT * FROM student AS a where EXISTS (select *from teacher b where a.name =b.name AND a.ID=b.ID)