RSS

SQL Logical Order of Operations

02 May

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: , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

 
%d bloggers like this: