SQL: RIGHT JOIN

Shafeen and Faisal

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:

FirstName	LastName	ProductName
Jim	Jones	VB6
Jim	Jones	MySQL
John	Smith	NULL
Mary	Jones	MS Word
Mary	Jones	OpenOffice
Mary	Jones	MySQL
Chloe	Feltham	NULL
Bharat	Patel	VB6
Terry	Jones	VB6
Terry	Jones	PaintShop 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;