Is it a Good Practice to Add two Conditions when using a JOIN keyword?

Question

I'd like to know if having to conditionals when using a JOIN keyword is a good practice.

I'm trying to filter this resultset by date but I'm unable to get all the branches listed even if there's no expense or income for a date using a WHERE clause. Is there a better way of doing this, if so how?

SELECT
  Branches.Name
  ,SUM(Expenses.Amount) AS Expenses
  ,SUM(Incomes.Amount) AS Incomes
FROM
  Branches
  LEFT JOIN Expenses
    ON Branches.Id = Expenses.BranchId AND Expenses.Date = '3/11/2010'
  LEFT JOIN Incomes
    ON Branches.Id = Incomes.BranchId AND Incomes.Date = '3/11/2010'
GROUP BY Branches.Name

Answer 1

That is the correct way to condition an outer join.

You should not have any performance problems, as long as your query can use indexes for both the BranchId and Date fields of the Expenses and Incomes tables.

Answer 2

Why not? Even more! OUTER JOIN has very specific trick about these two conditions! INNER JOIN is tolerant to recombination, for example following are equivalent:

INNER JOIN Expenses
    ON Branches.Id = Expenses.BranchId
WHERE
    Expenses.Date = '3/11/2010'

via:

INNER JOIN Expenses
    ON Branches.Id = Expenses.BranchId AND Expenses.Date = '3/11/2010'

BUT!!! For OUTER JOIN you MUST specify exactly two conditions inside ON, since WHERE would treat result as INNER JOIN

Answer 3

You should separate the join logic and the filtering in my opinion.

SELECT
  Branches.Name
  ,SUM(Expenses.Amount) AS Expenses
  ,SUM(Incomes.Amount) AS Incomes
FROM
  Branches
  LEFT JOIN Expenses
    ON Branches.Id = Expenses.BranchId
  LEFT JOIN Incomes
    ON Branches.Id = Incomes.BranchId
WHERE ISNULL(Expenses.Date,'3/11/2010') = '3/11/2010'
AND  ISNULL(Incomes.Date,'3/11/2010') = '3/11/2010'
GROUP BY Branches.Name

Answer 4

You are doing it correctly. In your case, you are expecting to filter the right side of the join, so the date condition belongs in the join. It says "give me all branches, and only those income & expenses that match my condition."

When you move your condition to the WHERE clause, you are changing the entire meaning of the query to be "give me all branches and their income & expenses, but only where everything matches my condition."

This is not a matter of good practice, but is by design as to how your database will evaluate your queries.

Answer 5

This is the best way to do this when using outer joins. Moving the additional condition to the where clause will change the join to an inner join as it has to meet the where condition in all the records.

Answer 6

Your above query is correct. Here is how I keep it straight:

• When a criteria is in your WHERE clause, it's a criteria that has to be satisfied in the result set, meaning that NULL rows (in your LEFT JOIN, for example) won't match, and will be excluded.
• Where a criteria is in your JOIN clause (either LEFT or RIGHT), it's criteria that has to be satisfied in the join, but not in the results. If a row exists, it must satisfy the criteria, but SQL will allow an OUTER JOIN to count as a match, even though there's no value on the other side to actually evaluate.

I'm not sure if that makes more sense, but in your case of wanting to return the row even if there are no matches, your criteria must be in the JOIN (as you've done it above).