SQL Clone
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
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63478 Visits: 17966
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 Modens 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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1285 Visits: 1213
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
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: 3556 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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1285 Visits: 1213
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
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: 3556 Visits: 3149
Yes. How else would it do it?
Sean Lange
Sean Lange
SSC Guru
SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)SSC Guru (63K reputation)

Group: General Forum Members
Points: 63478 Visits: 17966
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 Modens 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