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:
This generates the following table of results:
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.
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 |
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:
Post a Comment