SQL Server Performance

Urgent! Why OUTER JOIN doesn't list all the rows?

Discussion in 'T-SQL Performance Tuning for Developers' started by zxmssp, Nov 13, 2003.

  1. zxmssp New Member

    Hi, all:

    I issued a query on two tables:

    SELECT p.pcol1, ISNULL(COUNT(b.bcol1), 0)
    FROM p LEFT OUTER JOIN b ON p.pcol2 = b.bcol2
    WHERE b.col3 >= '02/02/03' AND b.col3 <= '11/12/03'
    GROUP BY p.pcol1

    I expect all the rows of pcol1 be shown on the left and if there in on match in table b, shows 0:

    name1 10
    name2 5
    name3 0
    name4 0
    name5 15

    But instead, it only shows rows that has match in table b:

    name1 10
    name2 5
    name5 15

    Any reason why?

  2. Luis Martin Moderator

    Date configuration in Server match your where clause? (In your example I can't know if format is dd/mm/aa or mm/dd/aa)


    Luis Martin

    ...Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
    Bertrand Russell
  3. Twan New Member



    SELECT p.pcol1, ISNULL(COUNT(b.bcol1), 0)
    FROM p LEFT OUTER JOIN b ON p.pcol2 = b.bcol2
    and b.col3 >= '02/02/03' AND b.col3 <= '11/12/03'
    GROUP BY p.pcol1

    the where clause referencing b will turn it into an inner join

    Cheers
    Twan
  4. zxmssp New Member

    Then how to change it so that the data returned satisfy the original purpose?

    Thanks

    John
  5. fhanlon New Member

    Must be your WHERE clause. It works for me. Check the dates.
  6. Twan New Member

    Hi John,

    are you saying that changing the where to an and still causes the wrong rows to be shown?

    Cheers
    Twan
  7. richmondata New Member

    Your original query:

    SELECT p.pcol1, ISNULL(COUNT(b.bcol1), 0)
    FROM p LEFT OUTER JOIN b ON p.pcol2 = b.bcol2
    WHERE b.col3 >= '02/02/03' AND b.col3 <= '11/12/03'
    GROUP BY p.pcol1

    Twan is correct about the problem you're having. Here's what is happening:
    - the values for b.col3 are NULL for the non-matching columns.
    - NULLs are not like zeros or blanks. They are essentially "I don't know". In your case, the WHERE clause would eliminate the rows without matches in the inner table.


    If you want to include the unmatched rows, you could change your query to something like:

    SELECT p.pcol1, ISNULL(COUNT(b.bcol1), 0)
    FROM p LEFT OUTER JOIN b ON p.pcol2 = b.bcol2
    WHERE isnull(b.col3,'02/02/2003') >= '02/02/2003'
    AND isnull(b.col3,'02/02/2003') <= '11/12/2003'
    GROUP BY p.pcol1


    In general, LEFT JOINs with restriction clauses on the inner table can be tricky. Most of the time, you will want to include the restriction clauses in the LEFT JOIN's join criteria so the inner table consists only of rows matching those criteria (as Twan suggested). In some cases, though, you may want the criteria in the WHERE clause in which case you will need to use ISNULL around the columns from the inner table.

Share This Page