x

When using a static column with SELECT, why can't I access that column from WHERE clause?

If I try to do the following:

SELECT *,1 AS b FROM tbl__a_ WHERE tbl_a.a < b;

I get an error basically saying that b is not available, but if I do it from a subquery:

SELECT * (SELECT *,1 AS b FROM tbl__a_) AS foo WHERE tbl_a.a < b;

it works.

Yeah, here b is a constant, but b could also be a formula which I don't want evaluated multiple times. It would actually be preferable if I could get b evaluated only if everything prior to the comparison with b required that b be evaluated. Having b inside of the subquery requires that b be evaluated for each row with no logical short circuiting possible (assuming that any of the DBs actually do that).

I'm using MySQL 5.5. Does this occur in other DBs?

Thanks

A
more ▼

asked Jul 18, 2012 at 08:41 PM in Default

adrianh gravatar image

adrianh
20 2 2 4

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I have never used MySQL; however, SQL Server will not let you access columns created entirely in the select clause in your where clause, and would give a "Invalid Column name" error if you try. This is because it is looking explicit at the columns available from entities in the FROM clause when it matches to the where clause.

Using a subquery will not always make SQL Server evaluate a static formula multiple times though, depending on how you write the query and exactly what the engine has to do. If you look at the execution plan for something like:

select *
from
(select *, 1 as b
from dbo.Test ) as bt
where bt.b = 1

It will show that the compute scalar part of the plan has an I/O cost of 0 and an estimated number of executions of 0, regardless of how large the test table is.

Incidentally, it seems that there should be little reason to use a static formula (one which does not actually reference any columns from the base tables) in the where clause and a formula that does reference a column really would need to be evaluated seperately for each row.

more ▼

answered Jul 18, 2012 at 10:26 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left

Thank you.

While exploring this problem further, I wrapped my head around the syntax more and see why access to the select clause is not available from the where clause. This is so that functions called from the select clause to generate a row are not called if a row is not generated.

The the main reason that I am using a static formula is so that my prepared statement doesn't need to have reference to the same variable several times. A simple example would be:

SELECT *
FROM a INNER JOIN b ON a.id=b.id
WHERE a.i < ? AND b.i < ?;  # the ? are representing the same number

SELECT *
FROM (
    SELECT *, ? AS constant # only one ? reference used
    FROM a INNER JOIN b ON a.id=b.id
) AS foo
WHERE a.i < a.constant AND b.i < constant;
Cheers
more ▼

answered Jul 19, 2012 at 06:44 PM

adrianh gravatar image

adrianh
20 2 2 4

No. The WHERE clause cannot reference the SELECT clause because it comes before it during the logical processing of the query. The order to consider is: FROM->ON(JOIN)->OUTER(JOIN)->WHERE->GROUP BY->CUBE/ROLLUP->HAVING->SELECT->DISTINCT->ORDER BY->TOP. SELECT is one of the last operators to be evaluated.
Jul 21, 2012 at 01:03 AM Scot Hauder
As I am kinda new to SQL, I'll say that you are right in your order of precedence. But you are not taking into consideration that the ? AS constant is part of a subquery so that is like generating a whole new table making this new column available to the main query.
Jul 21, 2012 at 02:23 AM adrianh
OK, if you are new to SQL then skip MySQL and download the free version of SQL Server: http://www.microsoft.com/en-us/download/details.aspx?id=29062 ..and NEVER look back
Jul 21, 2012 at 04:41 AM Scot Hauder
I'm not that new. I just dabble. And why should I use MsSQL over MySQL?
Jul 21, 2012 at 03:11 PM adrianh
Learn both
Jul 21, 2012 at 06:08 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x109
x68
x19

asked: Jul 18, 2012 at 08:41 PM

Seen: 1734 times

Last Updated: Jul 21, 2012 at 06:08 PM