SQL Order Of Operations

Kevin Feasel

2016-12-12

Syntax

Lukas Eder explains order of operations in a SQL query:

If you’re not a frequent SQL writer, the syntax can indeed be confusing. Especially GROUP BY and aggregations “infect” the rest of the entire SELECT clause, and things get really weird. When confronted with this weirdness, we have two options:

  • Get mad and scream at the SQL language designers
  • Accept our fate, close our eyes, forget about the snytax and remember the logicaloperations order

I generally recommend the latter, because then things start making a lot more sense, including the beautiful cumulative daily revenue calculation below, which nests the daily revenue (SUM(amount) aggregate function) inside of the cumulative revenue (SUM(...) OVER (...)window function):

Lukas explains things from an Oracle perspective, so not all of this matches T-SQL, but it’s close enough for comparison.

Related Posts

Pitfalls with Window Functions

Itzik Ben-Gan takes us through two issues you might run into when using window functions: There are two common pitfalls involving window functions, both of which are the result of counterintuitive implicit defaults that are imposed by the SQL standard. One pitfall has to do with calculations of running totals where you get a window […]

Read More

The If Statement and Friends in Powershell

Kevin Marquette explains what is possible with if in Powershell: The -not operator flips an expression from $false to $true or from $true to $false. Here is an example where we want to perform an action when Test-Path is $false. if ( -not ( Test-Path -Path $path ) ) There’s plenty of good stuff here, so check it out.

Read More

Categories

December 2016
MTWTFSS
« Nov Jan »
 1234
567891011
12131415161718
19202122232425
262728293031