Where, on when MySQL external connection is used

elicit questions

Example ：

1. Query the product information without sales records , And product information with sales records

test , Using internal connection is as simple as connecting two tables

SELECT  *  FROM   goods  JOIN   Sales list   ON    Sales list . Product id  = goods . Product id 
  

 

External connection two tables are connected

 SELECT *  FROM    goods   LEFT  JOIN  Sales list    ON  Sales list . Product id  = goods . Product id 
  

 

 

 

ps: There is an additional line of product information without sales records .

 

 

The results have come out ,  That is, because in the commodity list g16 This item number does not match in the sales table , The sales form doesn't have g16 Sales record of , But because of the external connection , Itself is to let : Table on the left ( goods ) Even if some fields ( Product id ) The record is not associated with another table ( Sales list ) Field matching will also show ,  ps: Therefore, the commodity number in the commodity table is G16 This record will show . You can also understand why the sales table on the right side is empty ,【 No information matching the left table 】

 

Here's a simple test : In commodities The item number is 16 The record of is consistent with the above figure .

 

Throw questions ,            The main           below  

Now I want to query the product information without sales record . Is to take null Filter out the record of .

Positive solution :

 SELECT *  FROM    goods   LEFT  JOIN  Sales list   ON   Sales list . Product id  = goods . Product id 
  WHERE   Sales list . Number  IS NULL

 

 

ps: I used to write my own way :

Why can't tables be connected   on    Followed by filter conditions ？？？

SELECT *  FROM    goods   LEFT  JOIN  Sales list   ON   Sales list . Product id  = goods . Product id 
  AND  Sales list . Number  IS NULL

As a result, the records whose sales table is not empty are also displayed , All in null fill . This does not meet the requirements

 

This may not be easy to understand , Direct tests   left  join    Back     on + Filter conditions     and   where + Filter conditions

where  Filter

sql: Execution order ;      1.join left   on When connected according to conditions , There are no other filter conditions , Generate temporary tables

                           2. After the temporary table is generated ( It's a big watch connected by two watches ), Add filtering conditions   where , here   Not in line with  where  Conditions   Of remove

                         3.select Inquire about

 

on When connected  +and Filter

sql Execution order :                   1.join  left on When connected according to conditions , That is, when the temporary table is being generated and filter , No matter and The condition is true and false , The records on the left show ,     2.select  Inquire about  

    As a result, the sales provinces and cities are not all in Shanxi null fill , It's not filtered into a record we want

 

summary :              In fact, that is   left  join Connected to a     where  And on  The timing of implementation is different

In the use of left join when ,on and where The conditions differ as follows ： 1、 on The condition is the condition used to generate the temporary table , It doesn't care on Is the condition in true , Will return to the records in the table on the left , The right table does not meet the conditions null fill  2、where The condition is that after the temporary table is generated , Then filter the temporary table . There is no such thing as left join The meaning of the , The temporary table has been generated , If the conditions are not true, filter them out