Imagine having a tool that can automatically detect JPA and Hibernate performance issues.
Hypersistence Optimizer is that tool!
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:
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
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.
2 Comments on “SQL JOIN USING – A Beginner’s Guide”
On Oracle (and maybe the others) the using column is also deduplicated from the output and no longer associated with any tables. Usually this is not a problem as the join column isn’t of interest, but since you selected for it, you’d need to change p.post_id to just post_id.
On Oracle (and maybe the others) the using column is also deduplicated from the output and no longer associated with any tables. Usually this is not a problem as the join column isn’t of interest, but since you selected for it, you’d need to change p.post_id to just post_id.
This behavior only applies to Oracle. The alias works just fine in SQL Server, PostgreSQL, and MySQL.