Shulou(Shulou.com)05/31 Report--

This article mainly introduces "what is the execution order of SQL query statements". In daily operations, I believe many people have doubts about the execution order of SQL query statements. The editor consulted all kinds of materials and sorted out simple and useful operation methods. I hope it will be helpful to answer the doubts of "what is the execution order of SQL query statements". Next, please follow the editor to study!

Execution order of SQL queries

So I studied it and found that the order was about this. SELECT is not the first to execute, but the fifth.

This picture answers the following questions

This diagram is related to the semantics of a SQL query, lets you know what a query will return, and answers the following questions:

Can I use WHERE after GRROUP BY? No, WHERE is after GROUP BY!

Can I filter the results returned by the window function? (no, the window function is in the SELECT statement, and SELECT is after WHERE and GROUP BY.)

Can I do ORDER BY based on what is in GROUP BY? (yes, ORDER BY is basically executed at the end, so ORDER BY can be based on anything.)

When is LIMIT executed? (at last!)

However, the database engine does not necessarily execute SQL queries in this order, because they make some optimizations to execute the query faster, which will be explained in a later article.

So:

If you want to know whether a query statement is legal or what a query statement will return, you can refer to this figure

This diagram does not apply when it comes to query performance or something related to indexes.

Mixed factors: column aliases

There are many SQL implementations that allow you to use this syntax:

SELECT CONCAT (first_name,'', last_name) AS full_name, count (*) FROM table GROUP BY full_name

From this statement, it looks as if GROUP BY was executed after SELECT because it refers to an alias in SELECT. But it doesn't have to be like this, and the database engine can rewrite the query like this:

SELECT CONCAT (first_name,', last_name) AS full_name, count (*) FROM table GROUP BY CONCAT (first_name,', last_name)

In this way, GROUP BY is still executed first.

The database engine also does a series of checks to make sure that what is in SELECT and GROUP BY is valid, so it does an overall check on the query before generating the execution plan.

The database may not execute queries in this order (optimization)

In practice, databases do not necessarily execute queries in the order of JOIN, WHERE, and GROUP BY, because they will make a series of optimizations to disrupt the order of execution, so as to make the query execute faster, as long as the query results are not changed.

This query explains why the query needs to be executed in a different order:

SELECT * FROM owners LEFT JOIN cats ON owners.id = cats.owner WHERE cats.name ='mr darcy'

If you only need to find the cat named "mr darcy", there is no need to perform a left join on all the data in the two tables and filter before joining, so that the query is much faster, and for this query, performing the filtering first does not change the query results.

The database engine makes a lot of other optimizations to execute queries in a different order, but I'm not an expert, so I won't say much about it here.

LINQ's query begins with FROM

LINQ (query syntax in C # and VB.NET) is in the order of FROM...WHERE...SELECT. Here is an example of a LINQ query:

Var teenAgerStudent = from s in studentList where s.Age > 12 & & s.Age

1000] # WHERE df = df.groupby ('something', num_yes = (' yes', 'sum')) # GROUP BY df = DF [df.num _ yes > 2] # HAVING, filter the GROUP BY result df = df [[' num_yes', 'something1',' something']] # SELECT, select the columns df.sort_values ('sometthing', ascending=True) [: 30] # ORDER BY and LIMIT df [: 30] to display

This is not written because pandas dictates these rules, but it makes more sense to write code in the same order as JOIN/WHERE/GROUP BY/HAVING. But I often write WHERE first to improve performance, and I think most database engines do the same.

Dplyr in the R language also allows developers to write SQL queries using different syntax to query Postgre, MySQL, and SQLite.

At this point, the study of "what is the execution order of SQL query statements" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.