SQL Order Of Execution

I had an issue where I couldn’t use a column that was being evaluated with a modulus statement, the problem was the order of the execution.

Statement in question:

1
2
3
4
5
6
7
8
9
declare @lowerBound int = 0;
declare @upperBound int = 10;

select top 100 user_id, (user_id % 1000) as lastThreeDigits, email
from dbo.myUsers
where email like '%@github.com'
and lastThreeDigits >= @lowerBound
and lastThreeDigits <= @upperBound
order by email;

This moans with invalid column name lastThreeDigits the reason for this is the order of execution in the SQL statement being:

1
2
3
4
5
6
7
8
9
10
11
12
13
This isn't always the order but it's the normal one:

 1. FROM
 2. ON
 3. JOIN
 4. WHERE
 5. GROUP BY
 6. WITH CUBE or WITH ROLLUP
 7. HAVING
 8. SELECT
 9. DISTINCT
10. ORDER BY
11. TOP

So the WHERE is before the SELECT (where I had the alias the column lastThreeDigits) so it doesn’t exist at the point the WHERE is executed.

The solution was to give SQL a little more work to do:

1
2
3
4
5
6
7
8
9
declare @lowerBound int = 0;
declare @upperBound int = 10;

select top 100 user_id, (user_id % 1000) as lastThreeDigits, email
from dbo.myUsers
where email like '%@github.com'
and (user_id % 1000) >= @lowerBound
and (user_id % 1000) <= @upperBound
order by email;

References