Monday, October 17, 2011

SQL: RIGHT JOIN

I got this info from http://www.techbookreport.com/index.html


Although the INNER JOIN is the type of join most often used, it isn't the only one. There are in fact 'outer' joins as well as an inner join. A good illustration of a useful outer join follows on from our previous examples. The table of users and software only lists those users for whom software products are recorded, but what if we want to list all of the users in our database? Clearly we would need to join the User to UserSoftware, but we would want all of the records from User listed, not just those for which there is a matching employee number. We do this using a type of outer join called a LEFT JOIN.
We can thus amend our SQL as follows:

SELECT DISTINCT User.FirstName, User.LastName, Software.ProductName
FROM (User LEFT JOIN UserSoftware ON User.EmpNo = UserSoftware.EmpNo) 
LEFT JOIN Software ON UserSoftware.ProductID = Software.ProductID;

This generates the following table of results:
FirstNameLastNameProductName
JimJonesVB6
JimJonesMySQL
JohnSmithNULL
MaryJonesMS Word
MaryJonesOpenOffice
MaryJonesMySQL
ChloeFelthamNULL
BharatPatelVB6
TerryJonesVB6
TerryJonesPaintShop Pro
Note that some systems report a missing value as NULL, others (including MS Access) would simply return a blank in the above result.
And what about those software products for whom there are no users? Again we need an outer join, only this time we want to include those records from the second table in the query, which is called a RIGHT JOIN.

SELECT DISTINCT User.FirstName, User.LastName, Software.ProductID
FROM (UserSoftware LEFT JOIN User ON UserSoftware.EmpNo = User.EmpNo) 
RIGHT JOIN Software ON UserSoftware.ProductID = Software.ProductID;

No comments: