MySQL statement execution order and writing order example analysis

The complete syntax of the select statement is:

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>

Execution order:

from →join →on →where →group by →having →select →order by →limit

(1) The role of each keyword:

from: From which data table do you want to retrieve data? If there is a join, perform a Cartesian product (cross join) on the first two tables in the FROM clause to generate a temporary table (n×m rows)

on: Conditionally filter the above temporary tables

left/right (join): Supplement the left or right table to keep it complete. If there are multiple associated tables, the intermediate table continues the above two steps for the next table.

• where: Conditions for filtering data in the table
  
• group by: How to group the filtered data above

sum: aggregate function

• Having: Conditions for filtering the grouped data above
  
• select: View which column in the result set, or the calculation result of the column

distinct:

• order by: the order in which to view the returned data
  
• limit: Limit the number of query results returned
  

(2) The difference between on and where:

• The screening conditions after a.on are mainly for related tables [and are not applicable to the main table screening conditions].
• b. If you want to filter after the connection is completed, you should put the condition after where. We need to treat association tables differently. If you want to connect after conditional query, you should put the query after on.
• c. The filter condition for the main table should be placed after where, not after on.
  

(3) The difference between having and where:

• a.having can only be used after group by to filter the grouped results (that is, the prerequisite for using having is grouping).
• b.where must come before group by, that is, before having.
• Aggregate functions are not allowed in the conditional expression after where, but having is allowed.
  

(4) Usage of count

When using count(column name) and a null value appears in a column, count(*) will still be calculated, but count(column name) will not.

The above is the full content of this article. I hope it will be helpful for everyone’s study. I also hope that everyone will support 123WORDPRESS.COM.