发现有些开发新同学, 不管遇到什么 sql, 都是直接 left join. 一直想纠正他们的这个习惯, 但是没啥用, 没人听.
在多表连接的单个 select 语句中, 混合了 outer join 和 inner join 的时候, 最终的结果可能并不是他们真正想要的逻辑结果.
假设有下面的表和测试数据:
- CREATE TABLE people( personid serial4 PRIMARY KEY, personname VARCHAR ( 20 ) );
- CREATE TABLE pettypes( pettypeid serial4 PRIMARY KEY, pettype VARCHAR ( 10 ) );
- CREATE TABLE pets(
- petid serial4 PRIMARY KEY,
- pettypeid INT REFERENCES pettypes ( pettypeid ) NOT NULL,
- petname VARCHAR ( 10 ),
- ownerid INT REFERENCES people ( personid ) NOT NULL
- );
- insert into people (personname)
- select 'fred flintstone' union all
- select 'barney rubble' union all
- select 'george jetson';
- insert into pettypes (pettype)
- select 'dinosaur' union all
- select 'hopparoo';
- insert into pets (pettypeid, petname, ownerid)
- select 1,'dino',1 union all
- select 2,'hoppy',2;
- postgres=# select * from pettypes;
- pettypeid | pettype
- -----------+----------
- 1 | dinosaur
- 2 | hopparoo
- (2 rows)
- postgres=# select * from people;
- personid | personname
- ----------+-----------------
- 1 | fred flintstone
- 2 | barney rubble
- 3 | george jetson
- (3 rows)
- postgres=# select * from pets;
- petid | pettypeid | petname | ownerid
- -------+-----------+---------+---------
- 1 | 1 | dino | 1
- 2 | 2 | hoppy | 2
- (2 rows)
- postgres=#
每个宠物必须有一个 ownerid, 也必须属于某个类型, 即也必须有一个 pettypeid.
如果我们想查看所有人的名字以及其宠物的名字, 可以使用 left join 查看:
- postgres=# select people.personname, pets.petname
- postgres-# from people
- postgres-# left outer join pets on pets.ownerid = people.personid;
- personname | petname
- -----------------+---------
- fred flintstone | dino
- barney rubble | hoppy
- george jetson |
- (3 rows)
- postgres=#
可以看到, 虽然 George Jetson 没有养宠物, 但是 left join 还是可以返回 George Jetson 的个人信息. 这正是我们想要的结果.
现在, 假设我们还想看到每个宠物的类型, 所有宠物都必须属于某种类型. 即查看所有人的信息, 如果有养宠物, 还要查出宠物的信息, 且宠物必须要有 pettypeid.
看起来我只要在 pets 和 pettypes 两表之间增加一个 inner join 即可. 来试试:
- postgres=# select people.personname, pets.petname, pettypes.pettype
- postgres-# from people
- postgres-# left outer join pets on pets.ownerid = people.personid
- postgres-# inner join pettypes on pets.pettypeid = pettypes.pettypeid;
- personname | petname | pettype
- -----------------+---------+----------
- fred flintstone | dino | dinosaur
- barney rubble | hoppy | hopparoo
- (2 rows)
- postgres=#
为什么没有了 George Jetson ?
我们知道, 既然 pets 和 pettype 之间, 每个 pet 必须属于某个类型, 即每个 pet 在 pettype 表总会有一条对应的记录. 而且我们在 people 和 pets 之间使用了 left join 来确保无论是否养了宠物, 都会返回人的信息. 这看起来似乎是对的, 但是我们需要再仔细地想想.
回到我们最初的 left join(结果中包括 George), 并将 pets 表中的 pettypeid 列添加到查询. 可以看到 george jetson 那一行的 pettypeid 的为 null.
- postgres=# select
- postgres-# people.personname,
- postgres-# pets.petname,
- postgres-# pets.pettypeid
- postgres-# from
- postgres-# people
- postgres-# left outer join pets on pets.ownerid = people.personid;
- personname | petname | pettypeid
- -----------------+---------+-----------
- fred flintstone | dino | 1
- barney rubble | hoppy | 2
- george jetson | |
- (3 rows)
- postgres=#
在上面三表连接的例子中, 数据库先是执行 left join, 然后使用获得结果再和 pettypes 表执行 inner join.George Jetson 对应的 pettypeid 是 null, 在 pettypes 表中找不到对应的记录, 而 inner join 需要匹配的记录, 因此就只能返回两条记录了.
那么我们该如何解决这个问题呢?
通常可能会想到使用的一种方法是将 Pets 和 PetTypes 之间的内连接也更改为左外连接. 而这种用法恰恰是我们应该避免的: 这里看似解决了我们的问题. 但实际上这种改法并不等价与我们的需求.
- postgres=# SELECT
- postgres-# people.personname,
- postgres-# pets.petname,
- postgres-# pettypes.pettype
- postgres-# FROM
- postgres-# people
- postgres-# LEFT OUTER JOIN pets ON pets.ownerid = people.personid
- postgres-# LEFT OUTER JOIN pettypes ON pets.pettypeid = pettypes.pettypeid;
- personname | petname | pettype
- -----------------+---------+----------
- fred flintstone | dino | dinosaur
- barney rubble | hoppy | hopparoo
- george jetson | |
- (3 rows)
我们一开始是将 pets 和 pettypes 之间执行 inner join, 因为我们并不希望返回任何没有 pettypeid 的宠物. 而且我们是要求每个 pet 都有一个 pettypeid. 但如果 pet 的对应 pettypeid 可以为 null 呢? 上面改成 left join 后, 逻辑就和 inner join 不一样了.
为了示例, 我们创建一个新的 pets 表.
- drop table pets;
- CREATE TABLE pets(
- petid serial4 PRIMARY KEY,
- pettypeid INT REFERENCES pettypes ( pettypeid ),
- petname VARCHAR ( 10 ),
- ownerid INT REFERENCES people ( personid ) NOT NULL
- );
- insert into pets (pettypeid, petname, ownerid)
- select 1,'Dino',1 union all
- select 2,'Hoppy',2 union all
- select null,'Baby Puss',1;
- postgres=# select * from pets;
- petid | pettypeid | petname | ownerid
- -------+-----------+-----------+---------
- 1 | 1 | Dino | 1
- 2 | 2 | Hoppy | 2
- 3 | | Baby Puss | 1
- (3 rows)
- postgres=#
重新执行:(left join/ inner join)
- postgres=# select people.personname, pets.petname, pettypes.pettype
- postgres-# from people
- postgres-# left outer join pets on pets.ownerid = people.personid
- postgres-# inner join pettypes on pets.pettypeid = pettypes.pettypeid;
- personname | petname | pettype
- -----------------+---------+----------
- fred flintstone | Dino | dinosaur
- barney rubble | Hoppy | hopparoo
- (2 rows)
- postgres=#
George 仍然被排除了, 原因上面已经说过. 但是 "fred flintstone" 的宠物 "Baby Puss" 也被排除了 (inner join 的原因).
重新执行:(left join/ left join)
- postgres=# SELECT
- postgres-# people.personname,
- postgres-# pets.petname,
- postgres-# pettypes.pettype
- postgres-# FROM
- postgres-# people
- postgres-# LEFT OUTER JOIN pets ON pets.ownerid = people.personid
- postgres-# LEFT OUTER JOIN pettypes ON pets.pettypeid = pettypes.pettypeid;
- personname | petname | pettype
- -----------------+-----------+----------
- fred flintstone | Dino | dinosaur
- barney rubble | Hoppy | hopparoo
- fred flintstone | Baby Puss |
- george jetson | |
- (4 rows)
- postgres=#
"fred flintstone" 的宠物 "Baby Puss" 被查出来了. 将 inner join 改成 left join 虽然返回了想要的结果, 但是逻辑被改变了. 这样的替换虽然在大多数场景下, 或根据你的约束条件限制下能满足所有场景的需求, 但是我们必须要知道, 背后的逻辑已经完全改变了.(这里读起来有点绕, 重点就是即使将 inner join 改成 left join 后, 最终结果满足了我们的需求, 但是底层的实现逻辑已经发生改变)
最好的解决方案是使用派生表来封装 Pets 和 PetTypes 之间的 inner join. 然后, 我们只需从 People 表和派生表执行 left join. 这会返回我们正在寻找的结果:
- postgres=# SELECT
- postgres-# People.PersonName,
- postgres-# Pets.PetName,
- postgres-# Pets.PetType
- postgres-# FROM
- postgres-# People
- postgres-# LEFT OUTER JOIN ( SELECT Pets.ownerID, Pets.PetName, PetTypes.PetType FROM Pets INNER JOIN PetTypes ON Pets.PetTypeID = PetTypes.PetTypeID ) Pets ON Pets.OwnerID = People.PersonID;
- personname | petname | pettype
- -----------------+---------+----------
- fred flintstone | Dino | dinosaur
- barney rubble | Hoppy | hopparoo
- george jetson | |
- (3 rows)
- postgres=#
或者:
- postgres=# SELECT
- postgres-# People.PersonName,
- postgres-# Pets.PetName,
- postgres-# PetTypes.PetType
- postgres-# FROM
- postgres-# People
- postgres-# LEFT OUTER JOIN ( Pets INNER JOIN PetTypes ON Pets.PetTypeID = PetTypes.PetTypeID ) ON Pets.OwnerID = People.PersonID;
- personname | petname | pettype
- -----------------+---------+----------
- fred flintstone | Dino | dinosaur
- barney rubble | Hoppy | hopparoo
- george jetson | |
- (3 rows)
- postgres=#
这就返回了准确的结果, 且逻辑是精确的. 不过这篇文章读起来似乎有点绕口.
将 inner join 和 left join 一起使用的时候, 一定要想清楚逻辑, 而不是像我看的那样, 在一个含有十个 left join 的慢 sql 中, 开发不假思索地将中间的一些 left join 直接改成了 inner join, 而理由就是改了之后, 好像 sql 变快了!!!
来源: http://www.bubuko.com/infodetail-3716481.html