MySQL left join uses the difference between on and where filtering

For illustrative purposes, we now have two tables, namely the commodity table (products) and the Sales_detail (Sales record sheet). We mainly use these two tables to tell the difference between the left join condition on MySQL and the Where condition.

1. Data preparation

Create a Products table and insert data

Drop Table if existsProducts ; CREATE TABLE' products ' (' pid 'INT(3) not NULLauto_increment, ' pname 'VARCHAR( -) not NULL, ' Pcode 'VARCHAR( -) not NULL,    PRIMARY KEY(' pid ')) ENGINE=MyISAM auto_increment= 1 DEFAULTCHARSET=UTF8;INSERT  into' products ' (' pid ', ' pname ', ' Pcode ')VALUES    (1,'Item 1','AC90'),    (2,'Item 2','DE78'),    (3,'Item 3','XXXX');

Create a Sales_detail table and insert data

Drop Table if existsSales_detail;CREATE TABLE' sales_detail ' (' Aid ' )INT(3) not NULLauto_increment, ' Pcode 'VARCHAR( -) not NULL, ' saletime ' date not NULL,    PRIMARY KEY(' aid ')) ENGINE=MyISAMDEFAULTCHARSET=UTF8 auto_increment= 1;INSERT  into' Sales_detail ' (' aid ', ' pcode ', ' saletime ')VALUES    (1,'AC90','2008-09-22'),    (2,'DE78','2008-09-22'),    (3,'AC90','2008-09-23'),    (4,'AC90','2008-09-24');

The data in the database is as follows:

Products table

Pid	PName	Pcode
1	Item 1	AC90
2	Item 2	DE78
3	Item 3	Xxxx

Sales_detail table

Aid	Pcode	Saletime
1	AC90	2008-09-22
2	DE78	2008-09-22
3	AC90	2008-09-23
4	AC90	2008-09-24

2. Testing

Now there is a scene, according to the product in a certain time period of sales, such as I would like to count 23-24 of these two days of sales number side row. (Note: DE78 This product does not sell in these two days, but also to show out, only the quantity is 0)

Use the Where Condition query:

SELECTP.pname, P.pcode, S.saletime,Count(S.aid) as Total from Products asP Left JOINSales_detail asS on(S.pcode=P.pcode)WHERES.saletimeinch('2008-09-23','2008-09-24')GROUP  byP.pcodeORDER  by TotalDESC, P.pidASC

Results:

PName	Pcode	Saletime	Total
Item 1	AC90	2008-09-23	2

The query process here can be divided into two parts, first by the on condition to generate an intermediate table (a total of 3 data), and then use the Where condition to filter the intermediate table to get the final result.

Use the On condition query:

SELECTP.pname, P.pcode, S.saletime,Count(S.aid) as Total from Products asP Left JOINSales_detail asS on((S.pcode=P.pcode) andS.saletimeinch('2008-09-23','2008-09-24'))GROUP  byP.pcodeORDER  by TotalDESC, P.pidASC

Results:

PName	Pcode	Saletime	Total
Item 1	AC90	2009-09-23	2
Item 2	DE78	Null	0
Item 3	Xxxx	Null	0

The results are obtained directly from the on condition, regardless of whether the on condition is true or not, the records in the left table are returned, and if a row that does not satisfy the join condition is also present in the query results, the join condition must be placed on. The above query is equivalent to:

SELECTP.pname, P.pcode, S.saletime,Count(S.aid) as Total from Products asP Left JOIN(Select *  fromSales_detail swhereS.saletimeinch('2008-09-23','2008-09-24')) asS on(S.pcode=P.pcode)GROUP  byP.pcodeORDER  by TotalDESC, P.pidASC

3. Conclusion

When a database returns records by connecting two or more tables, an intermediate temporary table is generated, and the temporary table is returned to the user.

When using left Jion, the difference between on and where conditions is as follows:

1. On condition is the condition used when generating a temporary table, which returns records from the left table regardless of whether the condition on is true.

2. Where condition is the condition that the temporary table is filtered after the temporal table has been generated. At this point there is no left join meaning (must return the record of the table on the right), the condition is not true all filter out.

MySQL left join uses the difference between on and where filtering