sql(join中on与where区别)

悠悠 2021-09-26 00:16 369阅读 0赞

**准确的说，ON一般只用在有多个表连接的情况下（也即是，用在JOIN子句中）**

**而WHERE则没有限制，只要想用这个限制条件，可以用在任何情况下**

left join :左连接，返回左表中所有的记录以及右表中连接字段相等的记录。

right join :右连接，返回右表中所有的记录以及左表中连接字段相等的记录。

inner join: 内连接，又叫等值连接，只返回两个表中连接字段相等的行。

full join:外连接，返回两个表中的行：left join + right join。

cross join:结果是笛卡尔积，就是第一个表的行数乘以第二个表的行数。

**关键字: on**

数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户。

在使用left jion时，on和where条件的区别如下：

1、 on条件是在生成临时表时使用的条件，它不管on中的条件是否为真，都会返回左边表中的记录。

2、where条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有left join的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

假设有两张表：

表1：tab2

表2：tab2

两条SQL:  
1、select \* form tab1 left join tab2   **on **(tab1.size = tab2.size)    **where** tab2.name=’AAA’  
2、select \* form tab1 left join tab2 **on** (tab1.size = tab2.size and tab2.name=’AAA’)

<table> 
 <tbody> 
  <tr> 
   <td><span style="font-size:18px">第一条SQL的过程：</span> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> </p> 
    <table> 
     <tbody> 
      <tr> 
       <td><span style="font-size:18px">1、中间表<br> on条件:<br> tab1.size&nbsp;=&nbsp;tab2.size</span></td> 
       <td> 
        <table> 
         <tbody> 
          <tr> 
           <td><span style="font-size:18px">tab1.id</span></td> 
           <td><span style="font-size:18px">tab1.size</span></td> 
           <td><span style="font-size:18px">tab2.size</span></td> 
           <td><span style="font-size:18px">tab2.name</span></td> 
          </tr> 
          <tr> 
           <td><span style="font-size:18px">1</span></td> 
           <td><span style="font-size:18px">10</span></td> 
           <td><span style="font-size:18px">10</span></td> 
           <td><span style="font-size:18px">AAA</span></td> 
          </tr> 
          <tr> 
           <td><span style="font-size:18px">2</span></td> 
           <td><span style="font-size:18px">20</span></td> 
           <td><span style="font-size:18px">20</span></td> 
           <td><span style="font-size:18px">BBB</span></td> 
          </tr> 
          <tr> 
           <td><span style="font-size:18px">2</span></td> 
           <td><span style="font-size:18px">20</span></td> 
           <td><span style="font-size:18px">20</span></td> 
           <td><span style="font-size:18px">CCC</span></td> 
          </tr> 
          <tr> 
           <td><span style="font-size:18px">3</span></td> 
           <td><span style="font-size:18px">30</span></td> 
           <td><span style="font-size:18px">(null)</span></td> 
           <td><span style="font-size:18px">(null)</span></td> 
          </tr> 
         </tbody> 
        </table> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:18px">&nbsp;</span></p> </td> 
      </tr> 
      <tr> 
       <td><span style="font-size:18px"><br> </span></td> 
       <td><span style="font-size:18px"><br> </span></td> 
      </tr> 
      <tr> 
       <td><span style="font-size:18px">2、再对中间表过滤<br> where&nbsp;条件：<br> tab2.name=’AAA’</span></td> 
       <td> 
        <table> 
         <tbody> 
          <tr> 
           <td><span style="font-size:18px">tab1.id</span></td> 
           <td><span style="font-size:18px">tab1.size</span></td> 
           <td><span style="font-size:18px">tab2.size</span></td> 
           <td><span style="font-size:18px">tab2.name</span></td> 
          </tr> 
          <tr> 
           <td><span style="font-size:18px">1</span></td> 
           <td><span style="font-size:18px">10</span></td> 
           <td><span style="font-size:18px">10</span></td> 
           <td><span style="font-size:18px">AAA</span></td> 
          </tr> 
         </tbody> 
        </table> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:18px">&nbsp;</span></p> </td> 
      </tr> 
      <tr> 
       <td><span style="font-size:18px"><br> </span></td> 
       <td><span style="font-size:18px"><br> </span></td> 
      </tr> 
     </tbody> 
    </table> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:18px">&nbsp;</span></p> </td> 
  </tr> 
 </tbody> 
</table>

<table> 
 <tbody> 
  <tr> 
   <td><span style="font-size:18px">第二条SQL的过程：</span> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> </p> 
    <table> 
     <tbody> 
      <tr> 
       <td><span style="font-size:18px">1、中间表<br> on条件:<br> tab1.size&nbsp;=&nbsp;tab2.size&nbsp;and&nbsp;tab2.name=’AAA’<br> (条件不为真也会返回左表中的记录)</span></td> 
       <td> 
        <table> 
         <tbody> 
          <tr> 
           <td><span style="font-size:18px">tab1.id</span></td> 
           <td><span style="font-size:18px">tab1.size</span></td> 
           <td><span style="font-size:18px">tab2.size</span></td> 
           <td><span style="font-size:18px">tab2.name</span></td> 
          </tr> 
          <tr> 
           <td><span style="font-size:18px">1</span></td> 
           <td><span style="font-size:18px">10</span></td> 
           <td><span style="font-size:18px">10</span></td> 
           <td><span style="font-size:18px">AAA</span></td> 
          </tr> 
          <tr> 
           <td><span style="font-size:18px">2</span></td> 
           <td><span style="font-size:18px">20</span></td> 
           <td><span style="font-size:18px">(null)</span></td> 
           <td><span style="font-size:18px">(null)</span></td> 
          </tr> 
          <tr> 
           <td><span style="font-size:18px">3</span></td> 
           <td><span style="font-size:18px">30</span></td> 
           <td><span style="font-size:18px">(null)</span></td> 
           <td><span style="font-size:18px">(null)</span></td> 
          </tr> 
         </tbody> 
        </table> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:18px">&nbsp;</span></p> </td> 
      </tr> 
     </tbody> 
    </table> <p style="margin-top:0px; margin-bottom:0px; padding-top:0px; padding-bottom:0px"> <span style="font-size:18px">&nbsp;</span></p> </td> 
  </tr> 
 </tbody> 
</table>

其实以上结果的关键原因就是left join,right join,full join的特殊性，不管on上的条件是否为真都会返回left或right表中的记录，full则具有left和right的特性的并集。 而inner join没这个特殊性，则条件放在on中和where中，返回的结果集是相同的。