SQL 语法 left join on 多条件与 where 区别

ゝ一纸荒年。 2023-02-28 05:30 12阅读 0赞

left join on + 多条件与 where 的区别

**重点**

先匹配，再筛选 where 条件。

本文将通过几个例子说明两者的差别。

表 1：product

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>amount</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>100</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>200</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>300</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>400</td> 
  </tr> 
 </tbody> 
</table>

表 2：product\_details

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>weight</th> 
   <th>exist</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>2</td> 
   <td>22</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>44</td> 
   <td>1</td> 
  </tr> 
  <tr> 
   <td>5</td> 
   <td>55</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>6</td> 
   <td>66</td> 
   <td>1</td> 
  </tr> 
 </tbody> 
</table>

## 1. 单个条件 ##

select * from product a
    left join  product_details b
    on a.id  = b.id

以左表为准匹配，结果：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>amount</th> 
   <th>id</th> 
   <th>weight</th> 
   <th>exist</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>100</td> 
   <td>null</td> 
   <td>null</td> 
   <td>null</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>200</td> 
   <td>2</td> 
   <td>22</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>300</td> 
   <td>null</td> 
   <td>null</td> 
   <td>null</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>400</td> 
   <td>4</td> 
   <td>44</td> 
   <td>1</td> 
  </tr> 
 </tbody> 
</table>

## 2. 筛选条件写在 on 与 where 的区别 ##

查询 1：

SELECT * FROM product LEFT JOIN product_details
    ON (product.id = product_details.id)
    AND   product.amount=200;

结果：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>amount</th> 
   <th>id</th> 
   <th>weight</th> 
   <th>exist</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>100</td> 
   <td>null</td> 
   <td>null</td> 
   <td>null</td> 
  </tr> 
  <tr> 
   <td>2</td> 
   <td>200</td> 
   <td>2</td> 
   <td>22</td> 
   <td>0</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>300</td> 
   <td>null</td> 
   <td>null</td> 
   <td>null</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>400</td> 
   <td>null</td> 
   <td>null</td> 
   <td>null</td> 
  </tr> 
 </tbody> 
</table>

把 on 的所有条件作为匹配条件，不符合的右表都为 null。  
查询 2：

SELECT * FROM product LEFT JOIN product_details
    ON (product.id = product_details.id)
    WHERE product.amount=200;

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>amount</th> 
   <th>id</th> 
   <th>weight</th> 
   <th>exist</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>2</td> 
   <td>200</td> 
   <td>2</td> 
   <td>22</td> 
   <td>0</td> 
  </tr> 
 </tbody> 
</table>

匹配完再筛选，结果只有一条记录。

3. where XXX is null 情况

使用该语句表示：删除掉不匹配 on 后面条件的记录。  
where XXX is not null 则表示筛选出符合 on 后面条件的记录。  
常用于只需要左表的数据，比如 count id 这类。

SELECT a.* FROM product a LEFT JOIN product_details b
    ON a.id=b.id AND b.weight!=44 AND b.exist=0
    WHERE b.id IS NULL;

结果：

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>amount</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>100</td> 
  </tr> 
  <tr> 
   <td>3</td> 
   <td>300</td> 
  </tr> 
  <tr> 
   <td>4</td> 
   <td>400</td> 
  </tr> 
 </tbody> 
</table>

可以直观看出，只有 id=2 的纪录完全匹配上三个条件，所以筛除这条纪录，另三条保留，此时这三条纪录的右表均为 null。  
  
逻辑 AND 和 逻辑 OR 表达式，其操作数是从左到右求值的。如果第一个参数做够判断操作结果，那么第二个参数便不会被计算求值（短路效果）。

下面语句与该语句效果相同：（这里相同指的是最后只用到左表数据，若是将右表数据写出来是不一样的）

SELECT a.* FROM product a LEFT JOIN product_details b
    ON a.id=b.id
    WHERE b.id is null OR b.weight=44 OR b.exist=1;

将 on 的否定条件写在 where 后，效果相同。

注：  
如果使用 LEFT JOIN 来寻找在一些表中不存在的记录，则需要做下面的测试：WHERE 部分的 col\_name IS NULL，MYSQL 在查询到一条匹配 LEFT JOIN 条件后将停止搜索更多行（在一个特定的组合键下）。