SQL Server Performance

sum of is null and is not null query doesn't equal total

Discussion in 'General Developer Questions' started by boutwater, Aug 25, 2011.

  1. boutwater Member

    Hello, I run 3 queries. The first get's everything from a table, the second get's everything from that table that isn't in another table, and the third get's everything from that table that is in another table.

    Query 2 + Query 3 should equal Query 1

    Here are the queries:
    /*query 1*/
    select count(*) from location

    /*query 2*/
    select count(*) from location
    left join carton
    on location.facilityid = carton.facilityid
    and location.loctypeid = carton.loctypeid
    and location.locationid = carton.locationid
    where location.loctypeid = 'p'
    and carton.cartonid is null
    /*query 3*/
    select count(*) from location
    left join carton
    on location.facilityid = carton.facilityid
    and location.loctypeid = carton.loctypeid
    and location.locationid = carton.locationid
    where location.loctypeid = 'p'
    and carton.cartonid is not null

    my result is:
    595413
    552299
    101712
    How is it possible that there is more than the total? Thanks in advance,

    Ben
  2. FrankKalis Moderator

    In query 2 & 3 you refer in the WHERE clause to the carton table which you LEFT JOINed onto. This effectively turns the OUTER JOIN into an INNER JOIN and may well be the reason why you don't get the results you expect. Move this condition from WHERE to the JOIN.

Share This Page