Post reply

SELECT NOT IN (Trouble)

  • October 25, 2006 at 2:27 pm

    #69431

    Hi All

    Can you please let me know what am I doing wrong here?

    SELECT NOT IN is not returning rows that should be returned.

    Select

    * from [CIATStaging].[dbo].[ResponseSelectionDictionary] where SelectionValue Like 'False%' OR SelectionValue Like 'True%'

    --Returns 2 Rows with SelectionValue 'True' and 'False'

    SELECT

    * FROM CADS.dbo.AssessmentSelection where SelectionValue Like 'False%' OR SelectionValue Like 'True%'

    --Returns 0 Rows

    SELECT

    DISTINCT R.[SelectionValue] RS, A.[SelectionValue] [AS] FROM [CIATStaging].[dbo].[ResponseSelectionDictionary] R LEFT OUTER JOIN CADS.dbo.AssessmentSelection A ON A.[SelectionValue] = R.[SelectionValue]WHERE A.[SelectionValue] IS NULL

    --Returns 2 Rows with 2 NULLs in [AS]

    SELECT

    DISTINCT [SelectionValue] FROM [CIATStaging].[dbo].[ResponseSelectionDictionary] WHERE [SelectionValue] NOT IN (SELECT DISTINCT [SelectionValue] FROM CADS.dbo.AssessmentSelection)

    --Returns 0 rows

    I have already used NOT IN Clause in many places in my code and it might prove disasterous in future. Any Ideas other than changing the code to LEFT OUTER JOIN?

    Thanks

    Gary

     

  • October 25, 2006 at 2:42 pm

    #668008

    Try this:

    SELECT DISTINCT [SelectionValue] FROM [CIATStaging].[dbo].[ResponseSelectionDictionary] WHERE isnull([SelectionValue],'') NOT IN (SELECT DISTINCT [SelectionValue] FROM CADS.dbo.AssessmentSelection)

  • October 25, 2006 at 2:49 pm

    #668010

    Hi Sreejith

    Same - 0 rows returned

  • October 25, 2006 at 3:52 pm

    #668029

    The issue is with NULL values. NULL don't equal anything and they don't not equal anything. NULL is like saying I don't know what this value is. So, when you do the NOT IN, you won't get NULLs returned because SQL Server can't tell if it is or isn't in the subSelect.

    -SQLBill

  • October 25, 2006 at 3:53 pm

    #668031

    You could try:

    SELECT DISTINCT [SelectionValue] FROM [CIATStaging].[dbo].[ResponseSelectionDictionary] WHERE [SelectionValue] NOT IN (SELECT DISTINCT [SelectionValue] FROM CADS.dbo.AssessmentSelection)

    OR IS NULL

    -SQLBill

  • October 26, 2006 at 7:29 am

    #668164

    The reason is that a NULL value is in the subquery. Since NULL is a unknown value, all values from the main query are eliminated. Change to this:

    SELECT DISTINCT [SelectionValue] FROM [CIATStaging].[dbo].[ResponseSelectionDictionary] WHERE [SelectionValue] NOT IN (SELECT DISTINCT [SelectionValue] FROM CADS.dbo.AssessmentSelection WHERE [SelectionValue] IS NOT NULL)

     

  • October 26, 2006 at 8:11 am

    #668197

    I ran the above 2 queries - no change = 0 rows.

    The issue is not about NULL values here. LEFT OUTER JOIN returns NULL because it does not find a match in the right table for the rows found in LEFT table. Thats why I get NULLs in LEFT OUTER JOIN query. There are 2 rows in ResponseSelectionDictionary table which I know do not exist in AssessmentSelection.

  • October 26, 2006 at 9:08 am

    #668234

    did you try

    where isnull(selectionvalue,0) <> 0

    ?

     

  • October 26, 2006 at 9:51 am

    #668254

    My apologies to PeterHe, your query actually works. I accidently queried another Server, which has no data. Thanks a Lot Peter!

    I originally thought that only WHERE something = NULL is effected by ANSI NULL setting, but apparently not.

    If I say ANSI NULLS OFF

    Even my query brings back results, without IS NOT NULL and Peter's query works in both cases.

     

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply