We often use outer joins to construct queries and get the expected output, but what if you get an unexpected output and the query seems OK. A few days ago, I experienced the same thing and discovered the problem with the placement of the predicate (search condition).
Let’s take an example :
We’ll use the Orders and Customers table from the Northwind database.
Let’s check out INNER JOIN and LEFT JOIN results without any conditions first.
As you can see in the snap above, both are returning the same no of rows ( I’ve taken the top(5) for the image, as both the queries have more than 100 rows)
Now let’s check out INNER JOIN with a predicate (search condition) in WHERE and ON clauses.
Both the query returned the same result.
Now let’s check out LEFT JOIN with a WHERE clause.
It has also returned the same no of rows, but if we use the predicate (search condition) along with the ON clause rather than the WHERE clause, it will return more no of rows (unexpected output).
The explanation can be found on SQL Server Books Online, which says,
There can be predicates that involve only one of the joined tables in the ON clause. Such predicates also can be in the WHERE clause in the query. Although the placement of such predicates does not make a difference for INNER joins, they might cause a different result when OUTER joins are involved. This is because the predicates in the ON clause are applied to the table before the join, whereas the WHERE clause is semantically applied to the result of the join.
An outer join query can produce different results depending on how you write it and where predicates are placed in that query. Take Care!
Leave a Reply