SQL Joins   «Prev  Next»

Lesson 3 Examine Simple join
ObjectiveExamine how to use the WHERE statement to create a simple join.

SQL Joins Explained

The join is created when two key information items are related in a SELECT statement. You relate these items with the WHERE clause, creating a link between the different tables. Although you can create a join with several tables, for the purposes of this brief introduction, we will be using only two.
For reference, here are the tables:

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 syntax of the WHERE clause is
...WHERE table1.column=table2.column 

You are referencing the two different tables and the two columns that represent the information that links the two tables. For these tables, pub_id is common and will be used to create the join.
If you want the publisher's name for the book with the title ID of BU1032, the following statement will provide it:
SELECT pub_name FROM publishers, titles 
WHERE publishers.pub_id=titles.pub_id AND 
title_id='BU1032' 

We are naming more than one table in the FROM clause. That makes sense because we're taking information from both the publishers and titles tables. Next, you can see where the relationship is called out:
publishers.pub_id=titles.pub_id. 
That defines how these two tables are tied together. In this case, pub_id is the magic link between them.