SQL query language (1. SELECT statement process)

The general format of the SELECT statement:

Select[All | distinct] [Top N [percent] select_list
[Into new_table]From{Table_name | view_name [, table_name2 | view_name2] [,…]} [WhereSearch_condition]
[GroupGroup_by_list] [HavingSearch_conditon] [OrderOrder_list [ASC | DESC]

General classification and execution sequence of select statements

1. From clause

1-1. Overview of the from clause

The main task of the from clause is to generate the source table for the query and perform table operations. In the SELECT statement of the T-SQL, the first from clause must be executed, which also reflects a feature of the statement: the execution order of the clause is not in the write order. It is easy to understand how to execute the from clause first. We can know that we must first determine the source table and then perform subsequent operations according to the sequence of transactions.

1-2. Functions of the from clause

1-2-1. Single table:

1-2-1-1. Determine the table source;

1-2-1-2. We can also add some operations on the table, such as row-changing or column-changing operations.

1-2-2. Multiple tables:

1-2-2-1. Determine the multi-Table source;

1-2-2-1. establish the relationship between the table and the table (join ).

! -1. Why do we need to add the relationship between tables?

After the information of multiple tables is determined, the computer first combines the records of multiple tables into a new table (B1-1) in the form of cartesian products, that is, one record in one table and all records in another table form a new record respectively. To obtain uniqueness, we need to establish the relationship between the table and the table. Of course, we can also create them in the WHERE clause, which is different from each other and will be introduced later;

! -2. Join methods and explanations of tables and tables

@-1. Three Join Methods

Internal Connection, external connection, and Cross Connection

@-2. Classification of external connections

External connections include left outer connections, right outer connections, and full outer connections. external connections are just a general term for these three operations.

@-3. Explanation of external connections

Take the outer left join as an example: The expression form is the condition that a data table left [outer] Join B data table on is combined, and a new record is formed based on the record in A and the matching condition, if record B does not meet the condition, only record a in the new record does not exist in record B. This is the left Outer Join. You can launch the right Outer Join and full outer join.

1-3. General process of from clause internal execution

According to the table source in from, the computer performs Cartesian Product Operation on the records in each table to form a virtual table B-1-1 composed of a new record, the computer filters out new records from the B-1-1 to form a new virtual table B-1-2; the computer generates a new virtual table B-1-3 when a record is added based on the join relationship between the table and the table. You can think about the reason for this order. We will not talk about it here.

2. Where clause

2-1. Execution Process of the WHERE clause

Where filters the virtual table B-1 Based on the <where_predicate> condition and forms a new virtual table B-2 for all qualified records.

2-2. Differences between the where condition and the on (join) Condition

From the descriptions in 1-3 and 2-1, if the from clause only generates virtual table B-1-2, Then where can achieve the effect of on, if the from clause generates a B-1-3 table (different from the B-1-2), then, the effect of on and where is different. In other words, for inner connections, the where and on functions exactly the same. For outer connections, there is a difference between on and where. The results after on filtering are not the final results, add external rows to filter the tables with external rows added.

2-3. note in the WHERE clause

The alias of the table in the select clause cannot be referenced, because it has not been executed yet; the aggregate function cannot be used, because at this time the record has not been grouped, that is, the group by statement has not been executed yet.

3. Group by clause

3-3. Objective of the Group by clause

The purpose of the Group by clause can also be described as the purpose of grouping. grouping is used for aggregation calculation, that is, applying Aggregate functions (avg, sum, Min, Max, count ).

3-2. Execution of group by clauses

The computer combines the records in the virtual table B-2 (ranging in numbers) into a group as required and generates a new virtual table B-3 as a new record.

3-3. Differences between with cube and rollup in group

First, you must understand that groups can also be grouped in different levels;

With cube enables Aggregate functions to be applied to groups at each level. That is to say, the aggregate function application returns the records generated after the group aggregation operation at each level;

With rollup only sets the range of Aggregate functions at the first level. That is to say, the application of Aggregate functions only returns the records generated after the first layer of group aggregation.

4. Having clause

4-1. Differences between having clauses and where clauses

The having clause differs from the WHERE clause, and the operation objects are different. The object of the having clause is the record in the B-3 of the virtual table, that is, the object of the having clause is the group, and the object of the WHERE clause is the record in the B-1. Based on the above, we also know that the having clause is mainly
By clause.

4-2. Execution of having clauses

When the computer completes the group by clause to generate a virtual table B-3, the computer then filters the B-3 based on the conditions in the having clause, and the filtered records form a new virtual table B-4.

5. Select clause

5-1. Execution of the select clause

Depending on the field name or field name operation in the select, from the virtual table B-4 to the corresponding result, make a record, and then these records form a new virtual table B-5-1; (Note: when the selected result is a field name operation, we can create a field name for the column or a new field name for the system; in the select list, all expressions are calculated at the same time. In the simplest example, the values of two variables are exchanged, so we do not need to define the third variable .)

Run the all and distinct keywords. If the keyword is distinct, You need to delete the same record in the virtual table B-5-1, the new record is then made up a new virtual table B-5-2, the key is all without changing, if this keyword is not found by default, the keyword is all;

Run the keyword Top N [percent]. When it is top N [percent], the computer selects N or N percent of records from the virtual table B-5-2 to form a new virtual table B-5-3.

6. Order by clause

6-1. Execution Process of order

Sort by the specified field name, a record in the field name changes with the change of the column, and finally generates a cursor B-5 from the virtual table B-6. (The concept of cursor will be added later)

6-2. Differences between order by and group

Group by is a group. Multiple corresponding records in a table are grouped into a group, and this group of records is only a record in the new virtual table;

Order by sorts the records in a table and sorts the sequence according to certain requirements. The table with the sequence sorted is the new table. (The keyword ASC is ascending, And the keyword DESC is descending. The default value is ascending)

Note: The table concept in the preceding Section also refers to tables such as views. The SELECT statement does not have to go through any process. Therefore, we must pay attention to the virtual tables used in each process, at the time of writing, the name we write corresponds to the table next to its previous process. However, we need to know this meaning, the table names used in the process are not changed flexibly. For example, if we didn't write the having clause, then the B-4 in our select clause refers to another table. You need to understand the meaning of this layer.

Experience: This blog takes more than two days. After writing this blog, you can write a word: Awesome!