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

发现有些开发新同学，不管遇到什么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变快了！！！