SQL Logical Processing Order Deep Dive

If you're new to SQL, understanding the SQL logical processing order is the most important skill for writing efficient and bug-free code. Unlike other programming languages, SQL logically doesn't process the code top to bottom or left to right. In the video below, I provided examples of typical pitfalls/gotchas when creating SQL queries. We'll look into:

  1. Overview of SQL Logical Processing Order.
  2. Why query execution plan can be different from logical order.
  3. Why we can't use aliases defined in SELECT list in WHERE clause.
  4. Different between WHERE filters versus HAVING filters. 
  5. Using subquery to work around error when adding aggregate functions in WHERE clause.
  6. Using subquery and CTE to work around error when adding window functions in WHERE clause.
  7. Examine how expressions, aliases, and window functions are processed.
  8. Use window functions in ORDER BY clause.
  9. Tips on how to read SQL queries.




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

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

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