locked
not exists RRS feed

  • Question

  •  Hi,

    I am using Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34 (X64)   Aug 19 2014 12:21:34   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

      I had the below sql (plus other joins etc.)
      select * from tableA as tb
      where tb.ID  not in (
        SELECT  cr.TR_TRANSACTION_ID
        FROM dbo.Unified_Trade_Repository cr
        UNION ALL
        SELECT  U.TR_TRANSACTION_ID
        FROM dbo.Credit_Trade_Repository as U            
        )
    This was producing no rows


    Changing to the equivalent statement below works normally and produces the results I was expecting. I checked  all related columns and all appear to be Int just in case there was some different data type in the comparison.

    select * from tableA as tb

    where not exists (
        SELECT  cr.TR_TRANSACTION_ID
        FROM dbo.Unified_Trade_Repository cr
        where tb.ID  = cr.TR_TRANSACTION_ID
        UNION ALL
        SELECT  U.TR_TRANSACTION_ID
        FROM dbo.Credit_Trade_Repository as U
        where tb.ID  = U.TR_TRANSACTION_ID

        )

    Can anyone explain why the different results. Looks like a bug in sql server 2008.

    Thanks,

    Panos.

    Wednesday, August 29, 2018 12:19 PM

Answers

All replies

  • Does the column cr.TR_TRANSACTION_ID  have NULLs as well as the data?

    use EXISTS as it in you example correlated subquery


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Wednesday, August 29, 2018 12:21 PM
    Answerer
  • As Uri wrote, it's causes by NULL values in the Sub select, try it with

      select * from tableA as tb
       where tb.ID  not in (
         SELECT  cr.TR_TRANSACTION_ID
         FROM dbo.Unified_Trade_Repository cr
         WHERE NOT cr.TR_TRANSACTION_ID IS NULL
         UNION ALL
         SELECT  U.TR_TRANSACTION_ID
         FROM dbo.Credit_Trade_Repository as U            
         WHERE NOT U.TR_TRANSACTION_ID IS NULL
         )


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by panlondon Thursday, August 30, 2018 11:21 AM
    Wednesday, August 29, 2018 12:23 PM
  • Hi Uri,Yes it may have nulls but not normally. Why it should be behave this way though. Just because there might be a row with a NULL it breaks the logic?. It should be fixed by Microsoft IMO.

    Thank you.


    • Edited by panlondon Wednesday, August 29, 2018 12:54 PM
    Wednesday, August 29, 2018 12:53 PM
  • Yes, NULL is considered as unknown value, you can fix it  in IN clause to eliminate nulls as Olaf showed

    WHERE NOT cr.TR_TRANSACTION_ID IS NULL

     But I would prefer EXISTS/NOT EXISTS

    http://www.sqlbadpractices.com/using-not-in-operator-with-null-values/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence


    Wednesday, August 29, 2018 12:58 PM
    Answerer
  • I understand the solution but I still think it doesn't behave the way it should. Do you think I have a valid case for a bug report for sql server?
    Wednesday, August 29, 2018 1:01 PM
  • No, it behaives as expected and as documented, see NULL and UNKNOWN (Transact-SQL)

    Olaf Helper

    [ Blog] [ Xing] [ MVP]



    Wednesday, August 29, 2018 1:08 PM
  • Here is the explanation of what you are experiencing.

    1 NOT IN (2, NULL)

    -> NOT ((1 = 2) OR (1 = NULL))

    -> (1 <> 2) AND (1 <> NULL)

    Since "1 <> NULL" is NULL then the AND expression is Unknown.

    It is by design.


    AMB

    Some guidelines for posting questions...

    AYÚDANOS A AYUDARTE, guía básica de consejos para formular preguntas

    • Marked as answer by panlondon Thursday, August 30, 2018 11:19 AM
    Wednesday, August 29, 2018 1:11 PM
  • Hi panlondon,

    Per your description, I create a similar table structure and add some sample data into it. You can see the difference between NOT IN and NOT EXISTS clearly.

     

    As we know ,a NULL value is an unknown or missing value. In my following script, the script which use NOT in is equal to ''select 'GHK' where 'GHK' <> 'AXC' and 'GHK' <>NULL  and  'GHK' <>'AYC' and  'GHK' <>'FIK' ''. And  the result of 'GHK' <>NULL is UNKNOWN, so the predicate evaluates to UNKNOWN, and you will not get any rows.

     

    The solution is to use IS NOT NULL to filter out the NULL values in the NOT IN clause . Also ,NOT EXISTS gives us the right results because it uses two-valued Boolean logic, only TRUE or False, to filter out the rows.

     

    Here is the example script.

     

    ---drop table tableA
    ---drop table tableB
    create table tableA
    (
    id varchar(10)
    )
    create table tableB
    (
    TR_TRANSACTION_ID varchar(10)
    )
    insert into tableA values 
    ('GHK')
    insert into tableB values 
    ('AXC'),(NULL),('AYC'),('FIK')
    
    ------------- example----------
    -------not in --------
    select * 
    from tableA a 
    where id not in 
    (
    select TR_TRANSACTION_ID 
    from tableB
    )
    /*
    id
    ----------
    */
    -------not exists --------
    select * 
    from tableA a 
    where not exists 
    (
    select TR_TRANSACTION_ID 
    from tableB b 
    where b.TR_TRANSACTION_ID =a.id
    )
    /*
    id
    ----------
    GHK
    */
    
    
    -------------solution ----------
    -------not in --------
    select * from 
    tableA a 
    where id not in 
    (
    	select TR_TRANSACTION_ID 
    	from tableB
    	where TR_TRANSACTION_ID is not null 
    )
    /*
    id
    ----------
    GHK
    */
    


    If you have any questions, please let me know.

     

    Best Regards ,

    Rachel 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, August 30, 2018 7:22 AM