I’ve been writing SELECT statements for many years now. Often they’re ad-hoc queries in a database client like DBeaver. Sometimes they’re part of an application in a ORM. Regardless, they usually take a simple form and you don’t question it much.

SELECT
  *
FROM employee AS e;

Unless I’m writing a very simple SELECT statement, I’ll often need to join two or more tables. So of course I was taught about the JOIN clause and all it’s left and right and inner and outer trickery. I was also taught that a JOIN must always be accompanied by an ON. The ON statement declared how the left and right results sets will be joined.

SELECT
  *
FROM employee AS e
JOIN department AS d
  ON e.department_id = d.department_id;

So for years I toiled, always dutifully writing my ON with my JOIN. So did all the programmers around me. Often I would fumble the ON clause, putting the same column on both sides of the equals and wondering for an hour why my query wasn’t working. But just recently I learned of an alternative to the ON statement. Somehow I didn’t manage to come across it, even after writing hundreds of SELECT statements. It’s the USING clause.

SELECT
  *
FROM employee AS e
JOIN department AS d
  USING (department_id);

The USING clause is a special shorthand you can use if the column has the same name in the left and right tables that you’re joining. You don’t need specify the left and right tables as this is automatic. In our example here, the employee and department tables both share the department_id column, so all we have to specify to join them is USING (department_id) which is equivalent to ON e.department_id = d.department_id/ Additionally, you can also provide a list of column names if you need to join on more than one column.

The USING clause is documented in PostgeSQL, MySQL, and Oracle. Curiously, it’s not mentioned in their tutorials.

Natural join

To take this one step further, there is yet another clause I hadn’t used: NATURAL. This is a special type of join that is an even shorter hand version of USING. A natural join is equivalent to a USING statement that lists all the columns that both tables have in common.

SELECT
  *
FROM employee AS e
NATURAL JOIN department AS d

In this example, NATURAL JOIN department AS d is equivalent to writing JOIN department AS d USING (department_id), but no columns need to be specified. Just beautiful. So much less typing.

Caveat Emptor

I’m sure some of you take USING and NATURAL JOIN for granted. You might be surprised I haven’t come across it before. I am too! I thought I might share my discovery in case there are others like me.

There are obvious caveats to their usage. Of course, the design of the schema will determine your success with these clauses. If the schema designer decided to not name joining columns the same, then this will not work. For instance, if they chose to use id on the department table instead of department_id. This is a dubious choice as a column named id is easily dislocated; schema designers should take heed. Additionally if the schema is not properly normalized, and there are redundant or extraneous join keys on certain tables, then NATURAL JOIN will not always work.

Also, using queries like this for production workloads can have unintended consequences if the schema were to change underneath you. Some prefer to always have explicit join syntax to avoid this pitfall.

There are also just those times where you’ve got a more complicated JOIN and you need to bust out trusty old ON.

Conclusion

In my opinion, the USING and NATURAL JOIN clauses both provide elegant ways to write concise SELECT statements in a more fluid and intuitive way.