| Lesson 2 | Examine tables from the PUBS database |
| Objective | See how two tables are related by examining the PUBS database. |
Join using Where Clause
When you combine results from two or more tables into a single results set, you join the information. Joins are on the strange side. Whereas other SQL items are called out specifically with a statement or keyword, you don't use the keyword JOIN to bring the results together.
Instead, you use the WHERE clause. Consider the following tables, located in the PUBS database provided with your database engine or as
part of this course:
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 |
You can see just by looking at the tables that the books in the titles table (that are shown here) are published by Algodata Infosystems. How can you see this? By matching pub_id from the titles table with the corresponding pub_id of the publishers table.
Qualifying Column Names
Recall from Tables, Columns, and Rows in a previous module, that column names must be unique
within a table but can be reused in other tables. The tables authors and publishers in the sample database both contain a column
named city, for example. To identify an otherwise-ambiguous column uniquely in a query that involves multiple tables, use its qualified name. A qualified name is a table name followed by a dot and the name of the column in the table. Because tables must have different names within a database, a qualified name identifies a single column uniquely within the entire database.
To qualify a column name:
Type: (table.column) column is a column name, and table is name of the table that contains column
- You can mix qualified and unqualified names within the same statement.
- Qualified names are not required if there's no chance of ambiguity, that is, if the query's tables have no column names in common.
To improve system performance, however, qualify all columns in a query with joins.
- Another good reason to use qualified names is to ensure that changes to a table’s structure do nott introduce ambiguities.
If someone adds the column zip to the table publishers, any unqualified references to zip in a query that selects from the tables authors (which already contains a column zip) and publishers would be ambiguous
Listing 7.2 Here, the qualified names resolve otherwise-ambiguous references to the column
city in the tables authors and publishers. See the SQL below.
SELECT au_id, authors.city
FROM authors
INNER JOIN publishers
ON authors.city = publishers.city;
au_id city
----- -------------
A03 San Francisco
A04 San Francisco
A05 New York
