May 7, 2002 at 7:37 am
I am stumped...
I am using NOT IN with a subquery. My entire statement is:
select T.TOUR_DATE, T.TOUR_STATUS_CODE
from d_tour T
where T.TOUR_DATE between '03/01/02' and '04/30/02' and T.TOUR_STATUS_CODE = 2 and T.Office_Code in (34,37,40,43,55,65)
and T.LEAD_ID not in (select lead_id from p_contract_lead )
Go
When I run the main query by itself... select T.TOUR_DATE, T.TOUR_STATUS_CODE
from d_tour T
where T.TOUR_DATE between '03/01/02' and '04/30/02' and T.TOUR_STATUS_CODE = 2 and T.Office_Code in (34,37,40,43,55,65) I get a return of 14721 rows.
However, when I include the last line, I get a return of 0 rows, however, I know this is incorrect. So, to test, I change the NOT IN to IN and get 4984 rows returned. Shouldn't I get the difference (14721-4984 = 9737) when I change the IN back to NOT IN?
May 7, 2002 at 9:21 am
Greg
I would try this:
select T.TOUR_DATE, T.TOUR_STATUS_CODE
from d_tour T
left join p_contract_lead on
T.LEAD_ID = p_contract_lead.LEAD_ID
where T.TOUR_DATE between '03/01/02' and '04/30/02' and T.TOUR_STATUS_CODE = 2 and T.Office_Code in (34,37,40,43,55,65)
AND p_contract_lead.LEAD_ID IS NULL
I try to stay away from NOT IN with sub selects if at all possible. Most of the time you can do the same thing with a join.
May 7, 2002 at 11:15 am
After some research, I found that NOT IN using a subquery will return zero rows if any null values are returned in your subquery. Here is an article with a thorough explanation as to why:
http://www.pinnaclepublishing.com/SQ/SQmag.nsf/0/CEEA6CCC09585570852568E20077EC44
May 7, 2002 at 1:08 pm
Thanks, Greg, for posting the link - I was suspected it was something to do with nulls, but the article got complete explanation of it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply