MySQL Association leftjoin condition on and where are different _ MySQL

MySQL Association leftjoin condition on and where different bitsCN.com

I encountered a problem about the difference between on and where in left join at work today. I don't know if I can use where in left join. I asked my colleagues to know! This record is used for future query.

Difference: condition Association in on. if a table does not meet the condition, a null value is displayed. If it is set to where, the two tables are output completely meeting the condition data.

The following information is found:

The following article describes the differences between the left join condition on and where conditions in MySQL. We now have two tables: Product Table (products) and sales_detail (sales record table ). The two tables are used to describe the differences between the left join condition on and where conditions in MySQL.

Products:

Pid pname pcode

1 Product 1 AC90

2 goods 2 DE78

3 goods 3 XXXX

1. sales_detail:

2. aid pcode saletime

3. 1 AC90 2008-09-22

4. 2 DE78 2008-09-22

5. 3 AC90 2008-09-23

6. 4 AC90 2008-09-24

This is based on the sales volume of the product in a certain period of time. for example, I want to count the sales volume of the product on the 23-24 day basis and rank it. (Note: DE78 is not sold in the past two days, but it must be displayed, but the quantity is 0 ).

Difference between the left join condition on and where conditions in MySQL Association table structure 'products'

1. create table 'products '(

2. 'pid 'int (3) not null auto_increment,

3. 'pname' varchar (20) not null,

4. 'pcode' varchar (20) not null,

5. primary key ('pid ')

6.) ENGINE = MyISAM AUTO_INCREMENT = 1 default charset = utf8;

Export the data 'products' in the table'

1. insert into 'products' ('pid ', 'pname', 'pcode') VALUES (1, 'item 1', 'ac90 '),

2. (2, 'item 2', 'dest78 '),

3. (3, 'item 3'. 'xxxx ');

Table structure 'sales _ detail'

1. create table 'sales _ detail '(

2. 'aid 'int (3) not null auto_increment,

3. 'pcode' varchar (20) not null,

4. 'saletime' date not null,

5. primary key ('aid ')

6.) ENGINE = MyISAM default charset = utf8 AUTO_INCREMENT = 1;

Export the data 'sales _ detail' in the table'

1. insert into 'sales _ detail '('aid', 'pcode', 'saletime') VALUES (1, 'ac90', '2017-09-22 '),

2. (2, 'dest78 ', '2017-09-22 '),

3. (3, 'ac90', '2017-09-23 '),

4. (4, 'ac90', '2017-09-24 ');

Difference between the left join condition on and where conditions in MySQL:

1. select p. pname, p. pcode, s. saletime, count (s. aid) as total from products as p

2. left join sales_detail as s on (s. pcode = p. pcode)

3. where s. saletime in ('2017-09-23 ', '2017-09-24 ')

4. group by p. pcode order by total desc, p. pid asc

5. + --------- + ------- + ------------ + ------- +

6. | pname | pcode | saletime | total |

7. + --------- + ------- + ------------ + ------- +

8. | item 1 | AC90 | 2008-09-23 | 2 |

9. + --------- + ------- + ------------ + ------- +

10. select p. pname, p. pcode, s. saletime, count (s. aid) as total from products as p

11. left join sales_detail as s on (s. pcode = p. pcode) and s. saletime in ('2017-09-23 ', '2017-09-24 '))

12. group by p. pcode order by total desc, p. pid asc

13. + --------- + ------- + ------------ + --------- +

14. | pname | pcode | saletime | total |

15. + --------- + ------- + ------------ + ------- +

16. | item 1 | AC90 | 2008-09-23 | 2 |

17. | item 2 | DE78 | NULL | 0 |

18. | item 3 | XXXX | NULL | 0 |

19. + --------- + ------- + ------------ + --------- +

Tips: condition Association in on. if a table does not meet the conditions, a null value is displayed. If it is set to where, the two tables are output completely meeting the condition data.

BitsCN.com