MySQL LEFT JOIN 左表缺失与重复

- 日理万妓 2023-07-07 12:47 17阅读 0赞

MySQL 使用LEFT JOIN 后应该得到的结果是左表所有内容，以及匹配到的右表内容(未匹配的留空)，但是在实际使用的时候会出现左表内容不全的情况，这是不应该的，出现这种情况的原因可能是筛选条件的位置放错了。

比如现在有两张表:  
sell (销售)表:

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>date</th> 
   <th>profit</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>0001</td> 
   <td>2019-01-01</td> 
   <td>3.5</td> 
  </tr> 
  <tr> 
   <td>0001</td> 
   <td>2019-01-02</td> 
   <td>3.5</td> 
  </tr> 
  <tr> 
   <td>0002</td> 
   <td>2019-01-02</td> 
   <td>5</td> 
  </tr> 
  <tr> 
   <td>0003</td> 
   <td>2019-01-01</td> 
   <td>6</td> 
  </tr> 
  <tr> 
   <td>0003</td> 
   <td>2019-01-03</td> 
   <td>3</td> 
  </tr> 
 </tbody> 
</table>

info(商品信息)表:

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>sellPrice</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>0001</td> 
   <td>洗发露</td> 
   <td>7</td> 
  </tr> 
  <tr> 
   <td>0002</td> 
   <td>火锅</td> 
   <td>10</td> 
  </tr> 
  <tr> 
   <td>0003</td> 
   <td>方便面</td> 
   <td>10</td> 
  </tr> 
 </tbody> 
</table>

很显然这两张表以id作为键连接在一起

### 正常的左连表 ###

SELECT sell.id, info.name, 
    sell.date, sell.profit FROM sell 
    LEFT JOIN info ON info.id=sell.id

这时应该得到正常的结果

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>date</th> 
   <th>profit</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>0001</td> 
   <td>洗发露</td> 
   <td>2019-01-01</td> 
   <td>3.5</td> 
  </tr> 
  <tr> 
   <td>0001</td> 
   <td>洗发露</td> 
   <td>2019-01-02</td> 
   <td>3.5</td> 
  </tr> 
  <tr> 
   <td>0002</td> 
   <td>火锅</td> 
   <td>2019-01-02</td> 
   <td>5</td> 
  </tr> 
  <tr> 
   <td>0003</td> 
   <td>方便面</td> 
   <td>2019-01-01</td> 
   <td>6</td> 
  </tr> 
  <tr> 
   <td>0003</td> 
   <td>方便面</td> 
   <td>2019-01-03</td> 
   <td>3</td> 
  </tr> 
 </tbody> 
</table>

这种id多对一的情形一般没有问题，除非你在WHERE条件中对LEFT JOIN的目标表进行了限制，这就是下面的情形

### 缺失的左表 ###

现在我想看2019-01-01这一天所有商品的利润，无论有没有利润我都想让它显示出来，所以以info表为左表

SELECT info.id, info.name, SUM(sell.profit) AS profit FROM info
    LEFT JOIN sell ON sell.id=info.id
    WHERE sell.date="2019-01-01"
    GROUP BY info.id

这样得出的结果是

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>name</th> 
   <th>profit</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>0001</td> 
   <td>洗发露</td> 
   <td>3.5</td> 
  </tr> 
  <tr> 
   <td>0003</td> 
   <td>方便面</td> 
   <td>6</td> 
  </tr> 
 </tbody> 
</table>

0002号商品不见了，因为在筛选条件`WHERE sell.date="2019-01-01"`中它被筛掉了，出来的结果就不是一张完整的左表。筛选条件的正确位置应该是LEFT JOIN 语句的ON条件或者LEFT JOIN一张临时SELECT出的表，例如  
`LEFT JOIN sell ON sell.id=info.id AND sell.date="2019-01-01"`  
或者  
`LEFT JOIN (SELECT * FROM sell WHERE sell.date="2019-01-01") lt ON lt.id=info.id`

### 多余的左表 ###

上面的缺失情况实际是种id一对多的场景，这种情况下还有可能出现左表行数比原来还要多的情况, 原因是没有进行GROUP BY聚合处理，而一个id匹配到了多个结果都被保留了下来

除此之外还有一种比较坑的情况，发生在存在多个LEFT JOIN的情况，比如在id列上同时LEFT JOIN a表和b表，都是一对多的情况。id=0005的行数匹配到了3个a表中的行和5个b表中的行，那么最后的结果是什么样子呢？如果不做聚合的话，我没记错的话会出现类似笛卡尔积的情况，即使做聚合也是对错误的结果聚合。解决办法是在LEFT JOIN中就提前做好聚合，使得匹配是一对一的。

### 总结 ###

无论多对一匹配还是一对多匹配都有可能出现因为筛选条件位置不对而引起的缺失，要注意筛选的作用范围(作用于最后结果，还是连接的临时表)，以及SQL语句的执行顺序。