16 Advanced Joins

16.1 Using Table Aliases

Before we look at additional types of joins, we need to revisit aliases. Back in the lesson on creating calculated fields, you learned how to use aliases to refer to retrieved table columns. The syntax to alias a column looks like this:

SELECT RTRIM(name) || ' (' || RTRIM(city) || ')' AS museum_title
FROM museum
ORDER BY name;

In addition to using aliases for column names and calculated fields, SQL also enables you to alias table names. There are two primary reasons to do this:

• To shorten the SQL syntax
• To enable multiple uses of the same table within a single SELECT statement

Take a look at the following SELECT statement. It is basically the same statement as an example used in the previous lesson, but it has been modified to use aliases:

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

You'll notice that the three tables in the FROM clause all have aliases. artist AS A establishes A as an alias for artist, and so on. This approach enables you to use the abbreviated A instead of the full text artist. In this example, the table aliases were used only in the WHERE clause, but aliases are not limited to just WHERE. You can use aliases in the SELECT list, the ORDER BY clause, and in any other part of the statement as well.

It is also worth noting that table aliases are only used during query execution. Unlike column aliases, table aliases are never returned to the client.

16.2 Using Different Join Types

Thus far you have used only simple joins known as inner joins or equijoins. You'll now take a look at three additional join types: the self join, the natural join, and the outer join.

16.3 Self Joins

As mentioned earlier, one of the primary reasons to use table aliases is to be able to refer to the same table more than once in a single SELECT statement. An example will demonstrate this.

Suppose you wanted to identify all the artists who share a nationality with Claude Monet. This query requires that you first find out what nationality Claude Monet is and next which artists share that nationality. The following is one way to approach this problem:

SELECT artist_id, full_name, nationality
FROM artist
WHERE nationality = (SELECT nationality
                     FROM artist
                     WHERE full_name = 'Claude Monet');

This first solution uses a subquery. The inner SELECT statement does a simple retrieval to return the nationality of Claude Monet. That nationality is the one used in the WHERE clause of the outer query so that all artists who share that nationality are retrieved. (You learned all about subqueries in the lesson on working with subqueries. Refer to that lesson for more information.)

Now look at the same query using a join. You can use a chat with an AI assistant to create the SELECT statement that will do this:

Try the SQL statement.

SELECT a1.artist_id, a1.full_name, a1.nationality
FROM artist AS a1, artist AS a2
WHERE a1.nationality = a2.nationality
AND a2.full_name = 'Claude Monet';

The two tables needed in this query are actually the same table, and so the artist table appears in the FROM clause twice. Although this is perfectly legal, any references to table artist would be ambiguous because the DBMS does not know which artist table you are referring to.

To resolve this problem, table aliases are used. The first occurrence of artist has an alias of a1, and the second has an alias of a2. Now those aliases can be used as table names. The SELECT statement, for example, uses the a1 prefix to explicitly state the full name of the desired columns. If it did not, the DBMS would return an error because there are two of each column named artist_id, full_name, and nationality. It cannot know which one you want. (Even though they are the same.) The WHERE clause first joins the tables and then filters the data by full_name in the second table to return only the wanted data.

16.4 Natural Joins

Whenever tables are joined, at least one column will appear in more than one table (the columns being used to create the join). Standard joins (the inner joins that you learned about in the last lesson) return all data, even multiple occurrences of the same column. A natural join simply eliminates those multiple occurrences so that only one of each column is returned.

How does it do this? The answer is it doesn't — you do it. A natural join is a join in which you select only columns that are unique. This is typically done using a wildcard (SELECT *) for one table and explicit subsets of the columns for all other tables. The following is an example:

SELECT A.*, W.work_id, W.name, W.style, S.subject
FROM artist AS A, work AS W, subject AS S
WHERE A.artist_id = W.artist_id
AND S.work_id = W.work_id
AND S.subject = 'Portrait';

In this example, a wildcard is used for the first table only. All other columns are explicitly listed so that no duplicate columns are retrieved (notice, for instance, that W.artist_id and S.work_id are left out of the column list, since A.* already includes artist_id and W.work_id is already listed). The truth is, every inner join you have created thus far is actually a natural join, and you will probably never need an inner join that is not a natural join.

