(转）MSSQL 交集,差集,并集,笛卡尔积

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;