sql 交集差集并集笛卡尔积

发布时间：2023-02-28 13:46:43 所属栏目：MsSql教程来源：

导读：sql 交集差集并集笛卡尔积

1 交集 Join
1) inner join 其处理结果与等值/自然连接相同
如
mssql :
-- mssql 中 inner join 连接需要 on Connection条件否则会报错, MysqL 不会,其结果等于cross join
s

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方法实现,详细可以参考:
--
全连接 == 一个左连接 + 一个右连接 + 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;
带条件的笛卡尔积与等值连接功能相同.

（编辑：汽车网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

sql 交集 差集 并集 笛卡尔积

sql 交集差集并集笛卡尔积