sql一个常用的两表关联查询的例子分享,涉及Left join、right join、Union、Order By、等SELECT 查询知识点

两张表A1、A2
两张表A1、A2.
要实现下面四种查询结果:
1.
这里写图片描述
2.
这里写图片描述
3.
这里写图片描述
4.
这里写图片描述

相对应代(自己写的代码经供参考):
1.
SELECT a1.id as id
, a1.name as A1
,a2.name as A2
FROM A1 a1 left join A2 a2 on a2.id=a1.id order by a1.id
2.
SELECT a2.id as id
, a1.name as A1
,a2.name as A2
FROM A1 a1 right join A2 a2 on a2.id=a1.id order by a2.id
3.
SELECT a1.id as id
, a1.name as A1
,a2.name as A2
FROM A1 a1
left join A2 a2 on a2.id=a1.id
WHERE a1.id=a2.id
4.
SELECT * FROM (SELECT a1.id as id
, a1.name as A1
,a2.name as A2
FROM A1 a1 left join A2 a2 on a2.id=a1.id order by a1.id ) A
UNION all
SELECT * FROM (SELECT a2.id as id
, a1.name as A1
,a2.name as A2
FROM A1 a1 right join A2 a2 on a2.id=a1.id WHERE a2.id =’4’ order by a2.id ) B

已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页