Analysis of the difference between placing on and where in MySQL query conditions

Introduction

When writing SQL today, I encountered a problem. The requirement is to query the data and sort it in reverse order of the score and the number of visits in the past week. The problem is that the conventional way of writing is to put the day >= xxx condition in the where clause. If some data has no visits in the past week, then this data cannot be found. The solution is to put the condition in LEFT JOIN.

MySQL statement execution order

First of all, let me explain a concept. The order in which MySQL statements are executed is not the order of SQL statements. Here is the sample 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 SQL execution order

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 JOIN

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

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

• on is the condition used when generating a temporary table. Regardless of whether the on condition works or not, the rows of the left table (table_name1) will be returned.
• Where is the condition used after the temporary table is generated. At this time, it doesn’t matter whether left join is used or not. All rows that do not meet the condition will be filtered out.

The above is excerpted from two materials, which can summarize it well (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
• The difference between the on and where conditions in MySQL left join operations
• The difference between placing filter conditions in on and where in SQL.

Summarize

The above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support for 123WORDPRESS.COM.