SQL Getting Started Tutorial

SQL Getting Started Tutorial

1) from phase

This phase is the first step in the query logic processing. Thinking of this, I think the LINQ expression is not from the beginning, it seems to be quite reasonable. The From phase is responsible for representing the table or the table to query. If you specify table operators, you also need to process the operators individually, in left-to-right order. Table operators have 4 classes, Join,apply,pivot,unpivot. Each table operator has its own processing rule. Pick the most common join here.

For joins (join), there are generally several steps:

A. To get the Cartesian product, cross join the two tables to obtain the largest possible result set. If the left table has n rows and the right table has an M row, the result set has NXM rows.

B. Filter by using on filter criteria to remove rows that do not meet the criteria.

C. for outer joins (Left,right,full outer join), you also need to add external rows. In the previous step, the on condition eliminated all rows that did not match the two tables. However, in an outer join, by specifying the type of outer joins, one or two of the tables need to be marked as reserved, and all rows in that table are returned. So it's time to rejoin the rows in the retention table that were excluded from the on filter to the result set (these are added to the table, the book is called an outer row, and the column value of the non-persisted table in the outer row is marked null. Again, this step is only performed by an outer join, for an inner join (inner Join) requires only two steps to perform a and B.

(2) where stage

For the virtual table returned in the previous step, the Where condition is determined, only the rows where the Where condition is true are preserved. Note that because the data has not been grouped, it cannot be aggregated in the WHERE clause. You cannot also refer to aliases created in the select list because the Select phase is still behind you. For example, where Orderid>max (OrderID), select Year (thedate) as theyear ... where theyear>2010 is not available.

Another intriguing question is what is the difference between on and WHERE clauses for queries that contain joins, when the ON clause is used, and when the WHERE clause is used. Here is a description. This logical difference exists only on the on and WHERE clauses for outer joins, because the outer join, after filtering through the ON clause, also makes external row additions to the reserved table, and the WHERE clause is filtered after the outer row has been added. Therefore, the ON clause's filtering of this outer join is not the final result, and in the third step of the from phase, the outer row is added back. For an inner join, the WHERE clause and on clause function exactly the same, where filtering is the same effect, there is no other step. So in dealing with this query with outer joins, be sure to pay attention to the difference between on and where filters, to avoid using the wrong, and not to achieve the effect of filtering. In addition, for an inner join, a good suggestion is that for a field filter that exists for two tables, use the ON clause to filter the fields of a single table, where, for example: SELECT * from a INNER join B on a.col = B.col where a.col2 > 1.

(3) Group by stage

In this phase, the result set in the virtual table returned in the previous step is reorganized by grouping, identifying a group by each unique combination of all the columns in the grouping set. Then use these groups to relate to each row returned in the previous step. Note that each row can only be associated with one group. Finally, the resulting set of results can have only one row per group. About GROUP by there are a lot of interesting places, such as cube,rollup,grouping, and so on, have time to introduce.

(4) Having stage

The having filter is used to filter the result set returned in the previous step. Having filters are the only filters that can filter grouped data, on and where. The reason is simple, on and where both are processed before grouping, and the grouping is naturally not filtered. So the difference between having and where is also very obvious. A having can only be used with a SELECT statement. Having is usually used in the GROUP BY clause. If you do not use the GROUP BY clause, the having behavior is the same as WHERE clause.

(5) Select phase

This step, constructs the table that is eventually returned to the caller. This step involves 3 sub stages.

A. Evaluate expressions. In this phase, the select list can return the underlying columns of the virtual table obtained from the previous step of the oil, or it can be an operation on those underlying columns. One thing to note is that in this select list, all expressions are computed at the same time. For example, in SQL, you can exchange two column values in this way: Update tab_test set col1 = Col2,col2 =col1; in other languages it's really amazing.

Also, aliases created in the select list cannot be used in other expressions in the same select list. So, based on this feature, we'll come to the conclusion that the order of the select list is irrelevant.

B. Process Dinstinct, if DINSTINCT is specified in the query, delete duplicate rows from the virtual table returned in the previous step.

C. Apply the top option. For queries that specify the top option, the specified number of rows is selected based on the query's ORDER BY clause. There are a lot of special places in the top option, here is not far, there is a chance to speak alone.

(6) Order by stage

This step returns the cursor by sorting the table in the previous step, as listed in the Order by list.

Here is the need to talk about collections and cursors. The theoretical basis of SQL is the collection, the set is unordered, it is only a logical set of members. For queries with ordered by clauses with a sort effect, you can return an object in which the rows are organized in a specific order. ANSI makes this object a cursor (cursor).

Because in this step, the final return is the cursor, so the query with order by is not used to define views, subqueries, common tables, etc. For example:

The SELECT * FROM (select Col1,col2 to Tab_test ORDER by col1) is invalid and will be an error.

However, if the top option is specified at the same time, it is an exception. SELECT * FROM (select top) col1,col2 from Tab_test Order by col1, for this query, the result in the subquery must be fixed and ordered, because both the top and order by are specified, but the external queries, you cannot guarantee an orderly arrangement.