Hello? Left Join?

Am I missing something? I thought LEFT JOIN made the first table return a value even if there was nothing in the second table. Yet this query:

SELECT b.name, isnull(c.call_no, 0)

FROM business b

LEFT JOIN call c ON b.business_id=c.service_business_id

WHERE b.business_id = 1000634

AND c.create_time BETWEEN @.startDate AND @.endDate

Is not returning any rows at all because there are no calls during the time period I'm using.

The query works if I extend the date range to include at least one call, so I know the business id and everything is correct...

Am I losing my mind? Or have I completely misunderstood what LEFT JOIN is supposed to do?

Hi Telos

You need to re-arrange your query slightly, see below.

Chris

SELECT b.name, isnull(c.call_no, 0)

FROM business b

LEFT JOIN call c ON b.business_id=c.service_business_id AND c.create_time BETWEEN @.startDate AND @.endDate

WHERE b.business_id = 1000634

|||

Ok, that works.

I don't understand why though... can I get an explanation?

|||

The reason it didn't work before is that in case of OUTER JOINS the WHERE clause is applied after the ON clause. If you have rows that doesn't satisfy the condition in the ON clause the column values will be NULL and the BETWEEN check will fail (or evaluate to unknown and hence the row(s) will not qualify).

But the way to write the query is to use a simple sub-query in the SELECT list. There is really no reason to use outer join. You need to use outer join construct if you are retrieving more columns from the outer joined table for example. Otherwise, a sub-select is the way to go.

SELECT b.name

, coalesce((select c.call_no

from call c

where b.business_id=c.service_business_id

and c.create_time BETWEEN @.startDate AND @.endDate), 0) as call_no

FROM business b

WHERE b.business_id = 1000634

|||

Ok, that makes sense.

The real query is a bit more complex though, so I was trying to avoid the subselect. The one Chris posted is working well.

Thanks for your help!

|||

The other responses are great. I just thought I would add that in your original query you could have put

AND ((c.create_time BETWEEN @.startDate AND @.endDate)

OR (c.create_time IS NULL)

)

That way your WHERE clause accommodates the fact that the OUTER JOIN may not return a value for c.create_time.

|||

The subtle difference between DanR1's approach and the others is that if the value stored in the table in the c.create_time column is NULL then the row will qualify and, therefore, will be returned. However if the c.create_time column is not nullable then this query will behave in the same way as the others.

Chris

|||

Chris,

Thanks for the elaboration.

Dan