Lesson 7 | An example of a LEFT join |
Objective | Understand how LEFT and RIGHT joins work in a real example |
LEFT | RIGHT joins (SQL Operations)
If you use the tables we set up in the earlier examples, you can create a join of the first table (Publishers) and the second (Titles) that will
show this point.
Publishers Table
pub_id | pub_name | city | state | country |
0736 | New Moon Books | Boston | MA | USA |
0877 | Binnet & Hardley | Washington | DC | USA |
1389 | Algodata Infosystems | Berkeley | CA | USA |
1622 | Five Lakes Publishing | Chicago | IL | USA |
1756 | Ramona Publishers | Dallas | TX | USA |
9901 | GGG&G | München | | Germany |
9952 | Scootney Books | New York | NY | USA |
9999 | Lucerne Publishing | Paris | | France |
Titles Table (a portion of the table is shown below)
title_id | pub_id | price | pubdate |
BU1032 | 1389 | 19.99 | Jun 12 1991 12:00AM |
BU1111 | 1389 | 11.95 | Jun 9 1991 12:00AM |
The statement that you could use is
SELECT pub_ID, pub_name, title_id
FROM publishers LEFT OUTER JOIN Titles
ON publishers.pub_ID = titles.pub_ID
The result would be the following:
pub_id | pub_name | title_ID |
0736 | New Moon Books | |
0877 | Binnet & Hardley | |
1389 | Algodata Infosystems | BU1032 |
1389 | Algodata Infosystems | BU1111 |
1389 | Algodata Infosystems | |
1622 | Five Lakes Publishing | |
1756 | Ramona Publishers | |
9901 | GGG&G | |
9952 | Scootney Books | |
9999 | Lucerne Publishing | |
You can see that all the rows from the publishers table are included, and one row for each matching row in the titles table is created. In this case, there are only two books in the titles table, so all title IDs are blank except for those two.
Right joins work in the same way.