RSS

Tag Archives: tsql

SQL Logical Order of Operations

Following is SQL statement’s Logical Order of Operations (not particular to SQL Server):

  • FROM
  • WHERE
  • GROUP BY
  • Aggregations (COUNT, MAX, etc)
  • HAVING
  • WINDOW
  • SELECT
  • DISTINCT
  • UNION, INTERSECT, EXCEPT
  • ORDER BY
  • OFFSET
  • LIMIT, FETCH, TOP

That is why, running following SQL statement in SQL Server:

SELECT FirstName, LastName, COUNT(*)
FROM dbo.User
GROUP BY FirstName

Will throw:

Column ‘dbo.User.LastName’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

GROUP BY is run first and grouped User’s columns by FirstName therefore LastName columns and all other columns are not visible by SELECT clause.

Another not-so-obvious example:

SELECT FirstName, COUNT(*)
FROM dbo.User
WHERE COUNT(*) > 0
GROUP BY FirstName

Refer to logical order above, when WHERE clause is executed, COUNT(*) > 0 is not yet evaluated and thus SQL Server throw exception.

Read more here.

Advertisements
 
Leave a comment

Posted by on May 2, 2018 in General

 

Tags: , , , , ,

 
%d bloggers like this: