Various stages of logical processing

Various stages of a logical query

The most obvious feature of SQL that differs from other programming languages is the order in which code is processed, and in most languages, code is executed in the order in which it is written. In SQL, however, the first clause to be processed is the FROM clause , although the SELECT statement first appears, but it is always placed in the final processing.

Each step of SQL generates a virtual table that acts as the input to the next step. These virtual tables are not available to callers, and only the virtual tables generated in the last step are returned to the caller. If a clause is not specified in the query, the corresponding step is skipped.

The first step: The SQL query statement enters the FROM clause, and if there is a table operator, the operation is based on the type of the table operator (JOIN, APPLY, PIVOT, UNPIVOT). (Join process step 1. Cartesian product 2.ON Filter 3. Add an external row; pivot processing steps: 1. Grouping, 2. Extension, 3. Aggregation), and if there are other table operators, the loop continues processing.

Example: The SQL first enters the form clause, encounters the JOIN keyword, (left outer is the modifier of the join), then executes the first step of the join Cartesian product, and then the on filter.

This step performs a Cartesian product (cross join), if the left table contains n rows and the right table contains m rows, then the Cartesian product is generated (n*m line). Then on filter. There are three logical values of on: true/false/unkonw. then, based on the true of the on filter, insert into the newly generated virtual table, such as. The third step is to add the outer row. At this time the left Outer join, because it is a leave join, will be marked as a reserved table, although the left table in the logical operation of the on, some values do not match, but we still do not match the left table on the data

Add outer row, outer row non-reserved table property is null.

Step Two:

The where is handled. It is important to note that the WHERE clause has not been grouped, so the aggregate function cannot be used. The use of aggregate functions is only used in the HAVING clause. You cannot also use the alias of the select list. Because the SELECT statement is not yet executed. Because the left connection is used, if we want to make the logical judgment on the on, then we add the logical condition judgment related to the virtual table at the outer row of the on filter. So if the inner join is not used, then the relevant logical judgment is judged in the WHERE clause.

Step Three

GROUP BY. Groups virtual tables after the where condition is in effect.

Fourth Step:

Having a having, at this time having the following basis of the aggregation function within the specified column, it is necessary to judge the different columns according to the conditions are different results. (Because if count (null) is not the same as the result of Count (*), the count (null) result is a 0,count (*) result of 1) and The subquery cannot be entered as an aggregate function.

Fifth Step:

Carry out the Select,select step inside 1. Evaluates the expression. 2. Proceed to the DISTINCT clause, 3. Apply Top

SQL interchange values for two columns: UPDATE dbo. Customers SET CustomerID=City,city=CustomerID

Top applications, sorted according to order by or with TIES, if no order BY is specified, then the return top of each query may be indeterminate because it happens to be accessed first.

Sixth step:

Order BY, where order by is the only alias that can use the Select column. Order by returns a cursor that is not a valid table.

In SQL, set theory is the most important, and the collection is not pre-defined order, it is just a combination of logic, the order between members is irrelevant, for a sort of order BY clause of the query, you can return an object, where the rows are organized in a specific order. This is where we can make this object a cursor .

If you use top in the outermost query, then the ORDER BY clause has two meanings: 1. Logically sort top, 2. Sort the cursor for a definition.

If the order by is used in the following cases, it is only a logical order for top, not the result ordering

Table operators

1.JOIN

<left_table_expression> {Cross|inner|outer} join <right_table_expression>

To perform the steps:

1. Cartesian product
2. Apply an on filter
3. Add an external row

2.APPLY

<left_table_expression> {cross|outer} apply <right_table_expression>

To perform the steps:

1. Apply the expression of the right table to the row of the left table

2. Add an external row (add unmatched null data)

Cross apply and outer apply all contain step 1, but only outer apply contains step 2

3.PIVOT

Pivot (aggregate function (the column that gets the result) for (the column based on the condition) in (the collection of condition columns))

4.OVER clause

Can be used with aggregation functions, and it is also an element that requires the analysis of the rank function

Set operators

The set operators are: union ALL, Union, except, intersect

Union All: Returns the result of all rows in two inputs, including the same

Union: Returns a different row in two inputs

Except: Returns the first occurrence, but no longer appears in the second, mathematically speaking: To find the difference set of two sets

Intersect: Returns the different lines that have occurred in all two inputs, mathematically: the intersection of two sets

Various stages of logical processing