Inner join和Outer join一起使用的注意点
2021-01-15 09:33 abce 阅读(639) 评论(0) 编辑 收藏 举报发现有些开发新同学,不管遇到什么sql,都是直接left 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。
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的个人信息。 这正是我们想要的结果。
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 ?
回到我们最初的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=#
那么我们该如何解决这个问题呢?
通常可能会想到使用的一种方法是将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不一样了。
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的原因)。
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=#
最好的解决方案是使用派生表来封装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=#
这就返回了准确的结果,且逻辑是精确的。不过这篇文章读起来似乎有点绕口。