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:
To perform a simple join using the WHERE statement between two tables, namely the "Publishers Table" and the "Titles Table," you can follow these steps. The key here is to use the common column between these tables, which in this case is `pub_id`, to link them together. First, let’s define the structure of your tables for clarity:
  1. Publishers Table:
    • `pub_id`
    • `pub_name`
    • `city`
    • `state`
    • `country`
  2. Titles Table:
    • `title_id`
    • `pub_id`
    • `price`
    • `pubdate`

The goal is to join these tables using the `pub_id` column that exists in both. Here is how you can achieve this using a SQL query:
SELECT Publishers.pub_id, Publishers.pub_name, Titles.title_id, Titles.price, Titles.pubdate
FROM Publishers, Titles
WHERE Publishers.pub_id = Titles.pub_id;

In this query:
  • The `SELECT` statement specifies the columns you want to retrieve from the join operation. In this example, columns from both tables are selected, including `pub_id`, `pub_name` from the Publishers Table, and `title_id`, `price`, `pubdate` from the Titles Table.
  • The `FROM` clause lists the tables involved in the join. Here, both the Publishers and Titles tables are specified.
  • The `WHERE` clause is crucial for the join. It specifies the condition for the join, which in this case is `Publishers.pub_id = Titles.pub_id`. This condition ensures that rows are combined from both tables where the `pub_id` matches in both.

This approach, often referred to as an implicit join, is a traditional method of joining tables. It's important to note that while this method is still valid, the explicit JOIN syntax (using `INNER JOIN`, `LEFT JOIN`, etc.) is generally preferred in modern SQL for clarity and readability, especially in more complex queries. However, in simple cases like this, using the WHERE clause for joining tables remains a perfectly acceptable practice.


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.