November 7, 2008 at 11:14 am
This isn't a problem anymore (it almost was, I was 90% of the way done with a forum post when I figured it out), but due to how weird/interesting it is, I decided to post it anyways.
I wrote a very simple query:
SELECT DISTINCT C.Cust_Num
FROM Customer C
WHERE C.Cust_Num NOT IN (SELECT DISTINCT Cust_Num FROM fs_Unit)
I was expecting approximately 12k rows. I got... none.
The outer query:
SELECT DISTINCT C.Cust_Num FROM Customer C
Returns 21K rows.
The NOT IN query alone:
SELECT DISTINCT Cust_Num FROM fs_Unit
Returns 9K rows.
Changing the NOT IN to simply an IN
SELECT DISTINCT C.Cust_Num
FROM Customer C
WHERE C.Cust_Num IN (SELECT DISTINCT Cust_Num FROM fs_Unit)
Returns 9K rows.
So, why does the initial query, which should return approximately 12K rows, return nothing, and how would I fix it?
November 7, 2008 at 11:26 am
i think if this query:
SELECT DISTINCT Cust_Num FROM fs_Unit
returns a null in the distinct collection, you'll get no results;
you might need
SELECT DISTINCT Cust_Num FROM fs_Unit WHERE Cust_Num IS NOT NULL
Lowell
November 7, 2008 at 11:29 am
Garadin (11/7/2008)
This isn't a problem anymore (it almost was, I was 90% of the way done with a forum post when I figured it out), but due to how weird/interesting it is, I decided to post it anyways.I wrote a very simple query:
SELECT DISTINCT C.Cust_NumFROM Customer C
WHERE C.Cust_Num NOT IN (SELECT DISTINCT Cust_Num FROM fs_Unit)
I was expecting approximately 12k rows. I got... none.
The outer query:
SELECT DISTINCT C.Cust_Num FROM Customer CReturns 21K rows.
The NOT IN query alone:
SELECT DISTINCT Cust_Num FROM fs_UnitReturns 9K rows.
Changing the NOT IN to simply an IN
SELECT DISTINCT C.Cust_NumFROM Customer C
WHERE C.Cust_Num IN (SELECT DISTINCT Cust_Num FROM fs_Unit)
Returns 9K rows.
So, why does the initial query, which should return approximately 12K rows, return nothing, and how would I fix it?
What does this return
SELECT DISTINCT
C.Cust_Num
FROM
Customer C
left outer join fs_Unit F
on (C.Cust_Num = F.Cust_Num)
WHERE
F.Cust_Num is null
November 7, 2008 at 11:30 am
That's what I ended up doing to fix it, but I still don't fully understand why it should be necessary. Could you explain why a single NULL invalidates my entire result set?
November 7, 2008 at 11:35 am
Lynn,
That runs fine, and gives the 12K rows expected. There's no reason I couldn't have done it that way, it was just blowing my mind that the above way wasn't working.
November 7, 2008 at 11:44 am
IN or NOT IN turns into a series of equalities or non-equalities. Which means NULL is a killer, since that will alway return FALSE (since "NULL is neither equal nor not equal to any value")
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 7, 2008 at 11:52 am
Matt Miller (11/7/2008)
IN or NOT IN turns into a series of equalities or non-equalities. Which means NULL is a killer, since that will alway return FALSE (since "NULL is neither equal nor not equal to any value")
I should rephrase that - it's killer in the NOT IN scenario. Why? Look at what it turns into.
WHERE a in (1,2,3,NULL)
turns into
WHERE
a=1 OR
A=2 OR
A=3 OR
A=NULL --unknown
all it takes is one of the first three to be true and the whole WHERE is true.
Now, NOT IN looks like:
WHERE a not in (1,2,3,NULL)
turns into
WHERE
(a<>1) AND
(A<>2) AND
(A<>3) AND
(A<>NULL) --unknown
Now - ALL of these statements would have to evaluate to true, which can never happen.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
November 7, 2008 at 12:01 pm
Matt:
Thanks, that makes sense. I thought it might be something like that, but that definitely makes it more clear.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply