Introduction

In this article, we are going to see how the JOIN USING clause works in SQL, and how we can use it to replace the ON condition clause when the columns used to join the two tables have the same name in both the parent and the child tables.

Database tables

To demonstrate how the JOIN USING clause works, we are going to use the following post and post_comment database tables, which form a one-to-many table relationship via the post_id Foreign Key column in the post_comment table that references the post_id Primary Key column in the post table:

There are the following 3 rows in the parent post table:

| post_id | title     |
|---------|-----------|
| 1       | Java      |
| 2       | Hibernate |
| 3       | JPA       |

and the post_comment child table has the following 3 records:

| post_comment_id | review    | post_id |
|-----------------|-----------|---------|
| 1               | Good      | 1       |
| 2               | Excellent | 1       |
| 3               | Awesome   | 2       |

SQL JOIN ON clause with custom projection

Typically, when writing an INNER JOIN or LEFT JOIN query, we would use the ON clause to define the join condition.

For instance, to get the comments as well as the associated post title and post identifier, we can write the following SQL projection query:

SELECT
   post.post_id,
   title,
   review
FROM post
INNER JOIN post_comment ON post.post_id = post_comment.post_id
ORDER BY post.post_id, post_comment_id

And, we will get back the following result set:

| post_id | title     | review    |
|---------|-----------|-----------|
| 1       | Java      | Good      |
| 1       | Java      | Excellent |
| 2       | Hibernate | Awesome   |

SQL JOIN USING clause with custom projection

When the Foreign Key column and the column it references have the same name, you can use the USING clause, like this:

SELECT
  post_id,
  title,
  review
FROM post
INNER JOIN post_comment USING(post_id)
ORDER BY post_id, post_comment_id

And, the result set of the above query is going to be identical to the previous SQL query that used the ON clause:

| post_id | title     | review    |
|---------|-----------|-----------|
| 1       | Java      | Good      |
| 1       | Java      | Excellent |
| 2       | Hibernate | Awesome   |

The USING clause works for Oracle, PostgreSQL, MySQL, and MariaDB. SQL Server doesn’t support the USING clause, so you need to use the ON clause instead.

The USING clause can be used with INNER, LEFT, RIGHT, and FULL JOIN statements.

SQL JOIN ON clause with SELECT *

Now, if we change the previous ON clause query to select all columns:

SELECT *
FROM post
INNER JOIN post_comment ON post.post_id = post_comment.post_id
ORDER BY post.post_id, post_comment_id

We are going to get the following result set:

| post_id | title     | post_comment_id | review    | post_id |
|---------|-----------|-----------------|-----------|---------|
| 1       | Java      | 1               | Good      | 1       |
| 1       | Java      | 2               | Excellent | 1       |
| 2       | Hibernate | 3               | Awesome   | 2       |

Notice that the post_id is duplicated because both the post and post_comment tables contain a post_id column.

SQL JOIN USING clause with SELECT *

On the other hand, if we run a SELECT * query that features the USING clause:

SELECT *
FROM post
INNER JOIN post_comment USING(post_id)
ORDER BY post_id, post_comment_id

We are going to get the following result set:

| post_id | title     | post_comment_id | review    |
|---------|-----------|-----------------|-----------|
| 1       | Java      | 1               | Good      |
| 1       | Java      | 2               | Excellent |
| 2       | Hibernate | 3               | Awesome   |

We can see that the post_id column is deduplicated, so there is a single post_id column being included in the result set.

Online Workshops

If you enjoyed this article, I bet you are going to love my upcoming 4-day Online Workshop!

• High-Performance Java Persistence Online Workshop (4 hours x 4 days) between the 19th and 22nd of April

Conclusion

If you design your database schema so that Foreign Key column names match the columns they reference, and the JOIN conditions only check if the Foreign Key column value is equal to the value of its mirroring column in the other table, then you can employ the USING clause.

Otherwise, if the Foreign Key column name differs from the column it references or you want to include a more complex joining condition, then you should use the ON clause instead.

Insert details about how the information is going to be processed

DOWNLOAD NOW