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.