What's the logic of the NOT IN clause?

Question

What's the logic of the NOT IN clause?

December 16, 2023 at 1:28 pm

This was removed by the editor as SPAM

Viewing 8 posts - 16 through 22 (of 22 total)

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

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 1

Eirikur Eiriksson wrote:

Jeff Moden wrote:
Eirikur Eiriksson wrote:
Sam-263310 wrote:
@Eirikur That works, of course, but SQL Server won't be able to use any index for that query.
Using indices is not necessarily the same as having better performance, this is a fine example of that exception. 😎 I'll rig up a test harness for demonstration and post it here 😉
Ah.. while I totally understand and appreciate what you're saying there, be careful. Just like Knuth's parable about "pre-optimization", a lot of people will take something like that and run in the totally wrong direction with it. I'm very much looking forward to you demo.
Now when are we going to meet up so you can buy me a burger and I can show you the demo 😉 😎

This is why there is Teams and Zoom. 😀 You can demo what you said you'd post here. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 2

Yes, that is how NOT IN () is defined in ANSI/ISO Standard SQL.

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeffrey Williams SSC Guru Points: 90304 More actions · Answer 3

It seems this thread has gotten off-track. The original question was why does NOT IN behave the way it does - and that is easily explained when you consider the following:

IN (list) is equivalent to = ANY (list) - which matches on any one value being in the list. Therefore, NULL in the list does not have any impact as long as you understand that NULL <> NULL.

NOT IN (list) is equivalent to <> ALL (list) - which performs a check on ALL items in the list. If any of the items match - then the expression is true. The issue here is when the list contains a NULL - then the results will be unknown. You cannot validate that a columns value is equal to an unknown value (NULL) which makes the result of the expression unknown. It doesn't matter how many items are in the list or that none of the other items in the list are not equal to the column value - that NULL in the list cannot be evaluated as anything but unknown.

It isn't something MS got wrong either and it isn't an implementation issue.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Jeffrey Williams wrote:

It seems this thread has gotten off-track.

BWAAA-HAAA!!!! I wonder how that happened. 😀 😀 😀

Jeffrey Williams wrote:

Maybe I am off-base here, but...

And, not to worry... you're not the only one but that's what makes this site so good... discussions are encouraged!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

jcelko212 32090 wrote:

Yes, that is how NOT IN () is defined in ANSI/ISO Standard SQL.

Thanks for the confirmation on that, Joe. Do you happen to have a link for that or a copy of the "definition" in the Standard?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 6

SQL FOR SMARTIES 5th edition, chapter 18.2 goes into a lot of painful details znd works it all out

Please post DDL and follow ANSI/ISO standards when asking for help.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 7

jcelko212 32090 wrote:

SQL FOR SMARTIES 5th edition, chapter 18.2 goes into a lot of painful details znd works it all out

Thanks, Joe!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)