LEFT JOIN in SQL: What's the Key Difference Between `ON` and `WHERE` Clauses?

LEFT JOIN with Conditions: Where's the Difference Between ON and WHERE?

In SQL Server, LEFT JOIN combines rows from two tables based on a specified join condition. While the ON clause defines the join condition within the LEFT JOIN statement, the WHERE clause filters rows after the join operation. This subtle distinction can lead to unexpected results if not properly understood.

Consider the following query:

SELECT t1.f2
FROM   t1
LEFT JOIN t2
ON t1.f1 = t2.f1 AND cond2 AND t2.f3 > something

Surprisingly, this query does not produce the same results as the following:

SELECT t1.f2
FROM   t1
LEFT JOIN t2
ON t1.f1 = t2.f1 AND cond2
WHERE  t2.f3 > something

The Explanation

The ON clause plays a crucial role in determining which rows participate in the join. The condition specified in ON must be true for rows to be matched and included in the join result. In contrast, the WHERE clause filters rows that have already been joined.

In the first query, the condition t2.f3 > something is part of the ON clause. Consequently, only rows where t2.f3 is greater than something will be included in the join. This means that any matching rows where t2.f3 is not greater than something will be excluded from the result.

In the second query, the condition t2.f3 > something is part of the WHERE clause. This clause is evaluated after the join has been performed. Hence, all rows that satisfy the ON clause condition (t1.f1 = t2.f1 and cond2) will be part of the join result. The WHERE clause then filters these rows, retaining only those where t2.f3 is greater than something.

Example

To illustrate this concept, consider a table of candidates (candidates) and a table of votes (votes). If we want to find candidates who have received votes, we can use a LEFT JOIN:

SELECT  *
FROM    candidates c
LEFT JOIN    
        votes v
ON      c.name = v.voted_for

This query returns all candidates, even those who have not received any votes. If we move the condition to the WHERE clause:

SELECT  *
FROM    candidates c
LEFT JOIN    
        votes v
ON      c.name = v.voted_for
WHERE   v.voted_for IS NOT NULL

The results now only include candidates who have received at least one vote.

Conclusion

Understanding the difference between ON and WHERE clauses in a LEFT JOIN is crucial for manipulating data effectively. By carefully placing conditions in their appropriate clauses, you can ensure that your queries return the desired results.

The above is the detailed content of LEFT JOIN in SQL: What's the Key Difference Between `ON` and `WHERE` Clauses?. For more information, please follow other related articles on the PHP Chinese website!