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

Left Joins Revisited: The Distinction Between ON and WHERE Clauses

In SQL Server, the LEFT JOIN operation plays a crucial role in retrieving data from multiple tables. However, a subtle yet significant difference exists between specifying join conditions in the ON clause and the WHERE clause.

Consider the following query:

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

This query attempts to retrieve the f2 column from table t1, while optionally joining rows from table t2 based on matching values in the f1 columns and additional conditions (cond2 and t2.f3 > something).

In contrast, the following query takes a different approach:

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

Here, the join condition is specified solely in the ON clause, while the t2.f3 > something condition is added as a filter in the WHERE clause.

Understanding the Difference

The key difference between these two queries lies in the order of operations:

• ON Clause: Specifies the conditions used to identify matching rows for the join. Rows that do not meet these conditions are excluded from the joined result set.
• WHERE Clause: Filters rows from the joined result set based on additional conditions.

Example

To illustrate this distinction, consider a table of candidates (candidates) and a table of votes (votes):

candidates
| name        |
|-------------|
| Obama        |
| Romney        |

votes
| voter       | voted_for |
|-------------|-------------|
| Mickey Mouse | Romney      |
| Donald Duck  | Obama       |

Query 1:

SELECT *
FROM candidates c
LEFT JOIN votes v
ON c.name = v.voted_for AND v.voter = 'Donald Duck'

This query retrieves all candidates, including Romney, even though Donald Duck voted for Obama. This is because the v.voter = 'Donald Duck' condition is specified in the ON clause, which only affects the join operation.

Query 2:

SELECT *
FROM candidates c
LEFT JOIN votes v
ON c.name = v.voted_for
WHERE v.voter = 'Donald Duck'

In this query, the v.voter = 'Donald Duck' condition is moved to the WHERE clause. As a result, Romney is excluded from the result set because he does not have a matching vote from Donald Duck. Only Obama, who received a vote from Donald Duck, is returned.

Conclusion

Understanding the distinction between ON and WHERE clauses in LEFT JOIN operations is essential for effectively retrieving data in SQL Server. By carefully placing conditions in the appropriate clause, you can control the matching criteria and filter the joined result set efficiently.

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