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

两张表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