Lesson 3 | Examine Simple join |
Objective | Examine 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_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 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.