Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    12

    Unanswered: NOT IN vs. NOT EXISTS returning diff results

    select count(orgcode)
    from orglevel
    where orglvl = 1 and exists (select 'x' from empcomp where eecemplstatus = 'A' and eecorglvl1 = orgcode)

    26 rows

    select count(orgcode)
    from orglevel
    where orglvl = 1 and orgcode in (select distinct eecorglvl1 from empcomp where eecemplstatus = 'A')

    26 rows

    select count(orgcode)
    from orglevel
    where orglvl = 1 and not exists (select 'x' from empcomp where eecemplstatus = 'A' and eecorglvl1 = orgcode)

    6 rows

    select count(orgcode)
    from orglevel
    where orglvl = 1 and orgcode not in (select distinct eecorglvl1 from empcomp where eecemplstatus = 'A')

    zero rows


    Confusing the hell out of me .....
    Howard Nugent
    Sr. Business Application Analyst

  2. #2
    Join Date
    Mar 2004
    Posts
    45
    At least one eecorglvl1 value is NULL. This is the correct behaviour: remember that NULL means "unknown" not "empty."

    Hans.

  3. #3
    Join Date
    Mar 2004
    Posts
    12
    DOH!

    My last application avoided true NULL like the plague, so I'm lacking some experience there.

    Thanks!
    Howard Nugent
    Sr. Business Application Analyst

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •