inner and natural joins

Today someone told me about natural and inner joins. As I’m using SQL for many years already, I was a bit puzzled at first. I heard of the terms, but thought till now, that they were meaning the same.

The first thing I did was looking in the PostgreSQL documentation and yes, they are not the same. But they are also the same.

The inner join is the default for doing joins. It just joins two tables using the on clause.

# select *
  from tableA A
  join tableB B
    on A.id = B.id;

| a.id | b.id |
|------|------|
|    3 |    3 |

Now an interesting thing is, that the on clause can be replaced by a using clause when both tables provide the same columns. This not only makes the select a bit shorter, but also reduces the number of columns in the result. All columns listed in the using clause will be left out from the result and replaced with a new column with the name used in the using clause. The select from above would then look like this

# select *
  from tableA A
  join tableB B
  using (id);

| id |
|----|
|  3 |

The natural join goes one step further and tries to search for common columns itself and generate a using clause itself. The resulting query then looks like this

# select *
  from tableA A
  natural join tableB B;

| id |
|----|
|  3 |

As nice as this seems it can backfire pretty fast, when one has two tables with a column of the same name, but completely different content not meant to be joined. Then it is possible just to get nothing.

# select * from foo limit 1;
 id | test | bar 
----+------+-----
  1 | 1    | 3

# select * from baz limit 1;
 id | test | bar 
----+------+-----
  1 | 1    | 20

# select * from foo natural join baz
 id | test | bar 
----+------+-----

As all columns are named the same, but the content is different in column bar, no common row is found and therefore returned.

For further information, the PostgreSQL documentation is pretty good.