"NOT IN" Query Returns No Rows

Russell Scheinberg, MCSE Business Intelligence
Russell Scheinberg, MCSE Business Intelligence used Ask the Experts™
on
I have been playing with this query for some time now and can't figure out what I am missing. It is probably something simple but I can't for the life of me see it. I have a query in which I am simply trying to get the a list of values that are not in another list. I am using CTEs as much of this is sub-querying the same view.

The query looks like this:

;WITH CTE_NONULLS AS (
SELECT MAX(CabinetID) AS CabinetID, TIN FROM [Trades].[dbo].[vw_CabinetNG_New] 
WHERE (Source = 'DST' OR Source = 'DAZL') 
  AND (Address1 IS NOT NULL) AND (TIN IS NOT NULL AND TIN <> '000-00-0000')
  GROUP BY TIN),
CTE_NULLS AS (
SELECT MAX(CabinetID) AS CABINETID, FirstName, TIN FROM [Trades].[dbo].[vw_CabinetNG_New]
WHERE (Source = 'DST' OR Source = 'DAZL') 
  AND (Address1 IS NOT NULL) AND ((FIRSTNAME IS NOT NULL AND FIRSTNAME <> '') OR (LASTNAME IS NOT NULL AND LASTNAME <> ''))
  AND (TIN IS NULL OR TIN = '000-00-0000')
  GROUP BY FirstName, TIN),
CTE_FC AS (
SELECT TIN FROM [dbo].[vw_CabinetNG_New] 
WHERE Source = 'FirstClearing'
GROUP BY TIN
)
SELECT TIN
	  , CASE WHEN [FirstName] IS NOT NULL THEN [FirstName] + ' ' ELSE '' END
	   + CASE WHEN [LastName] IS NOT NULL THEN [LastName] ELSE '' END  
	   AS [Full Name]
	  ,CASE WHEN [AdditionalName] IS NULL THEN '' ELSE [AdditionalName] END AS [AdditionalName]
      ,CASE WHEN [Address1] IS NULL THEN '' ELSE [Address1] END AS [Address1]
      ,CASE WHEN [Address2] IS NULL THEN '' ELSE [Address2] END AS [Address2]
      ,CASE WHEN [Address3] IS NULL THEN '' ELSE [Address3] END AS [Address3]
      ,[City]
      ,[State]
      ,[ZipCode]
	  ,LastUpdated
	  ,[Source]
FROM [Trades].[dbo].[vw_CabinetNG_New] 
WHERE CabinetID IN
(SELECT CabinetID FROM CTE_NONULLS UNION ALL SELECT CabinetID FROM CTE_NULLS)
AND TIN NOT IN (SELECT TIN FROM CTE_FC) 
ORDER BY TIN

Open in new window


Pretty simple. If I end the query at the UNION ALL join and leave out the "AND TIN NOT IN...." I get about 5100 records. If I add the "AND TIN NOT IN...." I get 0 records. However, if I change the "NOT IN" to "IN" I get about 2300 matches, which is what it should be. So what happens to my 2800 records that do not match when I use the "NOT IN" instead of the "IN"?

Like I said it is probably going to be something crazy simple but I am stumped.

Thanks ahead of time for you help.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
David ToddSenior Database Administrator

Commented:
Hi,

Firstly, because of the sorting in cte_fc, that is likely to be expensive.

what does this query give?
select tin, count( * )
from dbo.vw_CabinetNG_New
group by
    tin
order by
    count( * ) desc
;

Open in new window


Then do a count of each of the lines from cte_nonulls and cte_nulls.

HTH
  David

PS Doing a union all of cte_nonulls and cte_nulls LOGICALLY defeats the purpose of the exercise!

I suspect that your query can be simplified to:
use Trades
go

select 
	v.TIN
	, isnull( v.FirstName + ' ', '' ) = isnull( v.LastName, '' ) as [Full Name]
	, isnull( v.AdditionalName, '' ) as AdditionalName
	, isnull( v.Address1, '' ) as Address1
	, isnull( v.Address2, '' ) as Address2
	, isnull( v.Address3, '' ) as Address3
	, v.City
	, v.State
	, v.ZipCode
	, v.LastUpdated
	, v.Source
from dbo.vw_CabinetNG_New  v
where 
	v.Source = 'FirstClearing' 
order by 
	v.TIN 
;

Open in new window

Commented:
"NOT IN" can lead to performance problems; try to use join syntax instead:

