phases and their orders are given as follows:
1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10 ORDER BY
11. TOP
As OUTER join is applied subsequent to ON clause, all rows eliminated by the ON clause will still be included by the OUTER join as described in the article SQL SERVER – Interesting Observation of ON Clause on LEFT JOIN – How ON Clause Effects Resultset in LEFT JOIN.
However, I am perplexed about the last two, ORDER BY and TOP. According to some people TOP comes first in logical query processing phase while others suggest that ORDER BY comes first. Now, here I’ve laid down my questions for you all to think about:
1) What is the correct answer for order query processing phase – ORDER BY or TOP?
2) How can we create an example to verify query processing phase for ORDER BY and TOP?
2) How can we create an example to verify query processing phase for ORDER BY and TOP?
Brief Description of Logical Query Processing Phases
Don’t worry too much if the description of the steps doesn’t seem to make much sense for
now. These are provided as a reference. Sections that come after the scenario example
will cover the steps in much more detail.
1. FROM: A Cartesian product (cross join) is performed between the first two tables in the FROM clause, and as a result, virtual table VT1 is generated.
2. ON: The ON filter is applied to VT1. Only rows for which the is TRUE
are inserted to VT2.
3. OUTER (join): If an OUTER JOIN is specified (as opposed to a CROSS JOIN or an
INNER JOIN), rows from the preserved table or tables for which a match was not found
are added to the rows from VT2 as outer rows, generating VT3. If more than two tables
appear in the FROM clause, steps 1 through 3 are applied repeatedly between the result
of the last join and the next table in the FROM clause until all tables are processed.
4. WHERE: The WHERE filter is applied to VT3. Only rows for which the
is TRUE are inserted to VT4.
5. GROUP BY: The rows from VT4 are arranged in groups based on the column list specified
in the GROUP BY clause. VT5 is generated.
6. CUBE | ROLLUP: Supergroups (groups of groups) are added to the rows from VT5,
generating VT6.
7. HAVING: The HAVING filter is applied to VT6. Only groups for which the
is TRUE are inserted to VT7.
8. SELECT: The SELECT list is processed, generating VT8.
9. DISTINCT: Duplicate rows are removed from VT8. VT9 is generated.
10. ORDER BY: The rows from VT9 are sorted according to the column list specified in the
ORDER BY clause. A cursor is generated (VC10).
11. TOP: The specified number or percentage of rows is selected from the beginning of
VC10. Table VT11 is generated and returned to the caller
1. FROM: A Cartesian product (cross join) is performed between the first two tables in the FROM clause, and as a result, virtual table VT1 is generated.
2. ON: The ON filter is applied to VT1. Only rows for which the
Logical Query Processing order
|
Logical Query writing order
|
Logical Query Processing Step Numbers
|
|
FROM clause
WHERE clause
GROUP BY clause
HAVING clause
SELECT clause
ORDER BY clause
|
FROM
ON
OUTER
WHERE
GROUP BY
CUBE | ROLLUP
HAVING
SELECT
DISTINCT
ORDER BY
TOP
|
SELECT clause
FROM clause
WHERE clause
GROUP BY clause
HAVING clause
ORDER BY clause
|
(8) SELECT
(9) DISTINCT
(11)TOP<TOP_specification>
<select_list>
(1) FROM
<left_table>
(3) <join_type> JOIN <right_table>
(2) ON <join_condition>
(4) WHERE
<where_condition>
(5) GROUP
BY <group_by_list>
(6) WITH
{CUBE |
ROLLUP}
(7) HAVING
<having_condition>
(10)ORDER
BY <order_by_list>
|
No comments:
Post a Comment