Syntax order and execution order in SQL (GO)

Many programmers are very conflicted with SQL. In fact, SQL is a small number of declarative languages, but it operates in a completely different way from the command-line language we know, the object-oriented programming language, and even the function language.

Today we will study the syntax order and execution order of SQL. (Entry-level. The great gods can just drop out of the ha. ）

SQL is a declarative language

The first thing to do is to keep this concept in mind: "declaration." The SQL language is an example of declaring a computer what you want from the raw data, rather than telling the computer how to get the results.

The SQL language declares the properties of the result set, and the computer picks up the declared data from the database based on what SQL declares, rather than on how the computer operates with traditional programming thinking. Isn't that cool?

(Small: Simple and rude: Hello, computer you put all the sales records that were stored in last month to me.) Computer: Last month's records were:

The above example is very clear. We don't care how this data comes about, we just want to look at the results.

Why a lot of programmers are very resistant to SQL, the main reason is: our subconscious is in accordance with the imperative programming thinking way to think about the problem. It's like this: "Computer, take this step first, then perform that step, but before that, check to see if condition A and condition B are met." For example, using variable parameters, using loop statements, iterating, calling functions, and so on, are all thought-ins of this type of imperative programming. And SQL does not follow this kind of imperative programming thinking way to deal with the problem.

In addition, the most critical point: SQL syntax is not executed in syntax order

The SQL statement has a feature that makes most people confused: the order in which the SQL statements are executed is inconsistent with the syntax order of their statements. The syntax order of the SQL statement is:

1. SELECT[DISTINCT]

2. From

3. WHERE

4. GROUP by

5. Having

6. UNION

7. ORDER by

For ease of understanding, the above does not list all the SQL syntax structure, but it is sufficient to explain that the syntax of the SQL statement and its order of execution is completely different, the above statement as an example, the order of execution is:

1. From

2. WHERE

3. GROUP by

4. Having

5. SELECT

6. DISTINCT

7. UNION

8. ORDER by

There are three interesting areas to note about the execution order of SQL statements:

1, from is the first step of SQL statement execution, not SELECT. The first step in the database execution of the SQL statement is to load the data from the hard disk into the data buffer in order to manipulate the data.

2. SELECT is executed after most of the statements have been executed, strictly speaking, after the From and GROUP by. It is important to understand this, which is why you cannot use the field that sets the alias in the SELECT in the where to determine the condition.

If you want to reuse alias New_rq, you have two choices. Or just re-write it again. The original field represented by NEW_RQ:

... or resorting to derivative tables, universal data expressions, or views to avoid alias reuse.

3. The UNION is always ranked before order by, both syntactically and in order of execution. Many people think that each UNION segment can use ORDER by ordering, but this is not true according to the SQL language standard and the differences in the execution of each database SQL. Although some databases allow for the ordering of SQL statement subqueries (subqueries) or derived tables (derived tables), this does not mean that the sort order will remain sorted after the UNION operation.

(Warm tip: Not all databases use the same parsing method for SQL statements.) such as MySQL, PostgreSQL, and SQLite will not be executed as described in the 2nd above. ）

Dear friends. Have you learned it?

Since not all databases are performing SQL forecasts in the way described above, I'm typing for a half-day for what?

To make everyone remember: The syntax order of SQL statements is inconsistent with the order in which they are executed, so that we can avoid some general errors. If you can remember the differences in the syntax order and execution Order of SQL statements, then congratulations, you can easily solve some of the general problems of SQL.

Syntax order and execution order in SQL (GO)