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.

...WHERE table1.column=table2.column 

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.