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

This article introduces the relevant knowledge of "what is the execution order of SQL queries". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

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 before 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 is legal, or what a query will return, the picture above

That diagram is not applicable 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 based on FROM. WHERE... In the order of 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.

This is the end of the content of "what is the order in which SQL queries are executed". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.