JOIN USING to Link By Column

1. Inner JOIN with USING

As we have discussed, natural joins are not always ideal, because they will join all common columns between the two tables. Although in some cases this may not be an issue, there can be common columns for a variety of purposes.

Let's take a look at the product and category tables that we created in the prior tutorial, and which had an issue with the natural join:

CREATE TABLE category ( category_id serial PRIMARY KEY, name VARCHAR (100) NOT NULL );

CREATE TABLE product ( product_id serial PRIMARY KEY, name VARCHAR (100) NOT NULL, category_id INT NOT NULL, FOREIGN KEY (category_id) REFERENCES category (category_id) );

INSERT INTO category (name)
VALUES ('Game'), ('Movie'), ('CD');

INSERT INTO product (name, category_id)
VALUES ('Call of Duty', 1), ('Final Fantasy', 1), ('Wizard of Oz', 2), ('Jaws', 2), ('Great Hits', 3), ('Journey', 3);

When we tried to run a natural join:

SELECT * 
FROM product
NATURAL JOIN category;

We got the following result:

This was due to the fact that the tables had two common attributes. However, the name columns did not have values that matched between the two tables. The JOIN and USING clauses will help with this, and allow us to pick which common attribute to join.

The syntax for the command looks like this:

SELECT <columnlist> 
FROM <table1>
INNER JOIN <table2> USING (<commonattribute>);

In our set of tables, we can use the JOIN and USING clauses to focus on category_id:

SELECT * 
FROM product
INNER JOIN category USING (category_id);

Since name also exists in both tables, we can also use name:

SELECT * 
FROM product
INNER JOIN category USING (name);

However, again, since none of the names between the tables match, no rows are returned.

Let us go back to some of our other tables, like the album and artist table.

Right now, we have only focused on each individual table. We don’t know which album has which artist unless we look up the artist_id. However, since the artist_id exists in both tables, we can use the JOIN and USING clause to join the tables on the artist_id:

SELECT * 
FROM album
INNER JOIN artist USING (artist_id);

Now our data is starting to make a bit more sense, as we join it together.

2. Adding Complexity

We can add more than just two tables together by adding additional INNER JOIN statements with USING. If we take the example of artist and album, we can also identify the tracks on each:

SELECT * 
FROM track
INNER JOIN album USING (album_id)
INNER JOIN artist USING (artist_id);

As you can see, by using the * as we start to join more tables, we may have too many columns being returned. We can specify in the SELECT clause which columns should be returned. It is a best practice to use the format <tablename>.<columnname> when we list the columns. Otherwise, if a column name exists in more than one table, the database does not know which column you want to display and returns an ambiguous error. For example, “name” exists in the track and artist table. If we try to simply include the name column in the SELECT clause:

SELECT name 
FROM track
INNER JOIN album USING (album_id)
INNER JOIN artist USING (artist_id);

We will get the following error message:

Note with an INNER JOIN, you can add in the artist_id, album_id, and track_id as the result set only has one item. However, this is not a good practice.

SELECT album_id, artist_id, track_id, album.title, artist.name, track.name 
FROM track
INNER JOIN album USING (album_id)
INNER JOIN artist USING (artist_id)
ORDER BY album_id, artist_id, track_id;

It would be a better practice to use the same labeling as the following:

SELECT album.album_id, artist.artist_id, track.track_id, album.title, artist.name, track.name 
FROM track
INNER JOIN album USING (album_id)
INNER JOIN artist USING (artist_id)
ORDER BY album.album_id, artist.artist_id, track.track_id;

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.