Difference Analysis of on and where Placed in MySQL Query Criteria

Introduction

Today, when writing SQL, I encountered a problem. The demand is like this, query the data, and sort it in reverse order according to the score and the number of visits in recent 1 week. The problem is the conventional way to write day > = xxx condition is put into where. If some data has no visits in recent 1 week, this data cannot be found. The solution is to put the condition in LEFT JOIN.

MySQL statement execution order

First of all, I will explain a concept. The order in which MySQL statements are executed is not the order in which SQL statements are executed. The following is an example SQL

SELECT DISTINCT
 < select_list >
FROM
 < left_table > < join_type >
JOIN < right_table > ON < join_condition >
WHERE
 < where_condition >
GROUP BY
 < group_by_list >
HAVING
 < having_condition >
ORDER BY
 < order_by_condition >
LIMIT < limit_number >

The following is the execution order of SQL

FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT 
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

The role of LEFT and JOIN

The difference in result sets is not only related to the priority of SQL, but also related to LEFT and JOIN

When using left join, the condition after on is valid only for the right table

on is the condition used when generating the temporary table, and the rows of the left table (table_name1) are returned regardless of whether the condition of on works or not. where is the condition used after generating the temporary table. At this time, no matter whether left join is used or not, as long as the condition is not true, all rows are filtered out.

The above are excerpts from the two materials, which can be well summarized (the original links are below, and there are examples in them).

References:

Step by step: overview of MySQL architecture- > Query execution process- > SQL parsing order Difference between on and where placement conditions in MySQL left join operation The difference between the filter conditions in SQL and on.

Summarize