Joins: USING vs ON for join conditions

A subtle difference between ON and USING when establishing join conditions

Neil Sainsbury

2 minute read

When establishing the join condition between two tables in PostgreSQL, and you’re performing an equality join where the columns are named the same in both tables, a nice shorthand syntax to establish the join is with USING. You might think this is identical to establishing the join condition with ON, but there’s a subtle difference. Let’s dive in and take a look with an example (you can find the full source code to reproduce this example on GitHub here)

The Setup

Two classic tables, employees and departments.

select *
from departments;

select *
from employees;
dept_id|description             |
-------|------------------------|
      1|Accounting              |
      2|Finance                 |
      3|Research and Development|
      4|Marketing               |
      5|Sales                   |

employee_id|dept_id|first_name|last_name|
-----------|-------|----------|---------|
          1|      1|John      |Smith    |
          2|      4|Peter     |Maybank  |
          3|      5|Samantha  |Savoy    |
          4|      2|Evan      |Baxter   |
          5|      3|Kamila    |Erdos    |

ON vs USING

select *
from employees as e
  inner join departments as d
    on e.dept_id = d.dept_id;
employee_id|dept_id|first_name|last_name|dept_id|description             |
-----------|-------|----------|---------|-------|------------------------|
          1|      1|John      |Smith    |      1|Accounting              |
          2|      4|Peter     |Maybank  |      4|Marketing               |
          3|      5|Samantha  |Savoy    |      5|Sales                   |
          4|      2|Evan      |Baxter   |      2|Finance                 |
          5|      3|Kamila    |Erdos    |      3|Research and Development|

5 rows

When joining with ON, notice that the output contains 6 columns as you would expect - each column from each table is in the result.

When joining with USING however, we get only 5 columns in the output:

select *
from employees as e
  inner join departments as d using (dept_id);
dept_id|employee_id|first_name|last_name|description             |
-------|-----------|----------|---------|------------------------|
      1|          1|John      |Smith    |Accounting              |
      4|          2|Peter     |Maybank  |Marketing               |
      5|          3|Samantha  |Savoy    |Sales                   |
      2|          4|Evan      |Baxter   |Finance                 |
      3|          5|Kamila    |Erdos    |Research and Development|

5 rows

The dept_id column, the column we’re joining the two tables on, only appears once in the output. Why? Because USING performs an equality join and can only be used when the column names are identical, it’s unnecessary to include the column twice. A nice side-effect of this is convenience - when you’re using USING, if you want to refer to the column in your SELECT clause or elsewhere, you don’t need to prefix the column name with the table name!

As with all my blog posts, you can find the full source code on GitHub here.