SQL Joins with the USING Clause: How It Compares to the ON Clause

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 the ON clause.
• Ambiguity Handling: In queries using the ON clause, both tables retain their original column names, so if we need to use the department_id in the result set, we’d need to refer to it as employees.department_id or departments.department_id. However, when using the USING clause, SQL will only return one department_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. The USING 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 the ON 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.