16.5 Outer Joins

Most joins relate rows in one table with rows in another. But occasionally, you want to include rows that have no related rows. For example, you might use joins to accomplish the following tasks:

• Count how many works each artist has created, including artists who have yet to have a work catalogued.
• List all works with their subjects, including works that have not yet been assigned a subject.
• Calculate the average area of artwork, taking into account artists who have no works recorded at all.

In each of these examples, the join includes table rows that have no associated rows in the related table. This type of join is called an outer join.

The following SELECT statement is a simple inner join. It retrieves a list of all artists and their works:

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

Outer join syntax is similar. To retrieve a list of all artists including those who have no works recorded, you can do the following:

SELECT artist.full_name, work.name
FROM artist
LEFT OUTER JOIN work ON artist.artist_id = work.artist_id;

Like the inner join seen in the last lesson, this SELECT statement uses the keywords OUTER JOIN to specify the join type (instead of specifying it in the WHERE clause). But unlike inner joins, which relate rows in both tables, outer joins also include rows with no related rows. When using OUTER JOIN syntax, you must use the RIGHT or LEFT keywords to specify the table from which to include all rows (RIGHT for the one on the right of OUTER JOIN and LEFT for the one on the left). The previous example uses LEFT OUTER JOIN to select all the rows from the table on the left in the FROM clause (the artist table). To select all the rows from the table on the right, you use a RIGHT OUTER JOIN.

There is one other variant of the outer join, one that tends to be rarely used. The full outer join retrieves all rows from both tables and relates those that can be related. Unlike a left outer join or right outer join, which includes unrelated rows from a single table, the full outer join includes unrelated rows from both tables. The syntax for a full outer join is as follows:

SELECT artist.full_name, work.name
FROM artist
FULL OUTER JOIN work ON artist.artist_id = work.artist_id;

16.6 Using Joins with Aggregate Functions

As you learned in the lesson on summarizing data, aggregate functions are used to summarize data. Although all the examples of aggregate functions thus far only summarized data from a single table, these functions can also be used with joins.

To demonstrate this, let's look at an example. You want to retrieve a list of all artists and the number of works that each has created. You can use a chat with an AI assistant to create the SELECT statement that will do this:

Try the SQL statement.

SELECT artist.artist_id, COUNT(work.work_id) AS num_works
FROM artist
INNER JOIN work ON artist.artist_id = work.artist_id
GROUP BY artist.artist_id;

This SELECT statement uses INNER JOIN to relate the artist and work tables to each other. The GROUP BY clause groups the data by artist, and so the function call COUNT(work.work_id) counts the number of works for each artist and returns it as num_works.

Aggregate functions can be used just as easily with other join types. See the following example:

SELECT artist.artist_id, COUNT(work.work_id) AS num_works
FROM artist
LEFT OUTER JOIN work ON artist.artist_id = work.artist_id
GROUP BY artist.artist_id;

This example uses a left outer join to include all artists, even those who have no works recorded in the work table. The results now include those artists, each shown with a num_works of 0, unlike when the INNER JOIN was used.

16.7 Using Joins and Join Conditions

Before wrapping up this two-lesson discussion on joins, it is worthwhile to summarize some key points regarding joins and their use:

• Pay careful attention to the type of join being used. More often than not, you'll want an inner join, but there are often valid uses for outer joins too.
• Check your DBMS documentation for the exact join syntax it supports. (Most DBMSs use one of the forms of syntax described in these two lessons.)
• Make sure you use the correct join condition (regardless of the syntax being used), or you'll return incorrect data.
• Make sure you always provide a join condition, or you'll end up with the Cartesian product.
• You may include multiple tables in a join and even have different join types for each. Although this is legal and often useful, make sure you test each join separately before testing them together. This will make troubleshooting far simpler.

16.8 Summary

This lesson was a continuation of the last lesson on joins. This lesson started by teaching you how and why to use aliases, and then continued with a discussion on different join types and various forms of syntax used with each. You also learned how to use aggregate functions with joins and some important do's and don'ts to keep in mind when working with joins.