Good practice to add two conditions when using the JOIN keyword?

Question

I would like to know if using conditional conditions when using the JOIN keyword is good practice.

I am trying to filter this result set by date, but I can’t get all the branches listed, even if there are no expenses or revenues for the date using the WHERE clause. Is there a better way to do 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

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

 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 an OUTER JOIN, you MUST specify exactly two conditions inside ON, since WHERE will process the result as an INNER JOIN

Answer 2

This is the correct way to condition the outer join.

You should have no performance issues if your query can use indexes for the BranchId and Date fields of the Expenses and Incomes .

Answer 3

You are doing it right. In your case, you expect to filter the right side of the connection, so the date condition belongs to the connection. It says: "Give me all the branches, and only those income and expenses that correspond to my condition."

When you move your condition to the WHERE clause, you change the whole meaning of the request to "give me all the branches and their income and expenses, but only where everything corresponds to my condition."

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

Answer 4

This is the best way to do this when using external connections. Moving an additional condition into the where clause will change the join to an inner join, since it must satisfy the where clause in all records.

Answer 5

Your previous request is correct. Here's how I keep it straight:

• If there are criteria, this criterion in your WHERE clause, which must be satisfied in the result set, which means that the NULL string (for example, on your LEFT JOIN) are not the same, and will be removed.
• If your JOIN clause contains criteria (either LEFT or RIGHT), these are criteria that must be satisfied in the join , but not in the results. If a row exists, it must satisfy the criteria, but SQL will allow OUTER JOIN to be considered a match, even if there is no value on the other side for the actual evaluation.

I'm not sure if this makes sense, but in your case, if you want to return a string, even if there are no matches, your criteria should be in JOIN (as you did above).

Answer 6

You should separate the connection logic and 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