Inner join和Outer join一起使用的注意点

发现有些开发新同学，不管遇到什么sql，都是直接left join。一直想纠正他们的这个习惯，但是没啥用，没人听。

在多表连接的单个select语句中，混合了outer join和inner join的时候，最终的结果可能并不是他们真正想要的逻辑结果。

假设有下面的表和测试数据：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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;

　　

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23

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查看：

?

1 2 3 4 5 6 7 8 9 10 11

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即可。来试试：

?

1 2 3 4 5 6 7 8 9 10 11

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。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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之间的内连接也更改为左外连接。而这种用法恰恰是我们应该避免的: 这里看似解决了我们的问题。但实际上这种改法并不等价与我们的需求。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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表。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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)

?

1 2 3 4 5 6 7 8 9 10 11

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)

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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。这会返回我们正在寻找的结果:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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=#

或者：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

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变快了！！！