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;