使用左外连接时,注意以下两点
- 第一张表的过滤条件写在where子句中
- 其余各张表的过滤条件写在on子句中
假设有以下两张表
A表
id | active |
---|---|
1 | 1 |
2 | 1 |
3 | 1 |
4 | 0 |
id | aid | active |
---|---|---|
11 | 1 | 1 |
12 | 1 | 0 |
22 | 2 | 1 |
23 | 2 | 1 |
两张表的id字段都是主键,B表的aid字段为指向A表的外键。
两张表中的active字段表示当前记录是否还有效。active=0表示该记录已经被逻辑删除。
现在要求使用左连接结合A表和B表,但是必须过滤掉各自表中已经被逻辑删除(即active=0)的记录。
要求的结果
A.id | A.active | B.id | B.aid | B.active |
---|---|---|---|---|
1 | 1 | 11 | 1 | 1 |
2 | 1 | 22 | 2 | 1 |
2 | 1 | 23 | 2 | 1 |
3 | 1 |
错误的SQL 1
select A.*, B.*
from A left join B
on A.id = B.aid and A.active=1 and B.active=1
A.id | A.active | B.id | B.aid | B.active |
---|---|---|---|---|
1 | 1 | 11 | 1 | 1 |
2 | 1 | 22 | 2 | 1 |
2 | 1 | 23 | 2 | 1 |
3 | 1 | |||
4 | 0 |
错误的SQL 2
select A.*, B.*
from A left join B
on A.id = B.aid
where A.active=1 and B.active=1
A.id | A.active | B.id | B.aid | B.active |
---|---|---|---|---|
1 | 1 | 11 | 1 | 1 |
2 | 1 | 22 | 2 | 1 |
2 | 1 | 23 | 2 | 1 |
正确的SQL 1
select A.*, B.*
from A left join B
on A.id = B.aid and B.active=1
where A.active=1
A.id | A.active | B.id | B.aid | B.active |
---|---|---|---|---|
1 | 1 | 11 | 1 | 1 |
2 | 1 | 22 | 2 | 1 |
2 | 1 | 23 | 2 | 1 |
3 | 1 |
正确的SQL 2
select A.*, B.*
from A left join B
on A.id = B.aid
where A.active=1
and (B.id is null or B.active=1)
A.id | A.active | B.id | B.aid | B.active |
---|---|---|---|---|
1 | 1 | 11 | 1 | 1 |
2 | 1 | 22 | 2 | 1 |
2 | 1 | 23 | 2 | 1 |
3 | 1 |
正确的SQL 3
select A.*, B.*
from (select * from A where A.active=1) A
left join (select * from B where B.active=1) B
on A.id = B.aid
A.id | A.active | B.id | B.aid | B.active |
---|---|---|---|---|
1 | 1 | 11 | 1 | 1 |
2 | 1 | 22 | 2 | 1 |
2 | 1 | 23 | 2 | 1 |
3 | 1 |