Outer joins and search predicates – II

In my last post, we saw that if we place a search predicate in WHERE clause instead of placing it along with the ON clause, we may get the expected output. Now let’s understand what happens behind the scene.

Note: we’ll use the pubs database for the example.

Suppose we want to list all the book titles; then we might write the following query.

SELECT T.title_id as ID, T.Title 
FROM titles T 
ORDER BY T.Title 

Now we are asked to list down sales quantity for any given store for all the titles. If the given title is not sold, the quantity should be zero. For that, we might write the following query.

SELECT T.title_id, T.title, IsNull(S.qty,0) Qty 
FROM titles T 
LEFT JOIN sales S on T.title_id = S.title_id 
WHERE S.stor_id = '6380' 
ORDER BY T.title

If you run the above query, you’ll get only two rows that were sold instead of the remaining titles with zero quantity. Now if you see the execution plan, you can see that LEFT JOIN is converted to INNER JOIN by the execution engine. Check out the below snap.

Now if you need to get all the rows with quantity zero if the title is not sold, place your search predicate along with the ON clause in LEFT JOIN, and you’ll get the expected output. So our expected query should be like the below :

SELECT T.title_id, T.title, IsNull(S.qty,0) Qty 
FROM titles T 
LEFT JOIN sales S on T.title_id = S.title_id 
and S.stor_id = '6380' 
ORDER BY T.title 

Always be careful when using search conditions with OUTER JOIN and construct your queries as per the output expected. Enjoy!! 🙂