Shulou(Shulou.com)06/02 Report--

This article mainly explains "what is the execution order of SQL query". Interested friends may wish to have a look at it. The method introduced in this paper is simple, fast and practical. Next, let the editor take you to learn "what is the execution order of SQL queries?"

Many SQL queries start with SELECT.

Recently, however, I explained to others what a window function is. I searched the Internet for the question of "can I filter the results returned by the window function?" and concluded that "the window function must be after WHERE and GROUP BY, so it cannot."

So I came up with another question: what is the order in which SQL queries are executed?

It seems that this question should be easy to answer. After all, I have written tens of thousands of SQL queries, some of which are very complicated. But the truth is, it's still hard for me to say exactly what the order is.

Execution order of SQL queries

So I studied it and found that the order was about this. SELECT is not executed first, but in 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. Recommendation: MySQL comprehensive optimization, speed to fly.

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.

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

At this point, I believe you have a deeper understanding of "what is the order in which SQL queries are executed?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

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.