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