Additional Criteria in the JOIN Clause

Additional Criteria in the JOIN Clause
AjarnMark on 9/16/2002 in 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.
Note: All examples here use the Pubs database that comes with SQL Server.
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.

Chiếc hộp tình yêu

Chiếc hộp tình yêu

Có một người cha nghèo đã quở phạt đứa con gái 3 tuổi của mình vì tội lãng phí cả một cuộn giấy gói quà mầu vàng. Tiền bạc eo hẹp, người cha nổi giận khi đứa bé cắt cuộn giấy quý ra thành từng mảnh nhỏ trang trí một cái hộp giấy. Sáng sớm hôm sau, đứa con gái nhỏ vẫn mang hộp quà đến nói với cha: "Con tặng bố!". Người cha cảm thấy bối rối vì cơn giận dữ của mình tối hôm trước nhưng rồi cơn giận dữ lại bùng lên khi ông mở ra, thấy cái hộp trống rỗng. Ông mắng con gái. Đứa con gái nhỏ ngước nhìn cha, nước mắt rưng rưng, thưa: "Bố ơi, đó đâu phải là cái hộp rỗng, con đã thổi đầy những nụ hôn vào hộp để tặng bố mà!". Người cha giật mình. Ông vòng tay ôm lấy đứa con gái nhỏ cầu xin con tha thứ. Đứa con gái nhỏ, sau đấy không bao lâu, qua đời trong một tai nạn. Nhiều năm sau, người cha vẫn khư khư giữ cái hộp giấy bên mình, mỗi khi gặp chuyện nản lòng, ông lấy ra một nụ hôn tưởng tượng và nghĩ đến tình yêu mà đứa con gái bé bỏng của ông đã thổi vào chiếc hộp. Trong cuộc sống, chúng ta đã và sẽ nhận được những chiếc hộp quý giá chứa đầy tình yêu và những nụ hôn vô tư từ con cái của chúng ta, từ bạn bè, gia đình. Trên đời này, chúng ta không thể có được tài sản nào quý giá hơn những chiếc hộp chứa đầy tình yêu vô tư như thế.

A lovely box

There is a poor father who punished his 3 years old daughter due to wasted entire roll of yellow paper . On a tight budget , The father was very angry when the little girl had cut the roll of that precious paper into many pieces to make up a paper box . The next early morning , she still brought the gift box to him and said : "I give you!" .
The father felt confused because of his angriness last night but the angriness became violent when he had openned it , having nothing inside .
He scolded her . The little girl raised her eyes to him , having tears welling up , said "Dad , It's not an empty box , I blew full of my kisses into the box to give you".
The father was very astonished . He huged her and prayed to be forgiven .
The little girl , in a short time later , was killed in an accident . Many years later , the father still clutched the box with him . Each time had discouraged , he got out an imaginary kiss and thought of love that his little girl had blown into the box .
In life , we have received precious boxes with full of love and fair-minded kisses from our children , friends , and family . In the world , We can't have property which is more precious than the boxes with full of fair-minded love like that .
Translated by DungLH.