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?



more ▼

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

avatar image

20 2 2 5

(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 *
 (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

avatar image

15.6k 22 49 38

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

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

     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;


more ▼

answered Jul 19, 2012 at 06:44 PM

avatar image

20 2 2 5

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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Jul 18, 2012 at 08:41 PM

Seen: 2316 times

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

Copyright 2016 Redgate Software. Privacy Policy