In this pgexercises about joining 3 different tables, the answer is given as following:
select mems.firstname || ' ' || mems.surname as member,
facs.name as facility,
case
when mems.memid = 0 then
bks.slots*facs.guestcost
else
bks.slots*facs.membercost
end as cost
from
cd.members mems
inner join cd.bookings bks
on mems.memid = bks.memid
inner join cd.facilities facs
on bks.facid = facs.facid
where
bks.starttime >= '2012-09-14' and
bks.starttime < '2012-09-15' and (
(mems.memid = 0 and bks.slots*facs.guestcost > 30) or
(mems.memid != 0 and bks.slots*facs.membercost > 30)
)
order by cost desc;
Why can't I refer to the cost
alias in the SELECT
list in the WHERE
clause?
If I run the same query with:
...
where
bks.starttime >= '2012-09-14' and
bks.starttime < '2012-09-15' and
cost > 30
order by cost desc;
an error occurs:
ERROR: column "cost" does not exist
It's clear with me from this answer that it's because of the order of evaluation. But why order by cost desc;
is allowed?
We cannot use a column alias with WHERE and HAVING clauses.
In PROC SQL, a column alias can be used in a WHERE clause, ON clause, GROUP BY clause, HAVING clause, or ORDER BY clause. In the ANSI SQL standard and ISO SQL standard, the value that is associated with a column alias does not need to be available until the ORDER BY clause is executed.
The basic syntax of a table alias is as follows. SELECT column1, column2.... FROM table_name AS alias_name WHERE [condition]; The basic syntax of a column alias is as follows.
All parts of the query are parsed before it's executed. So technically, nothing prevents us from using select aliases in other clauses. Sorry but no, your theory is wrong.
You ask two questions:
1.
Why can't I refer to the SELECT cost alias at the WHERE clause?
2.
But why order by cost desc; is allowed?
The manual has an answer for both of them here:
An output column's name can be used to refer to the column's value in
ORDER BY
andGROUP BY
clauses, but not in theWHERE
orHAVING
clauses; there you must write out the expression instead.
It's defined by the SQL standard and the reason is the sequence of events in a SELECT
query. At the time WHERE
clauses are applied, output columns in the SELECT
list have not yet been computed. But when it comes to ORDER BY
, output columns are readily available.
So while this is inconvenient and confusing at first, it still kind of makes sense.
Related:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With