...
FROM [Trades].[dbo].[vw_CabinetNG_New] LEFT OUTER JOIN CTE_FC
ON vw_CabinetNG_New.TIN = CTE_FC.TIN
WHERE CabinetID IN
(SELECT CabinetID FROM CTE_NONULLS UNION ALL SELECT CabinetID FROM CTE_NULLS)
AND CTE_FC.TIN IS NULL
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
Whilst you are re-using the same table, you are not re-using any single CTE, so there is no particular performance advantage by using the CTEs as you have them now. e.g.this would work without the third CTE
      AND TIN NOT IN (
                  SELECT
                        TIN
                  FROM [dbo].[vw_CabinetNG_New]
                  WHERE Source = 'FirstClearing'
                  GROUP BY
                        TIN
      )

Open in new window

So you are asking for TIN NOT IN (list of TIN where Source = 'FirstClearing')

why not simply use
AND SOURCE <> 'FirstClearing'
instead
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
I think it boils down to:
SELECT
*
FROM [Trades].[dbo].[vw_CabinetNG_New]
WHERE (Source = 'DST' OR Source = 'DAZL')
AND Address1 IS NOT NULL
AND (
     (TIN IS NOT NULL AND TIN <> '000-00-0000')
  OR 
     ( 
               (
                 (FIRSTNAME IS NOT NULL AND FIRSTNAME <> '')
            OR 
                 (LASTNAME IS NOT NULL AND LASTNAME <> '')
                )
            AND (TIN IS NULL OR TIN = '000-00-0000')
     )
   )
AND Source <> 'FirstClearing'
ORDER BY
      TIN

Open in new window

Associate Principal Engineer
Top Expert 2014
Commented:
HI,

The query below might be containing Null value and hence when it is placed in sub-query with not it clause it is not returning any row

SELECT TIN FROM [dbo].[vw_CabinetNG_New]
WHERE Source = 'FirstClearing'
GROUP BY TIN
Russell Scheinberg, MCSE Business IntelligenceSr. Business Intelligence Analyst

Author

Commented:
Thanks everyone for your help. I actually needed the grouping, which I know is expensive, to deal with the fact that I needed the last TIN entry of many in the table. And this was a query that will not be run on a regular basis. Once a year, so I was not too worried about the cost. That being said the fact that their were null values in the CTE_FC was the culprit. I have had this issue of null values biting me in the past so I am not surprised that it did so again. When I took out the null values in the CTE_FC, I got my 2814 rows as expected. Thanks again for your help. Experts Exchange rocks!!
PortletPaulEE Topic Advisor
Most Valuable Expert 2014
Awarded 2013

Commented:
>>"When I took out the null values in the CTE_FC, I got my 2814 rows as expected."
that's great

but you don't need CTE_FC

CREATE TABLE Table1
	([TIN] int, [Source] varchar(14))
;
	
INSERT INTO Table1
	([TIN], [Source])
VALUES
	(1, 'FirstClearing'),
	(2, 'FirstClearing'),
	(3, 'FirstClearing'),
	(4, 'FirstClearing'),
	(5, 'FirstClearing'),
	(6, 'FirstClearing'),
	(7, 'FirstClearing'),
	(8, 'FirstClearing'),
	(9, 'FirstClearing'),
	(10, 'FirstClearing'),
	(11, 'SecondClearing'),
	(12, 'SecondClearing'),
	(13, 'SecondClearing'),
	(14, 'SecondClearing'),
	(15, 'SecondClearing'),
	(16, 'SecondClearing'),
	(17, 'SecondClearing'),
	(18, 'SecondClearing'),
	(19, 'SecondClearing'),
	(20, 'SecondClearing')
;

Open in new window

    WITH cte_fc AS ( SELECT TIN FROM table1 WHERE SOURCE = 'FirstClearing')
    
    SELECT
    *
    FROM table1
    WHERE TIN NOT IN (SELECT TIN FROM cte_fc)
    

**[Results][2]**:
    
    | TIN |         SOURCE |
    |-----|----------------|
    |  11 | SecondClearing |
    |  12 | SecondClearing |
    |  13 | SecondClearing |
    |  14 | SecondClearing |
    |  15 | SecondClearing |
    |  16 | SecondClearing |
    |  17 | SecondClearing |
    |  18 | SecondClearing |
    |  19 | SecondClearing |
    |  20 | SecondClearing |


**Query 2**:

    SELECT
    *
    FROM table1
    WHERE SOURCE <> 'FirstClearing'
    

**[Results][3]**:
    
    | TIN |         SOURCE |
    |-----|----------------|
    |  11 | SecondClearing |
    |  12 | SecondClearing |
    |  13 | SecondClearing |
    |  14 | SecondClearing |
    |  15 | SecondClearing |
    |  16 | SecondClearing |
    |  17 | SecondClearing |
    |  18 | SecondClearing |
    |  19 | SecondClearing |
    |  20 | SecondClearing |



  [1]: http://sqlfiddle.com/#!3/0dd65/4

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial