Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Advantages of INTERSECT over INNER JOIN Expand / Collapse
Author
Message
Posted Tuesday, January 14, 2014 9:54 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 13,082, Visits: 12,547
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)
Post #1530763
Posted Tuesday, January 14, 2014 10:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:48 AM
Points: 106, Visits: 510
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 ?
Post #1530785
Posted Tuesday, January 14, 2014 11:35 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 10:56 AM
Points: 630, Visits: 2,134
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)
Post #1530810
Posted Tuesday, January 14, 2014 11:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, October 17, 2014 9:48 AM
Points: 106, Visits: 510
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)
Post #1530815
Posted Tuesday, January 14, 2014 11:54 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Today @ 10:56 AM
Points: 630, Visits: 2,134
Yes. How else would it do it?

Post #1530816
Posted Tuesday, January 14, 2014 12:12 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:50 AM
Points: 13,082, Visits: 12,547
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)
Post #1530823
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse