Join conditions, intermediate SQL

Question

what's the difference between the join conditions "on" and "using" if both are used to select specified column(s)?

Answer 1

using is just a short-circuit to express the join condition when the related columns have the same name.

Consider the following example:

select ...
from orders o
inner join order_items oi on oi.order_id = o.order_id

This can be shortened with using, as follows:

select ...
from orders o
inner join order_items oi using(order_id)

Notes:

• this also works when joining on several columns having identical names

• parentheses are mandatory with using

Answer 2

The main difference with using is that the columns for the join have to have the same names. This is generally a good practice anyway in the data model.

Another important difference is that the columns come from different tables -- the join condition doesn't specify the tables (some people view this as a weakness, but you'll see it is quite useful).

A handy feature is that the common columns used for the join are removed when you use select *. So

select *
from a join
     b
     on a.x = b.x

will result in x appearing twice in the result set. This is not allowed for subqueries or views. On the other hand, this query only has x once in the result set.

select *
from a join
     b
     using (x)

Of course, other columns could be duplicated.

For an outer join, the value is the non-NULL value, if any. This becomes quite handy for full joins:

select *
from a full join
     b
     using (x) full join
     c
     using (x);

Because of the null values, expressing this without using is rather cumbersome:

select *
from a full join
     b
     on b.x = a.x full join
     c
     on c.x = coalesce(a.x, b.x);