Perhaps one of the lesser-known clauses when it comes to SQL joins is the USING
clause. While the more widely-used ON
clause allows us to explicitly specify join conditions, the USING
clause simplifies the syntax when the join is based on columns with the same name in both tables.
In this article, we’ll dive into the USING
clause, compare it to the ON
clause, and look at examples that illustrate the difference.
What is the USING
Clause?
The USING
clause is used in SQL when performing joins (inner, left, right, or outer joins) and allows us to specify one or more columns on which the tables should be joined. The key difference between the USING
clause and the ON
clause is that USING
assumes that the joining column names in both tables are identical.
Here’s the basic syntax for the USING
clause:
SELECT columns
FROM table1
JOIN table2
USING (common_column[, common_column] ...);
This syntax conveys that we can provide one or more common columns to join on. The column(s) specified in the USING
clause must exist in both tables. It provides a streamlined, cleaner syntax for common cases, avoiding the repetitive nature of specifying the column from each table, as we would with the ON
clause.
Example: USING
vs ON
Clause
Let’s explore this with an example. Suppose we have two tables, employees
and departments
.
employees table:
emp_id | first_name | last_name | department_id |
---|---|---|---|
1 | Blake | Baxter | 101 |
2 | Eva | Everton | 102 |
departments table:
department_id | department_name |
---|---|
101 | Sales |
102 | HR |
We want to join these tables on the department_id
column. Here’s how we can do this using both the USING
and ON
clauses.
Using the ON
Clause
SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
JOIN departments
ON employees.department_id = departments.department_id;
Here, we explicitly mention the department_id
column from both tables in the ON
clause to perform the join.
Using the USING
Clause
SELECT first_name, last_name, department_name
FROM employees
JOIN departments
USING (department_id);
With the USING
clause, we only need to specify the common column department_id
once, and the database engine implicitly knows to join based on this column from both tables.
Key Differences Between USING
and ON
- Simplicity: The
USING
clause is simpler when there are common names for the joining columns. It also reduces repetition by avoiding the need to specify the table name for the join condition, which is required with theON
clause. - Ambiguity Handling: In queries using the
ON
clause, both tables retain their original column names, so if we need to use thedepartment_id
in the result set, we’d need to refer to it asemployees.department_id
ordepartments.department_id
. However, when using theUSING
clause, SQL will only return onedepartment_id
in the result. - Flexibility: The
ON
clause offers more flexibility because we can join tables on conditions other than equality or even on columns with different names. TheUSING
clause, on the other hand, can only be used when both columns have the same name and the join condition is equality (=
). For instance, with theON
clause we can use more complex conditions such as:
ON employees.salary > departments.avg_salary
Which DBMSs Support the USING
Clause?
The USING
clause is supported by many of the popular relational database management systems, but it’s not universally supported by all of them. Here’s a list of major DBMSs that support the USING
clause:
- PostgreSQL
- MySQL
- SQLite
- Oracle
- MariaDB
Microsoft SQL Server (T-SQL) does not support the USING
clause. For SQL Server, we must use the ON
clause for joins.
When to Use the USING
Clause
The USING
clause can be useful when:
- The tables you are joining share column names.
- You prefer the cleaner and more readable SQL syntax.
- You’re working on a DBMS that supports it (like PostgreSQL or MySQL).
For example, if you have many common columns between two tables, the USING
clause can significantly reduce code repetition and potential confusion.
However, if your column names differ between tables or if you need to use more complex join conditions, the ON
clause will be required.
Also, just because you can use the USING
clause, doesn’t mean you should. You may choose to use the ON
clause instead of USING
in order to keep your code as explicit as possible, so that it’s as clear as possible exactly which columns are joining with each other. Or you may wish to use ON
in order to keep your code as universal as possible in case the database is ported to a DBMS that doesn’t support the USING
clause.
Conclusion
The USING
clause is a convenient and less verbose way to join tables on common column names. It simplifies the syntax but is limited in flexibility compared to the ON
clause. Whether you choose to use it will depend on multiple factors, including your DBMS, the nature of your data, and personal preference.