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'

One of the idea is to create a view vwTest with select cast (id as varchar(3))+cast (x as varchar(3)) c from a and do the query select * from vwTest where c = '1a' — Thunder
– Thunder, Commented Dec 22, 2009 at 6:57
You say: "Please not that for some reason I don't want to use where id=1 and x ='a'". Please, please... whatever your reason is, it is NOT good enough. By choosing not use the most logical and sensible option, you entirely eliminate the possibility of using indexes, and your query & database will run slower for it. — Disillusioned
– Disillusioned, Commented Dec 23, 2009 at 15:37

Bill Karwin · Accepted Answer · 2009-12-22 06:54:21Z

8

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';

answered Dec 22, 2009 at 6:54

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

OMG Ponies · Accepted Answer · 2009-12-22 06:56:28Z

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'

answered Dec 22, 2009 at 6:56

OMG Ponies

334k85 gold badges536 silver badges508 bronze badges

Comments

gbn · Accepted Answer · 2009-12-22 07:32:16Z

1

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))

answered Dec 22, 2009 at 7:32

gbn

434k84 gold badges602 silver badges690 bronze badges

5 Comments

eidylon Over a year ago

Be careful with computed columns though. They are good for simple column-derived formulas like this. But I would not recommend them for more advanced calculations that you will wrap in functions, as referencing functions in computed columns will prevent you from modifying the function.

gbn Over a year ago

Personally, I'd live with this restriction, but perhaps one point of computed columns is to reduce the use of UDFs elsewhere. AKA transferring the calculation into the table definition from code, views etc

Thunder Over a year ago

I think it would be better to add a view rather than to add a calculated column as eidylon says .

Bill Karwin Over a year ago

Not all brands of RDBMS support computed columns in the way you show. You'd have to write a trigger in some cases. Other RDBMS may allow you to use expression indexes so you don't need the extra column.

gbn Over a year ago

@Bill. OP said SQL Server though so may as well leverage that

Disillusioned · Accepted Answer · 2009-12-22 07:57:06Z

1

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'

answered Dec 22, 2009 at 7:57

Disillusioned

14.9k3 gold badges47 silver badges80 bronze badges

Collectives™ on Stack Overflow

Sql select query with where from multiple columns

4 Answers 4

Comments

Comments

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related