Not In

Question

Not In

Viewing 15 posts - 31 through 45 (of 48 total)

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

TomThomson SSC Guru Points: 104773 More actions · Answer 1

Nice question and right answer. But, as a couple of people have pointed out, the explanation is wrong and misleading (it certainly misled omyrox, as this comment makes clear).

Tom

Hugo Kornelis SSC Guru Points: 64790 More actions · Answer 2

I don't need to repeat how the actual explanation is incorrect, as this has been pointed out already. But I don't think anyone has covered what will happen in case of a type mismatch between the operand before the IN and (one of) the operands inside the IN.

In such a case, the operand with lower data type precedence will be implicitly converted to the higher predence. If that's successful, the query returns the expected result. If not, it will fail with a runtime error.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

EL Jerry SSCertifiable Points: 7063 More actions · Answer 3

Thanks for the question, made me think and research for a while.

To me the explanation was clear. When stating

s.Statuses NOT IN (null, 'Idle')

you are trying to compare a data column attribute/property (null) against the actual value of the column, which returns UNKNOWN. Thus, UNKNOWN not being True (neither False), it will not set any row as selectable to return to the SELECT statement.

Thanks,

"El" Jerry.

"A watt of Ottawa" - Gerardo Galvan

To better understand your help request, please follow these best practices.[/url]

DBA_Dom SSCrazy Points: 2902 More actions · Answer 4

Dang it. Should have thought about this one a little more. Good question.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

interesting question.

Thanks

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 6

good question. dealt with that issue in some code on Monday! :w00t:

Cheers

Revenant SSC-Forever Points: 42467 More actions · Answer 7

Revenant

SSC-Forever

Points: 42467

March 1, 2012 at 3:14 pm

#1453920

I really enjoyed this one. Thanks!

tilew-948340 Hall of Fame Points: 3431 More actions · Answer 8

Stuart Davies (3/1/2012)
Good question - it reminds me why I do not like nulls

+1

Thanks

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 9

tilew-948340 (3/1/2012)
Stuart Davies (3/1/2012)
Good question - it reminds me why I do not like nulls
+1
Thanks

+1 ... Thanks 🙂

Thanks

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 10

Great question, thanks!

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

rfr.ferrari SSCertifiable Points: 6879 More actions · Answer 11

very good question!!!!

thanks!!!!

[font="Times New Roman"]rfr.ferrari[/font]
DBA - SQL Server 2008
MCITP | MCTS

remember is live or suffer twice!
the period you fastest growing is the most difficult period of your life!

Rich Mechaber SSChampion Points: 11130 More actions · Answer 12

I got this right b/c of a great article I've kept bookmarked, "Ten Common SQL Programming Mistakes":

http://www.simple-talk.com/content/print.aspx?article=783

Ratchev lists it as item #1, with a straightforward, lucid explanation as to why the query fails, giving a few different T-SQL approaches to correctly query NOT IN for nullable columns.

Thanks,

Rich

Stéphane Thinel SSC Veteran Points: 277 More actions · Answer 13

Thank you for the question. It made me realize that NOT IN (x, y) really means NOT IN x AND NOT IN y.

I was thinking more like NOT IN x OR NOT IN y.

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 14

Tough question. Thanks for submitting.

http://brittcluff.blogspot.com/

obasji Grasshopper Points: 11 More actions · Answer 15

You have made a mistake in the correct answer!! The 'not in' was not referring to id. 5 points to me.