MySQL Associated left join condition on is different from where, very practical, but use caution

In MySQL query, sometimes the business complex, have to write multi-table query, many of the early apes like to write subqueries, because the subquery is easy to understand, not prone to problems, but the efficiency of sub-query is not as related to query; Association queries I think it is a double-edged sword, in the mechanism does not understand it, or use caution, I met the former colleague, because the relevance of the query did not write well, the prize (query activity winning information) wrong, fortunately, the jackpot is not a car;

Online pick-up example, better understand

MySQL Association left JOIN condition on and difference table of where condition the structure of ' products '

1. CREATE TABLE ' Products ' (
2. ' PID ' int (3) not NULL auto_increment,
3. ' pname ' varchar (not NULL),
4. ' Pcode ' varchar (not NULL),
5. PRIMARY KEY (' pid ')
6. ) Engine=myisam auto_increment=1 DEFAULT Charset=utf8;

Export the data in the table ' products '

1. INSERT into ' product ' (' pid ', ' pname ', ' Pcode ') VALUES (1, ' Merchandise 1 ', ' AC90 '),
2. (2, ' Merchandise 2 ', ' DE78 '),
3. (3, ' Merchandise 3 '. ' XXXX ');

The structure of the table ' Sales_detail '

1. CREATE TABLE ' Sales_detail ' (
2. ' Aid ' int (3) not NULL auto_increment,
3. ' Pcode ' varchar (not NULL),
4. ' Saletime ' date not NULL,
5. PRIMARY KEY (' aid ')
6. ) Engine=myisam DEFAULT Charset=utf8 auto_increment=1;

Export the data in the table ' Sales_detail '

1. INSERT into ' sales_detail ' (' aid ', ' pcode ', ' Saletime ') VALUES (1, ' AC90 ', ' 2008-09-22 '),
2. (2, ' DE78 ', ' 2008-09-22 '),
3. (3, ' AC90 ', ' 2008-09-23 '),
4. (4, ' AC90 ', ' 2008-09-24 ');

MySQL Association left JOIN condition on and where Condition difference difference two SQL statement:

2. Select P.pname,p.pcode,s.saletime,count (s.aid) as total from products as P
4. Left join Sales_detail as S on (S.pcode=p.pcode)
6. where s.saletime in (' 2008-09-23 ', ' 2008-09-24 ')
8. Group BY P.pcode ORDER by Total desc,p.pid ASC
10. +---------+-------+------------+-------+
12. | PName | Pcode | Saletime | Total |
14. +---------+-------+------------+-------+
16. | Item 1 | AC90 | 2008-09-23 | 2 |
18. +---------+-------+------------+-------+
20. Select P.pname,p.pcode,s.saletime,count (s.aid) as total from products as P
22. Left join Sales_detail as S on ((S.pcode=p.pcode) and S.saletime in (' 2008-09-23 ', ' 2008-09-24 '))
24. Group BY P.pcode ORDER by Total desc,p.pid ASC
26. +---------+-------+------------+---------+
28. | PName | Pcode | Saletime | Total |
30. +---------+-------+------------+-------+
32. | Item 1 | AC90 | 2008-09-23 | 2 |
34. | Item 2 | DE78 | NULL | 0 |
36. | Item 3 | XXXX | NULL | 0 |
38. +---------+-------+------------+---------+

Experience: On the conditional association, a table of data does not meet the criteria when the null value is displayed. Where the output two tables fully satisfy the condition data.

My experience: The results in the Where condition affect the query recordset of the primary table, and if you want to query all eligible records of the main table in the left join, remember to look at the where condition, where the condition (whether it is the primary table, the condition of the schedule) will filter out the recordset in the primary table. Should be on the back.

We know that the standard query keyword execution order is from->where->group By->having->order by

The left join is in the From range class so the on condition first filters the table, and then the two tables do the left join.

And for where, the left join results are filtered again.

This article was reproduced from: http://gaoerpeng777.blog.163.com/blog/static/9437945020127633739771/

MySQL Associated left join condition on is different from where, very practical, but use caution