Reverse arguments in an IN statement?

Question

Post reply

Reverse arguments in an IN statement?

Renée

SSC-Addicted

Points: 470
More actions
June 23, 2011 at 9:55 am

#240463

What is the most elegant way to engineer the equivalent of an IN statement with reversed arguments in T-SQL?
As in... instead of something like WHERE fldFieldA IN('x','y','z'), I need something equivalent to WHERE 'x' IN (fldFieldA, fldFieldB, fldFieldC).
In other words, I've got one value to look for, but I need to look for it IN several columns. If the value is found in any of those columns, I want the record returned.
Thanks!
[font="Courier New"]_{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ SQL newbie hoping to learn ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~}[/font]

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

i think you would need to use something like

where 1 = case when (col1 = x ) or (col2 = x) or (col3 = x) then 1

else 0

end

Jayanth Kurup[/url]

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 2

No reverse IN, but there's no need to get complex here.

WHERE ColA = 'x' OR ColB = 'x' OR ColC = 'x' ...

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

drew.allen SSC Guru Points: 77014 More actions · Answer 3

Actually what you are looking for is IN itself. There's nothing that requires the left side to be a field and nothing that prevents the right side from being a field. This is very useful in cases like relationships where you might have one field for the relationship and another for the reciprocal relationship.

WHERE 'son' IN (Relationship, Reciprocal_Relationship)

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Renée SSC-Addicted Points: 470 More actions · Answer 4

I'd never seen an example of that, so it didn't occur to me that I could do it, Drew. It works perfectly. *sound of angels and harps and such*

Thank you!

[font="Courier New"]_{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ SQL newbie hoping to learn ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~}[/font]

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 5

drew.allen (6/23/2011)
Actually what you are looking for is IN itself. There's nothing that requires the left side to be a field and nothing that prevents the right side from being a field. This is very useful in cases like relationships where you might have one field for the relationship and another for the reciprocal relationship.

The below was edited after Gail's post to make sure I wasn't distributing bad information. It was edited to be more accurate.

Be aware this technique is non-SARGable without very different indexing. The reason you rarely see this method (though valid) is because if it's your only clause against a table, ~~it *will* require a scan~~ it needs very different indexing maintenance, and causes all sorts of interesting results if your IN contains multiple tables: 'x' IN (a.col1, b.col3, f.col2).

It's as bad, optimization wise, as a major OR clause. Which sometimes you just need, so don't sweat that part. Just wanted to make sure you were aware of some of the pitfalls of the method before it starts being shoved into code without performance checks, Renee. 🙂

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

Renée SSC-Addicted Points: 470 More actions · Answer 6

Thank you, Craig! That all makes sense. I am using it this time but noted what you said for future use. 🙂

[font="Courier New"]_{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ SQL newbie hoping to learn ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~}[/font]

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 7

ORs are fully SARGable, they just require a very different indexing setup than AND. http://sqlinthewild.co.za/index.php/2011/05/03/indexing-for-ors/

Also note that the parser/algebratiser expands IN into a series of OR before optimisation begins.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Renée SSC-Addicted Points: 470 More actions · Answer 8

Interesting stuff. Thanks, Gail. I'm learning more here in a couple of posts than I did on my own for days tearing my hair out!

[font="Courier New"]_{~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~ SQL newbie hoping to learn ~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~}[/font]

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 9

GilaMonster (6/23/2011)
ORs are fully SARGable, they just require a very different indexing setup than AND. http://sqlinthewild.co.za/index.php/2011/05/03/indexing-for-ors/
Also note that the parser/algebratiser expands IN into a series of OR before optimisation begins.

Hmm, I must say, that's some lovely foot I'm eating at the moment. Someone pass the A-1?

You're correct, of course. I just really didn't want to get that deep, and cross-table OR's are rather ugly, which is what I really meant to highlight and did it poorly, apparently.

I edited my post for accuracy.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA