Use of not exists

  • Hi

    I need to get the data from my "live" load table that does not exist in my archive_load

    Trying:

    select count([Live_Key])

    FROM [LOAD_SCHED] as T1

    WHERE NOT EXISTS (SELECT T2.[Arch_Key] FROM [Arch_LOAD] AS T2 where T2.[Arch_Key] = T1.[Live_Key])

    Results in the exact same posts as

    select count([Live_Key])

    FROM [LOAD_SCHED] as T1

    There are 9402 records, but about 7500 allready exist in the arch table, thus I would expect only 2000 rows from my first query

    If I replace with NOT IN I get 0 rows in return

    Any comments on why?

    Br

    DJ

  • What does this return?

    select count(*)

    FROM [LOAD_SCHED] as T1 inner join [Arch_LOAD] AS T2 on T2.[Arch_Key] = T1.[Live_Key]

    select count(*) from LOAD_SCHED where Live_Key IS NULL

    select count(*) from Arch_LOAD where Arch_Key IS NULL

    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

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

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