Dealing mostly with Rails style databases we generally write inner joins on ids and foreign keys but there are other ways to create joins, the most fun being the natural join.
The below query works like a natural join but uses
inner join syntax. When
two tables having columns with the same name, join on that column. You can see
that both columns have been returned even though they have the same
chris=# select * from (select 1 x) foo inner join (select 1 x) bar on bar.x = foo.x ; x | x ---+--- 1 | 1 (1 row)
When using the
natural join syntax the query is less verbose and only returns one column. Postgres knows that they have the same value, thats the whole point! So only present one column.
chris=# select * from (select 1 x) foo natural join (select 1 x) bar; x --- 1 (1 row)