Chapter 8 Home | Prev: 8.6.1 Wildcards | Next: 8.7 Sorting

8.6.2 Filtering with Aliases

When applying filtering your query, it is possible to use table aliases in the WHERE part of the statement, but it is NOT possible to use field aliases.  Remembering the Order of Execution, this is because the SELECT statement does not execute from the top to the bottom.  Therefore, the timing of the creation of the alias will impact the ability to reference it or not.

Table aliases are usable in the WHERE part of the statement as they are defined during the FROM part and therefore exist at the time WHERE executes.

However, fields aliases have not been defined at the time WHERE executes and therefore are not accessible.

SELECT firstname + ' ' + lastname AS fullname
FROM employees
WHERE upper(fullname) LIKE 'B%';

BOTH will fail. fullname does not yet exist when the WHERE statement executes, which results in an error.
This is caused due to the Order of Execution. This statement will have to be rewritten using the original fields:

SELECT firstname + ' ' + lastname AS fullname
FROM employees
WHERE upper(firstname) LIKE 'B%';

Another Example

SELECT studentID, mark, maxmarks, round(100 * mark / maxmarks,2) AS grade
FROM studentMarks
WHERE grade >= 50;    -- will fail

-- needs to be:
SELECT studentID, mark, maxmarks, round(100 * mark / maxmarks,2) AS grade
FROM studentMarks
WHERE round(100 * mark / maxmarks,2) >= 50;