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_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.