15 Joining Tables

15.1 Understanding Joins

One of SQL's most powerful features is the capability to join tables on-the-fly within data retrieval queries. Joins are one of the most important operations that you can perform using SQL SELECT, and a good understanding of joins and join syntax is an extremely important part of learning SQL.

Before you can effectively use joins, you must understand relational tables and the basics of relational database design. What follows is by no means complete coverage of the subject, but it should be enough to get you up and running.

15.2 Understanding Relational Tables

The best way to understand relational tables is to look at a real-world example, one based on the museum database you've used in the lessons thus far.

Suppose you had a database table containing a list of works of art, with each work in its own row. The kind of information you would store with each work would include a name and style, along with information about the artist who created the work.

Now suppose that you had multiple works created by the same artist. Where would you store the artist information (things like the artist's name, nationality, and birth and death years)? You wouldn't want to store that data along with the works for several reasons:

• Because the artist information is the same for each work that artist created, repeating the information for each work is a waste of time and storage space.
• If artist information changes (for example, if an error in the artist's birth year is corrected), you would need to update every occurrence of the artist information.
• When data is repeated (that is, the artist information is used with each work), there is a high likelihood that the data will not be entered identically each time. Inconsistent data is extremely difficult to use in reporting.

The key here is that having multiple occurrences of the same data is never a good thing, and that principle is the basis for relational database design. Relational tables are designed so that information is split into multiple tables, one for each data type. The tables are related to each other through common values (and thus the relational in relational design).

In our example, you can create two tables — one for artist information and one for work information. The artist table contains all the artist information, one table row per artist, along with a unique identifier for each artist. This value, called a primary key, is the artist_id.

The work table stores only work information and no artist-specific information other than the artist_id (the artist table's primary key). This key relates the artist table to the work table, and using this artist_id enables you to use the artist table to find the details about the appropriate artist.

What does this do for you? Well, consider the following:

• Artist information is never repeated, and so time and space are not wasted.
• If artist information changes, you can update a single record, the one in the artist table. Data in related tables does not change.
• Because no data is repeated, the data used is obviously consistent, making data reporting and manipulation much simpler.

The bottom line is that relational data can be stored efficiently and manipulated easily. Because of this, relational databases scale far better than nonrelational databases.

15.3 Why Use Joins?

As just explained, breaking data into multiple tables enables more efficient storage, easier manipulation, and greater scalability. But these benefits come with a price.

If data is stored in multiple tables, how can you retrieve that data with a single SELECT statement?

The answer is to use a join. Simply put, a join is a mechanism used to associate, or join, tables within a SELECT statement (and thus the name join). By using a special syntax, you can join multiple tables so that a single set of output is returned, and the join associates the correct rows in each table on the fly.

15.4 Creating a Join

Creating a join is very simple. You must specify all the tables to be included and how they are related to each other. Suppose you want to list the full name of each artist along with the name and style of each of their works. You can use a chat with an AI assistant to create the SELECT statement that will do this:

Try the SQL statement.

SELECT full_name, name, style
FROM artist, work
WHERE artist.artist_id = work.artist_id;

Let's take a look at the preceding code. The SELECT statement starts in the same way as all the statements you've looked at thus far, by specifying the columns to be retrieved. The big difference here is that one of the specified columns (full_name) is in one table, whereas the other two (name and style) are in another table.

Now look at the FROM clause. Unlike all the prior SELECT statements, this one has two tables listed in the FROM clause, artist and work. These are the names of the two tables that are being joined in this SELECT statement. The tables are correctly joined with a WHERE clause that instructs the DBMS to match artist_id in the artist table with artist_id in the work table.

You'll notice that the columns are specified as artist.artist_id and work.artist_id. This fully qualified column name is required here because if you just specified artist_id, the DBMS cannot tell which artist_id column you are referring to. (There are two of them, one in each table.) As you can see, a single SELECT statement returns data from two different tables.

15.5 The Importance of the WHERE Clause

It might seem strange to use a WHERE clause to set the join relationship, but actually, there is a very good reason for this. Remember, when tables are joined in a SELECT statement, that relationship is constructed on the fly. There is nothing in the database table definitions that can instruct the DBMS how to join the tables. You have to do that yourself. When you join two tables, what you are actually doing is pairing every row in the first table with every row in the second table. The WHERE clause acts as a filter to only include rows that match the specified filter condition — the join condition, in this case. Without the WHERE clause, every row in the first table will be paired with every row in the second table, regardless of whether they logically go together or not.

To understand this, look at the following SELECT statement:

SELECT full_name, name, style
FROM artist, work;

If the artist table has 421 rows and the work table has several thousand rows, the Cartesian product is seldom what you want. The data returned here has matched every work with every artist, including works with the incorrect artist (and even artists with no works at all recorded in this result).

15.6 Inner Joins

The join you have been using so far is called an equijoin — a join based on the testing of equality between two tables. This kind of join is also called an inner join. In fact, you may use a slightly different syntax for these joins, specifying the type of join explicitly. The following SELECT statement returns the exact same data as an earlier example:

SELECT full_name, name, style
FROM artist
INNER JOIN work ON artist.artist_id = work.artist_id;

The SELECT in the statement is the same as the preceding SELECT statement, but the FROM clause is different. Here the relationship between the two tables is part of the FROM clause specified as INNER JOIN. In this syntax, the join condition is specified using the special ON clause instead of a WHERE clause. The actual condition passed to ON is the same as would be passed to WHERE.

Refer to your DBMS documentation to see which syntax is preferred.

15.7 Joining Multiple Tables

SQL imposes no limit to the number of tables that may be joined in a SELECT statement. The basic rules for creating the join remain the same. First, list all the tables, and then define the relationship between each. Suppose you want to list the subject, artist name, work title, and style for the work with work_id 210. You can use a chat with an AI assistant to create the SELECT statement that will do this:

Try the SQL statement.

SELECT subject.subject, full_name, name, style
FROM subject, work, artist
WHERE work.artist_id = artist.artist_id
AND subject.work_id = work.work_id
AND subject.work_id = 210;

This example displays every subject recorded for the work with work_id 210, along with the artist's name, the work's title, and its style. Each subject is stored by its work_id, which refers to a work in the work table. The works are linked to the appropriate artist in the artist table by the artist_id, which is stored with each work record. The FROM clause here lists the three tables, and the WHERE clause defines both of those join conditions. An additional WHERE condition is then used to filter just the rows for work_id 210.

Now would be a good time to revisit the following example from the previous lesson, "Working with Subqueries." As you will recall, this SELECT statement returns a list of artists who created a work with the subject "Portrait":

SELECT full_name, nationality
FROM artist
WHERE artist_id IN (SELECT artist_id
                    FROM work
                    WHERE work_id IN (SELECT work_id
                                      FROM subject
                                      WHERE subject = 'Portrait'));

As mentioned in that lesson, subqueries are not always the most efficient way to perform complex SELECT operations, and so as promised, here is the same query using joins:

Try the SQL statement.

SELECT full_name, nationality
FROM artist, work, subject
WHERE artist.artist_id = work.artist_id
AND subject.work_id = work.work_id
AND subject.subject = 'Portrait';

As explained in the previous lesson, returning the data needed in this query requires the use of three tables. But instead of using them within nested subqueries, here two joins are used to connect the tables. There are three WHERE clause conditions here. The first two connect the tables in the join, and the last one filters the data for the subject "Portrait."

15.8 Summary

Joins are one of the most important and powerful features in SQL, and using them effectively requires a basic understanding of relational database design. In this lesson, you learned some of the basics of relational database design as an introduction to learning about joins. You also learned how to create an equijoin (also known as an inner join), which is the most commonly used form of join. In a later lesson, you'll learn how to create other types of joins.