sql 交集 差集 並集 笛卡爾積

sql 交集 差集 並集 笛卡爾積

1 交集 Join
1) inner join 其處理結果與等值/自然串連相同
如
mssql :
-- mssql 中 inner join   串連需要 on Connection條件 否則會報錯, mysql 不會,其結果等於cross join
select * from Major m  inner JOIN  
dbo.DepNmOrSubjectNm d on m.DepNoOrSubjectNo =d.DepNoOrSubjectNo
mysql:
-- 其結果等於 crose join
select * from Major m inner join  Department d ;
-- 等值串連
select m.*,d.* from Major m inner  join   Department d on m.dptId = d.dptId;
2) left join /left outer join 左串連/左外串連
在sql 中, left join 為 left outer join 的縮寫
mssql:
select * from Major m   left  outer  JOIN  
dbo.DepNmOrSubjectNm d on m.DepNoOrSubjectNo =d.DepNoOrSubjectNo
mysql:
select * from Major m   left    join `department` d on m.dptId = d.dptId;
3) right join/right outer join 右串連/右外串連
mssql:
select * from Major m   right  outer  JOIN  
dbo.DepNmOrSubjectNm d on m.DepNoOrSubjectNo =d.DepNoOrSubjectNo
mysql:
select * from Major m   right outer    join `department` d on m.dptId = d.dptId;
4) full join 全串連
mssql:
a)
select * from Major m  FULL OUTER JOIN  
dbo.DepNmOrSubjectNm d on m.DepNoOrSubjectNo =d.DepNoOrSubjectNo
b)
select * from Major m   left  outer  JOIN  
dbo.DepNmOrSubjectNm d on m.DepNoOrSubjectNo =d.DepNoOrSubjectNo
union
select * from Major m   right  outer  JOIN  
dbo.DepNmOrSubjectNm d on m.DepNoOrSubjectNo =d.DepNoOrSubjectNo
c)
select * from Major m   left  outer  JOIN  
dbo.DepNmOrSubjectNm d on m.DepNoOrSubjectNo =d.DepNoOrSubjectNo
union all
select * from Major m   right  outer  JOIN  
dbo.DepNmOrSubjectNm d on m.DepNoOrSubjectNo =d.DepNoOrSubjectNo
where m.DepNoOrSubjectNo is null
mysql:
mysql 5.0版本還不支援 full join ,但可以採用 join + union方法實現,詳細可以參考:
http://www.xaprb.com/blog/2006/05/26/how-to-write-full-outer-join-in-mysql/
--  
全串連 == 一個左串連 + 一個右串連 + union(去掉相同行)
a)select * from Major m  left outer join `department` d on m.dptId = d.dptId
union  
select * from Major m  right outer join `department` d on m.dptId = d.dptId
b)select * from Major m  left outer join `department` d on m.dptId = d.dptId
union  all
select * from Major m  right outer join `department` d on m.dptId = d.dptId
where m.dptId is null
--大力提倡使用b)
尤其是在處理很大的記錄集時, union all 不會進行排序及消除相同的行(消除相同的行可能通過第二個join的條件進行實現),所以可以節省不少時間.
2. 差集 (not in)
sql:
select * from Major m where m.dptId not in(select dptId from department )
差集中, mysql 與 mssql 語句可以直接採用 not in 來實現
3. 並集 union
mssql:
-- union  並, 預設取消相同 行
select * from Major m
union
select * from Major tm
-- union full 會有重複記錄
select * from Major m
union all
select * from Major tm
mysql:
-- union 預設會取消重複選項
select * from Major `major`
union  select * from Major mj ;
-- union all 不會取消重複選項
select * from Major m
  union  all
select * from Major tm
4. 笛卡爾積
mssql:
-- 笛卡爾
select * from Major cross  join  
dbo.DepNmOrSubjectNm
mysql:
-- 笛卡爾集
select * from Major m cross join `department` d;
帶條件的笛卡爾積與等值串連功能相同.

http://bjxagu.javaeye.com/blog/407034