SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Not In


Not In

Author
Message
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10270 Visits: 5157
Comments posted to this topic are about the item Not In

Igor Micev,
My blog: www.igormicev.com
bitbucket-25253
bitbucket-25253
SSCoach
SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)SSCoach (15K reputation)

Group: General Forum Members
Points: 15623 Visits: 25280
Nice question

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Kameswari Aravindh
Kameswari Aravindh
SSCrazy
SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2265 Visits: 177
Good Question. Thanks :-D
vk-kirov
vk-kirov
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4336 Visits: 4408
Nice question, although the 'explanation' is confusing and does not explain anything.

To find the real cause of the observed behavior, we can do a simple conversion. The statement
s.Stat NOT IN (NULL, 'Idle')

is equal to
(s.Stat <> NULL) AND (s.Stat <> 'Idle')

The comparison of any value with a NULL results to UNKNOWN, hence the result of the NOT IN condition is UNKNOWN for every row in the Statuses table (UNKNOWN, but not FALSE, as erroneously stated in the explanation). This is why the result set is empty.
Nils Gustav Stråbø
Nils Gustav Stråbø
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3631 Visits: 3575
Good question that demonstrates the pitfalls of NULL values in IN.

I have to agree with vk-kirov that the explanation is wrong. The result of the query has nothing to do with mismatch in data types, but is caused by the query processor converting the NOT IN statement to a series of <> operators.

Just to prove that is has nothing to do with non-matching data types, but everything to do with the NULL value:
create table #table(id int)

insert into #table
values
(1),
(2),
(3),
(4),
(5),
(null)

declare @notin int

-- The data type of @notin and column [id] matches, but still the query returns no rows.
-- The execution plan shows why (the <> comparison with the NULL valued @notin variable)
select * from #table
where id not in (@notin,1)




Attachments
qp.png (539 views, 26.00 KB)
baabhu
baabhu
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2459 Visits: 1218
+ 1.

Nice question.
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10270 Visits: 5157
Hi,

I agree with you.
From the reference
"If the value of test_expression/subquery is equal to any value returned by subquery or is equal to any expression from the comma-separated list, the result value is TRUE; otherwise, the result value is FALSE" - so the result UNKNOWN seems to be covered (or explained) by FALSE.

Alright, next time I'll try not to miss some things like this one.

Thanks

Igor Micev,
My blog: www.igormicev.com
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10270 Visits: 5157
Hi,

I agree with you.
From the reference
"If the value of test_expression/subquery is equal to any value returned by subquery or is equal to any expression from the comma-separated list, the result value is TRUE; otherwise, the result value is FALSE" - so the result UNKNOWN seems to be covered (or explained) by FALSE.

Alright, next time I'll try not to miss some things like this one.

Thanks

Igor Micev,
My blog: www.igormicev.com
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10270 Visits: 5157
Hi,

I agree with you.
From the reference
"If the value of test_expression/subquery is equal to any value returned by subquery or is equal to any expression from the comma-separated list, the result value is TRUE; otherwise, the result value is FALSE" - so the result UNKNOWN seems to be covered (or explained) by FALSE.

Alright, next time I'll try not to miss some things like this one.

Thanks

Igor Micev,
My blog: www.igormicev.com
Igor Micev
Igor Micev
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10270 Visits: 5157
No, I met this occasionally. It's inspired by practice.

Igor Micev,
My blog: www.igormicev.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search