WHERE vs. ON in JOIN Clauses: When Does Filter Placement Matter?

Understanding the Distinction Between WHERE and ON Clauses in JOIN Queries

Consider the following scenarios when joining two tables, Foo and Bar, based on their BarId column:

INNER JOIN with WHERE Clause

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

INNER JOIN with ON Clause

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

These two queries return the same results. However, when dealing with outer joins, a critical difference emerges. Let's explore using a LEFT OUTER JOIN:

LEFT OUTER JOIN with ON Clause Filter

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

LEFT OUTER JOIN with WHERE Clause Filter

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

In the first query, rows from the right table (Bar) are filtered based on the IsApproved column during the join process. Conversely, in the second query, this filtering is performed after the join.

This distinction becomes significant when there's a null value for b.BarId. In the first query, such rows will still be included in the results (with null values for columns from Bar). In the second query, these rows will be filtered out by the WHERE clause.

Equivalence for OPTIONAL Filters

For an OPTIONAL filter (e.g., b.IsApproved is not restricted), the equivalent LEFT OUTER JOIN with a WHERE clause would be:

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

This considers both the case where the join fails (b.BarId is null and the filter should be ignored) and the case where the join succeeds and the filter should be applied.

Conclusion

While the placement of a filter in the WHERE or ON clause may at first seem inconsequential, the subtle differences in behavior, especially for outer joins, necessitate careful consideration. This distinction ensures accurate data retrieval and efficient query optimization.

The above is the detailed content of WHERE vs. ON in JOIN Clauses: When Does Filter Placement Matter?. For more information, please follow other related articles on the PHP Chinese website!