Logical Query Processing Phase


I think this is one the most import concept to master when learning SQL.

Keyed-in order: the syntax order
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

Logical Query Processing (with easy to remember phrase, credit to my co-worker): the order logically query is processed
FROM           -- For
WHERE        -- Wives
GROUP BY   -- Good
HAVING        -- Husbands
SELECT        -- Sing
ORDER BY   -- Often

Logical Query Processing detail
FROM
JOINS    
WHERE    
GROUP BY  
HAVING      
SELECT
Column, and Expressions
Window Functions
Prepare Aliases for final output
DISTINCT  
ORDER BY  
TOP/LIMIT

Each phase output is served as input for the next phase
For optimization, SQL Server may process the query in any order.

Different between WHERE and HAVING clause:
WHERE evaluated before rows are grouped.
HAVING evaluated after rows are grouped.

SELECT statement:
All-at-once manner, no left to right
That mean this query will fail
SELECT 2*4 AS x, 2*x AS [2x]; --error invalid column x



Comments

Post a Comment

Popular posts from this blog

XPath Expression Part I

Image
Before leaning FOR XML PATH option and XQuery, today, we'll learn about XPath Expression. This post will go over some XPath terminologies. Next post we'll look at the XPath syntax and work through some examples. If you want to learn more about XPath, please read the W3C XPath documentation here. W3schools website also has a good tutorial here. Xpath (XML Path Language)     Is a standard for define path to element in XML fragments/documents.     Like a query language for XML     Expressed in hierarchical way (like path in UNIX file system)     Used in XQuery, XSLT, Xpointer, XML Schema     Level are delimited with / character     By default every column are element     Prefix the column alias with @ character to covert column to attribute-centric formatting. XPath Nodes:     Root     Element     Attribute     Text     Namespace     Comment...
Read more

Fun with NULL

I hope the example below will help you understand more about NULL. This 4 letters word can be troublesome and fun. After reading about null of this document , and this blog post  by Hugo Kornelis, I now conclude NULL is not unknown and update my explanation below. Definition of NULL    NULL is not a value    NULL is a mark for a missing value - not a value itself.    NULL represent missing data    NULL is not unknown    NULL is not applicable    NULL isn't empty    NULL doesn't equal zero or an empty string    NULL is not the value of string 'NULL'    NULL doesn't equal to NULL (just like saying something is missing can't equal to another missing object. I'll explain more on this below.)    Also SQL uses 3-valued predicate logic: True, False, Unknown.  March 27, 2014 SSC question of the day : G uess what this query returns? Select 1 Where 'hello' = Null Union ...
Read more

Transforms Rows Into XML Elements: FOR XML AUTO

This post we'll explore the XML AUTO mode. FOR XML AUTO     By default:         No nesting         Use attribute-centric presentation.         Element/row tag names = schema.table or the table alias.         It's not allowed to change the row/element tag names by specifying an argument for AUTO mode.         Note it's only allowed to specify an argument to RAW or PATH mode of FOR XML to change the element/row tag name.                 Example:                 SELECT custid                               ,contactname                 FROM Sales.Customers                 FOR XML RAW('Customers'); By adding the ELEMENT directive, the xml ...
Read more