SQL Compaction Basics (ii): Differences in use of on and where filtering in connection operations

one, on filtering and where filtering

in the connection query syntax, the other person will be the first to be confused　  The difference between on and where filtering is that if, when we write the query, the filter condition is placed either on the back or in the back, the results are always the same, so why superfluous the SQL query to support both filters? In fact, there are differences between the two filters, but it's not easy to find them if you don't dig deep.

There are 3 types of connection queries in SQL, cross Join,inner joins, and outer joins, and in cross join and inner joins, it is no different whether the filter condition is placed behind or where, at the extreme, when writing these two connection queries , only on does not use where and there is no problem. Therefore, thedifference between on filtering and where filtering is only for outer joins, which is the left join and right join that are most commonly used at ordinary times.

second, left join using on vs. where filtering differences

For illustrative purposes (using MySQL for the case), 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');

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)

2.1 using 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.

Note:

The keywords used in the query consist mainly of six, and their order is

Select--from--where--group By--having--order by

Where select and from are required, other keywords are optional, and the order of execution of these six keywords

is not the same as the order in which the SQL statements are written, but is executed in the following order

From--where--group By--having--select--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.

From: Which data table to retrieve data from

Where: Criteria for filtering data in a table

GROUP BY: How to group the data filtered above

Having: The criteria for filtering the data that has been grouped above

Select: View which column in the result set, or the computed result of the column

ORDER BY: What sequence to view the returned data

The table associated from the back of which is parsed from right to left

That is, when writing SQL text, try to put a small amount of data on the right side of the table to associate (with a small table to match the big table),

And the conditions that can filter out small amounts of data are placed at the far left of the where statement (with small tables to match large tables)

2.2 Using 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--filter out the desired time interval on(S.pcode=P.pcode)GROUP  byP.pcodeORDER  by TotalDESC, P.pidASC

Iii. 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.

SQL Compaction Basics (ii): Differences in use of on and where filtering in connection operations