Left outer join with where

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.



حول أحمد حسان
Senior IT Specialist @ German Hospital of Alexandria ,Co-Developed Medxpress ,Hospital ERP system ,Microsoft Certified Solution Developer

اترك رد

إملأ الحقول أدناه بالمعلومات المناسبة أو إضغط على إحدى الأيقونات لتسجيل الدخول:

WordPress.com Logo

أنت تعلق بإستخدام حساب WordPress.com. تسجيل خروج   / تغيير )

صورة تويتر

أنت تعلق بإستخدام حساب Twitter. تسجيل خروج   / تغيير )

Facebook photo

أنت تعلق بإستخدام حساب Facebook. تسجيل خروج   / تغيير )

Google+ photo

أنت تعلق بإستخدام حساب Google+. تسجيل خروج   / تغيير )

Connecting to %s

%d مدونون معجبون بهذه: