LEFT JOINs in SQL Server: ON vs. WHERE Clause – What's the Difference and When Should I Use Each?

LEFT JOIN with Condition: On vs. WHERE

In SQL Server, LEFT JOINs are often used to retrieve data from one table while linking it to another table through a common column. However, a subtle distinction arises when using conditions in the ON and WHERE clauses, leading to different results.

JOIN ON Clause vs. WHERE Clause

The ON clause in a LEFT JOIN specifies the condition that determines which rows from the primary table (joined table) will be matched with rows from the secondary table (joining table). In contrast, the WHERE clause filters the result set after the join has been performed.

Example

Consider the following two queries:

-- LEFT JOIN with Condition in ON Clause
SELECT t1.f2
FROM t1
LEFT JOIN t2
ON t1.f1 = t2.f1 AND cond2 AND t2.f3 > something;

-- LEFT JOIN with Condition in WHERE Clause
SELECT t1.f2
FROM t1
LEFT JOIN t2
ON t1.f1 = t2.f1 AND cond2
WHERE t2.f3 > something;

Results

Surprisingly, these two queries do not produce the same results. The reason lies in the difference between the JOIN and WHERE clauses.

In the first query, the condition in the ON clause restricts which rows from t2 are joined with t1. Therefore, rows from t2 that do not meet the condition (t2.f3 <= something) will be excluded from the join. Consequently, the result set will not include rows from t1 that could have potentially matched those excluded rows from t2.

In the second query, the condition in the WHERE clause is applied after the join has been performed. As a result, all rows from t1 are joined with all rows from t2, regardless of whether they meet the condition. Subsequently, the WHERE clause filters out the rows that do not meet the condition, providing a different result set compared to the first query.

Conclusion

Understanding the distinction between the ON and WHERE clauses is crucial when working with LEFT JOINs. The ON clause determines the matching rows for the join, while the WHERE clause filters the result set after the join has been completed. This understanding ensures that the desired results are obtained from SQL queries involving LEFT JOINs.

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