WHERE vs. ON in SQL JOINs: When Should I Use Each Clause?

Exploring the Differences between WHERE Clause and ON Condition in JOIN

SQL practitioners often use the WHERE clause and ON condition interchangeably when performing JOIN operations. While both approaches can return the same results, there are nuances to consider.

In the provided example, two SQL queries are presented:

SELECT *
FROM Foo AS f
INNER JOIN Bar AS b ON b.BarId = f.BarId
WHERE b.IsApproved = 1;

and

SELECT *
FROM Foo AS f
INNER JOIN Bar AS b ON (b.IsApproved = 1) AND (b.BarId = f.BarId);

Both queries return the same set of rows, but there is a crucial difference in their execution. When the filter is placed in the WHERE clause, it is applied after the JOIN operation. This means that the JOIN will retrieve all matching rows from both tables, even those where b.IsApproved is false. The WHERE clause then filters out the rows that do not meet the criterion b.IsApproved = 1.

In contrast, when the filter is placed in the ON condition, the condition is evaluated before the JOIN. As a result, only rows where both b.BarId and b.IsApproved meet the specified criteria will be included in the JOIN.

Outer Joins and Filter Placement

However, this difference becomes particularly important when dealing with outer joins. Consider the following left outer join query:

SELECT *
FROM Foo AS f
LEFT OUTER JOIN Bar AS b ON (b.IsApproved = 1) AND (b.BarId = f.BarId);

In this case, the filter in the ON condition will exclude rows where b.IsApproved is false or those where there is no matching b.BarId for a given f.BarId. This behavior is different from placing the filter in the WHERE clause:

SELECT *
FROM Foo AS f
LEFT OUTER JOIN Bar AS b ON (b.BarId = f.BarId)
WHERE b.IsApproved = 1;

In the second query, rows where b.IsApproved is false or those involving a failed join will still be returned, but b.IsApproved will be set to NULL.

Therefore, when using outer joins, it is essential to carefully consider the placement of filters. The ON condition filters rows before the join, while the WHERE clause filters the results after the join.

To complete the discussion, if you desire an OPTIONAL filter in a left outer join, the following query demonstrates the correct placement:

SELECT *
FROM Foo AS f
LEFT OUTER JOIN Bar AS b ON (b.BarId = f.BarId)
WHERE b.IsApproved IS NULL OR b.IsApproved = 1;

This query returns rows where the join succeeds and b.IsApproved meets the criterion, as well as rows where the join fails (resulting in a NULL value for b.IsApproved).

The above is the detailed content of WHERE vs. ON in SQL JOINs: When Should I Use Each Clause?. For more information, please follow other related articles on the PHP Chinese website!