Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Advantages of INTERSECT over INNER JOIN


Advantages of INTERSECT over INNER JOIN

Author
Message
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16680 Visits: 17038
j-1064772 (1/14/2014)
Thank you all for your inputs, I now understand what INTERSECT and EXCEPT bring to the party.

I am however still trying to understand why the following occurs:

I checked that NOT IN fails (no record returned because of the extra null) as opposed to IN which does work.

WHERE ProductID IN (1, 2, 3, 4, NULL) translates as
WHERE ProductID = 1 OR ProductID = 2 OR ProductID = 3 OR ProductID = 4 OR ProductID = NULL

WHERE ProductID NOT IN (1, 2, 3, 4, NULL) translates as
WHERE NOT (ProductID = 1 OR ProductID = 2 OR ProductID = 3 OR ProductID = 4 OR ProductID = NULL)

Using Boolean algebra the last expression should yield the same results
WHERE (Product != 1) AND (Product != 2) AND (Product != 3) AND (Product != 4) AND (ProductID != NULL)

The last part AND (ProductID != NULL) would explain why no record would be returned since
<anything> = NULL always returns false.

Assuming of course that this is indeed the way SQL Server evaluates logical expressions.

If not, then I am still at a loss as to why the
NOT IN fails.

By the way, I enjoyed the crystal-clear way to include a null in a results set without having to actually put one in the table as done by Mr. Lange.







The NOT IN is not failing, it is just not doing what you think it should. ;-)

That is the problem with NOT IN, if there is a NULL as one of the conditions it will not return any values because of the NULL.

We can do these checks without a table at all. You can just use some values to test.


select 'Yes'
where 1 not in (1, null)



BTW, I am glad you saw how you use UNION to force a NULL into a result set. Makes testing and such a LOT easier.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
j-1064772
j-1064772
SSC-Addicted
SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)

Group: General Forum Members
Points: 403 Visits: 1202
You are right about the choice of word "failing". It works in its own way, not the according to what I expected.

Still I am curious as to HOW a NOT IN is interpreted. Is my suggestion of the optimizer using a Boolean equivalent a valid explanation ?
Nevyn
Nevyn
SSC Eights!
SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)

Group: General Forum Members
Points: 962 Visits: 3149
Edit: just realized you already proposed most of the answer.

Yes, its because the logic of a not in is to check <> AND for each value.


In the meantime, the IN doesn't really 'work' either. It won't find the nulls, it just doesn't 'break' the other values.

Try this:

select 'yes'
where null in (1,null)
j-1064772
j-1064772
SSC-Addicted
SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)SSC-Addicted (403 reputation)

Group: General Forum Members
Points: 403 Visits: 1202
So the optimizer IS actually using the equivalent from Boolean algebra ?

As I wrote before:

WHERE NOT (ProductID = 1 OR ProductID = 2 OR ProductID = 3 OR ProductID = 4 OR ProductID = NULL)

Using Boolean algebra the last expression should yield the same results

WHERE (Product != 1) AND (Product != 2) AND (Product != 3) AND (Product != 4) AND (ProductID != NULL)

Nevyn
Nevyn
SSC Eights!
SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)SSC Eights! (962 reputation)

Group: General Forum Members
Points: 962 Visits: 3149
Yes. How else would it do it?
Sean Lange
Sean Lange
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16680 Visits: 17038
j-1064772 (1/14/2014)
So the optimizer IS actually using the equivalent from Boolean algebra ?

As I wrote before:

WHERE NOT (ProductID = 1 OR ProductID = 2 OR ProductID = 3 OR ProductID = 4 OR ProductID = NULL)

Using Boolean algebra the last expression should yield the same results

WHERE (Product != 1) AND (Product != 2) AND (Product != 3) AND (Product != 4) AND (ProductID != NULL)


What you are struggling here with is the concept of NULL. Remember that NULL is not a value placeholder. That is why nothing can equal NULL.

NULL is a representation for a missing value. This is why your check will not return the results you are expecting.

Keeping with examples of not requiring a table take a look at this.


declare @ProductID int = 10

select 'Yes'
--where 1 not in (1, null)
WHERE NOT (@ProductID = 1 OR @ProductID = 2 OR @ProductID = 3 OR @ProductID = 4 OR @ProductID = NULL)



Essentially this is looking for any of the known values OR an unknown value. It can't simultaneously be any of a given known values OR an unknown value.

Take the above example and with a slight modification it will do exactly what you are asking.


declare @ProductID int = 10

select 'Yes'
WHERE NOT (@ProductID = 1 OR @ProductID = 2 OR @ProductID = 3 OR @ProductID = 4 OR @ProductID is NULL)



You shorten this to use NOT IN like you were but you have another slight change.


declare @ProductID int = 10

select 'Yes'
where @ProductID not in (1, 2, 3, 4) OR @ProductID IS NULL



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
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