SQL Server Performance Forum – Threads Archive

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

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?

---

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

---

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

---

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

---

Must be your WHERE clause. It works for me. Check the dates.

---

Hi John, are you saying that changing the where to an and still causes the wrong rows to be shown? Cheers
Twan

---

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.

---

]]>