Sometimes putting an additional filter, even on a constant, in your join clause is the only way to go. Consider the following:

Your boss asks for a data extract of all sales people who have sales in a certain month.

So you whip up this quick query (all using AdventureWorks)

Select SP.SalesPersonID, Sum(SubTotal)

From

Sales.SalesPerson SP

INNER JOIN Sales.SalesOrderHeader SOH ON SP.SalesPersonID = SOH.SalesPersonID

where SOH.OrderDate Between '1/1/2003' and '1/31/2003' OR SOH.OrderDate is null

Group By SP.SalesPersonID

Your boss comes back to you complaining that she doesn't see all the sales people. You think to yourself "I learned this one in SQL class. I'll just LEFT JOIN, and my problems go away."

So you come back with

Select SP.SalesPersonID, Sum(SubTotal)

From

Sales.SalesPerson SP

LEFT JOIN Sales.SalesOrderHeader SOH ON SP.SalesPersonID = SOH.SalesPersonID

where SOH.OrderDate Between '1/1/2003' and '1/31/2003'

Group By SP.SalesPersonID

What the heck? Still only 12 Sales people. I should be getting 17.

Now you smack your hand on your forehead, and think that your where clause is filtering out NULLs, so you try

Select SP.SalesPersonID, Sum(SubTotal)

From

Sales.SalesPerson SP

LEFT JOIN Sales.SalesOrderHeader SOH ON SP.SalesPersonID = SOH.SalesPersonID

where SOH.OrderDate Between '1/1/2003' and '1/31/2003' OR OrderDate IS NULL

Group By SP.SalesPersonID

Still no luck. Your filter condition on the table being joined to is causing the sales people in the source table to be filtered out altogether, so you finally come up with

Select SP.SalesPersonID, Sum(SubTotal)

From

Sales.SalesPerson SP

LEFT JOIN Sales.SalesOrderHeader SOH ON SP.SalesPersonID = SOH.SalesPersonID

and SOH.OrderDate Between '1/1/2003' and '1/31/2003'

Group By SP.SalesPersonID

Ahhh...That's the ticket!

This can work anytime you are trying to filter on a table being joined to.

If your boss had asked only for Open Orders, or anything that required some sort of filter, but you still need to get all the records so you can at least report a 0 for those sales people that had no records, this technique can be quite useful.

Hope this helps.