Join filter considerations for MySQL SQL statements

We are not unfamiliar with the join statements for SQL statements. This is noteworthy for content filtering in the where statement, or for filtering in the on statement or having.

Case: To find a period of time to register new users, not through the Novice task specific which step task, statistics

which involves two tables

User base table: user_base, user_id, registration time Created_at

Task table: User_id,task_id,task_status

A statement:

Select min (b.task_id) life,task_status, a.user_id from User_tasks b join (select user_id from user_bases where creat Ed_at between ' and ') A on a.user_id=b.user_id group by A.USER_ID has b.task_status<3;

B Statement:

Select min (task_id), task_status,a.user_id from User_tasks b join (select user_id from user_bases where Created_at betwee n ' and ') A on a.user_id=b.user_id and task_status<3 Group by USER_ID;

The biggest difference between A,B statements is the order in which task_status<3 is filtered.

A, the statement in the table content to link match, take user_id, find each user's task_id, finally is having task_status<3 filter

B, when the statement is a table content match, Task_status<3 joins and, according to group by, asks each user min task_id.

So the B statement is in line with the standard.

When you link to a join, decide which fields to filter the content in!

This article is from the "Technology Achievement Dream" blog, please be sure to keep this source http://weipengfei.blog.51cto.com/1511707/1117069

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/MySQL/