SQL Joins   «Prev  Next»

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_idpub_namecitystatecountry
0736New Moon BooksBostonMAUSA
0877Binnet & HardleyWashingtonDCUSA
1389Algodata InfosystemsBerkeleyCAUSA
1622Five Lakes PublishingChicagoILUSA
1756Ramona PublishersDallasTXUSA
9901GGG&GMünchen Germany
9952Scootney BooksNew YorkNYUSA
9999Lucerne PublishingParis France
Titles Table (a portion of the table is shown below)
title_idpub_idpricepubdate
BU1032138919.99Jun 12 1991 12:00AM
BU1111138911.95Jun 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_idpub_nametitle_ID
0736New Moon Books 
0877Binnet & Hardley 
1389Algodata InfosystemsBU1032
1389Algodata InfosystemsBU1111
1389Algodata Infosystems 
1622Five Lakes Publishing 
1756Ramona Publishers 
9901GGG&G 
9952Scootney Books 
9999Lucerne 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.