Post reply

Is that strange behavior?

  • August 29, 2012 at 12:38 pm

    #266781

    I am trying to find all users who has no activity and in activity tables we do have some records without userid so it has null values on some records. I am using following statement to find user list:

    SELECT * FROM User WHERE UserId NOT IN (SELECT UserId FROM UserActivity) - This is not returning any user.

    If I use

    SELECT * FROM User WHERE UserId NOT IN (SELECT UserId FROM UserActivity WHERE UserId is Not Null) - Returns expected results.

    Is that a strange behavior or null value in subquery is not comparable?

  • August 29, 2012 at 12:46 pm

    #1531583

    Expected behaviour

    http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • August 29, 2012 at 1:09 pm

    #1531599

    GilaMonster (8/29/2012)

    Expected behaviour

    http://sqlinthewild.co.za/index.php/2010/02/18/not-exists-vs-not-in/

    Thanks Gail, your article make sense and I will use EXIST instead of IN now.

Viewing 3 posts - 1 through 3 (of 3 total)

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