SQL JOIN USING – A Beginner’s Guide
Imagine having a tool that can automatically detect JPA and Hibernate performance issues. Wouldn’t that be just awesome?
Well, Hypersistence Optimizer is that tool! And it works with Spring Boot, Spring Framework, Jakarta EE, Java EE, Quarkus, or Play Framework.
So, enjoy spending your time on the things you love rather than fixing performance issues in your production system on a Saturday night!
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.
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
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.
