Sql select query with where from multiple columns

Question

I have a simple table

CREATE TABLE  a(
    id int IDENTITY(1,1) NOT NULL,
    x varchar(50) 
)

I found that following query works

select cast (id as varchar(3))+cast (x as varchar(3)) c from a 
where cast (id as varchar(3))+cast (x as varchar(3))='1a'

but this does not work

select cast (id as varchar(3))+cast (x as varchar(3)) c from a 
where c='1a'

Does any one knows why is that? Please not that for some reason I don't want to use

where id=1 and x ='a'

Answer 1

Because expressions in the WHERE clause are evaluated to restrict rows before expressions and aliases are evaluated in the select-list.

Here's a workaround:

select aprime.*
from (select cast (id as varchar(3))+cast (x as varchar(3)) c from a) aprime
where aprime.c='1a';

Answer 2

You'd have to use:

SELECT *
  FROM (SELECT CAST(id AS VARCHAR(3)) + CAST(x AS VARCHAR(3)) AS 'output' FROM a) x
 WHERE x.output = '1a'

Answer 3

Building on Bill Karwin's answer, I'd consider a computed column to encapsulate the expression so it can be reused elsewhere

ALTER TABLE a ADD COLUMN c AS cast (id as varchar(3)) +cast (x as varchar(3))

Answer 4

You could use a subquery as demonstrated by Bill Karwin. However: Please I urge you, do not do this. Please use the where clause you "don't want to use".

The following permits the query optimiser to determine an index to use and make the query run efficiently.

where id=1 and x ='a'

The following (and Bill's equivalent) prevents the query optimser from using indexes and will contribute to general performance problems on your server.

where cast (id as varchar(3))+cast (x as varchar(3))='1a'