Additional Criteria in the JOIN Clause

By Mark Caldwell on 16 September 2002 | Tags: JOINs

Have you ever seen a SELECT statement joining two tables that had something like and Field = Value in the JOIN clause? Did you, like me, ask yourself, "Why did they put that in the JOIN instead of in the WHERE clause?" What difference does it make? Well recently I found out that I needed this functionality for a project at work. Here’s an explanation with samples to show the differences.

SQL Server users around the world will quickly recognize the basic SELECT statement in the form of:

SELECT title_id, title
FROM titles
ORDER BY title

which results in a list of all titles, sorted alphabetically. If you want to get some additional information, such as sales quantities, you can join the Sales table like this:

SELECT titles.title_id, titles.title, sales.qty
FROM titles 
	JOIN sales on 
		titles.title_id = sales.title_id
ORDER BY title

The JOIN syntax used above is called an INNER JOIN and its results only show rows where there is a match between the two tables referenced. It leaves out rows in either table where there is no match. For example, the book titled Net Etiquette with ID PC9999 does not appear in the results of the above SELECT because it has no matching record in the Sales table. If you add the word LEFT in front of the word JOIN above, you will create an OUTER JOIN, specifically a LEFT OUTER JOIN, and get a result of all titles regardless of whether they had sales, or not. For titles that had no sales, you will get the value NULL in the qty column which comes from the Sales table. For more information on different JOIN types (Inner, Outer, Full, Cross, etc.) read the topic Using Joins in Books Online, or other related articles here on SQLTeam.

Now we can get into some more complex queries. What if you need a list of all books, with the quantity sold at the “Doc-U-Mat: Quality Laundry and Books” store? To keep the example simple, we’ll leave the Stores table out of this other than to just look up the store number for Doc-U-Mat: Quality Laundry and Books, and find that it is 7131. You know from the brief discussion above that we’ll need to use a LEFT join to get a list of all books, so your first effort might result in this query:

SELECT T.title_id, T.title, S.qty
FROM titles T
LEFT JOIN sales S on
	T.title_id = S.title_id
WHERE S.stor_id = '7131'
ORDER BY T.title

A couple of notes about this statement: First, notice that I have aliased the tables (as T and S) to make the syntax easier to type. Second, notice that the stor_id is a char column type so we put single quotes around the value. But third, and most important, notice that the results of this query do not meet our requirements because they do not show ALL books, as was requested, even though we’re using a LEFT JOIN. Why is that?

In fact, it only shows the six titles that had sales at store number 7131. It looks like the result we would expect from an INNER JOIN, and in effect that is what we have done. The WHERE clause acts on the results after the JOIN has been completed. And for every row where there is no matching record in the joined table, SQL Server shows the value as NULL. So, when the WHERE S.stor_id = ‘7131’ is processed, all of those rows with NULL get removed.

If we move the filtering criteria to the JOIN clause instead of the WHERE clause like this:

SELECT T.title_id, T.title, S.qty
FROM titles T
LEFT JOIN sales S on
	T.title_id = S.title_id
	AND S.stor_id = '7131'
ORDER BY T.title

then we get the results we were looking for, every title listed and the quantity of sales, if any, from store number 7131. For those who like things a little neater, I’ll leave it up to you to investigate how you would get those NULL qty values to display as zero instead, in case you wanted to do some totaling. One little built-in function is all it takes.

Note that there is no difference in the result sets if we use an inner join instead of an outer join, because the inner join removes the non-matching records regardless.