Logical Processing Order of SELECT1. FROM table
2. ON join condition
3. JOIN table
4. WHERE clauses
5. GROUP BY columns
6. WITH CUBE / WITH ROLLUP
7. HAVING condition
8. SELECT columns
9. DISTINCT
10.ORDER BY columns
11.TOP % or number |
The steps above show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. CTEs; WITH cteName ( columnList )
AS ( SELECT statement )
SELECT columns
FROM cteName
INNER JOIN table ON condition
|
Below, is a list of those statements and/or clauses that cannot be used in ANY CTE.
COMPUTE or COMPUTE BY
ORDER BY (except when a TOP clause is specified)
INTO
OPTION clause with query hints
FOR XML
FOR BROWSE Recusrsive CTEs; WITH cteName ( columnList )
AS ( -- Anchor statement:
SELECT columns FROM table…
UNION ALL
-- Recursion statement:
SELECT columns FROM table…
INNER JOIN cteName ON …
)
SELECT columns
FROM cteName
|
Here are the statements and/or clauses that cannot be used in a recursive CTE:
SELECT DISTINCT
GROUP BY
HAVING
Scalar aggregation (meaning you can't use min or max)
TOP
LEFT, RIGHT, OUTER JOIN (INNER JOIN is allowed) OVER and PARTITION BY /* Aggregate functions include COUNT, MIN,
MAX, AVG, ROW_COUNT(), etc. */
SELECT
agg_func(col1) OVER(),
agg_func(col1)
OVER(PARTITION BY col2),
columns
FROM table…
| OVER allows you to get aggregate information without using a GROUP BY. In other words, you can retrieve detail rows, and get aggregate data alongside it. | Using PARTITION BY the result set is broken into into partitions. |
| | XML Trick: List of Details/* Table2 holds detail rows for Table1; e.g., order details to order headers. */
SELECT columns,
colname = STUFF(
( SELECT ','
+ Name
FROM Table2
WHERE Table1.ID = Table2.ID
ORDER BY Name
FOR XML PATH('')
), 1, 1, '')
FROM Table2
|
EXCEPT/INTERSECTSELECT col1, col2 FROM Table1
EXCEPT
SELECT col3, col4 FROM Table2
SELECT col1, col2 FROM Table1
INTERSECT
SELECT col3, col4 FROM Table2
|
MERGEDECLARE @Changes
TABLE(Change VARCHAR(20))
; MERGE INTO DestTable
USING
( SELECT from sourceTable
) AS Source ( columnList )
ON DestTable.ID = Source.ID
WHEN MATCHED THEN
Action on destination
-- E.g., UPDATE SET col1 = 1
WHEN NOT MATCHED BY TARGET|SOURCE
Action on destination
-- E.g., INSERT (col1) VALUES(1)
OUTPUT $action INTO @Changes
SELECT * FROM @Changes
|
|
Created By
tigersi.com
Metadata
Favourited By
Comments
No comments yet. Add yours below!
Add a Comment
More Cheat Sheets by renegrin