SQLTeam.com | Weblogs | Forums

Compare Distinct from two queries


#1

First I am a totoal noob as SQL and my company just thew me in the deep water and told me to swim, and so far i'm treading water and doing the doggie paddle lol.

That being said; here are 3 queries, I don't understand:

SELECT distinct [CustomerNumber] as '2013number'
FROM [Services]
where ServiceArea = 'Configuration' and ServiceYear = 2013 and CustomerNumber like '%320263%'
order by CustomerNumber asc /*The above returns nothing*/

-----------------------------------------------------------------------------------------

SELECT distinct [CustomerNumber] as '2014number'
FROM [Services]
where ServiceArea = 'Configuration' and ServiceYear = 2014 and CustomerNumber like '%320263%'

order by CustomerNumber asc /*The above returns 2 values 320263 and 3202636*/

-----------------------------------------------------------------------------------

Select distinct CustomerNumber from ServicesPL where serviceyear = 2014 and ServiceArea = 'Configuration' 
and CustomerNumber not in (Select distinct CustomerNumber from Services where serviceyear = 2013 and ServiceArea = 'Configuration' )

/*The above returns nothing*/

I do not understand how the last qurey returns nothing, I think it should return 320263 and 3202636.

Please help and thanks for helping.:smiley:

p.s. I've cross posted this on another SQL forum.


#2

Solved
There's an issue when using NOT IN and the inner query returns a NULL value. In that case, it won't return any rows. Corrected it by adding an additional condition in the WHERE clause.

SELECT DISTINCT CustomerNumber 
FROM ServicesPL 
WHERE serviceyear = 2014 
AND ServiceArea = 'Configuration' 
AND CustomerNumber NOT IN (SELECT CustomerNumber 
                        FROM Services 
                        WHERE serviceyear = 2013 
                        AND ServiceArea = 'Configuration'
                        AND CustomerNumber IS NOT NULL );

I was not seeing NULL values before because I used LIKE which prevented any NULL values.
I changed it to this:

SELECT [CustomerNumber] as '2014number'
FROM [Services]
WHERE ServiceArea = 'Configuration' and ServiceYear = 2014 and CustomerNumber like '%320263%'
EXCEPT
SELECT [CustomerNumber]
FROM [Services]
WHERE ServiceArea = 'Configuration' and ServiceYear = 2013 and CustomerNumber like '%320263%'
ORDER BY CustomerNumber ASC;