can't get natural join to work

Question

I'm using postgresql, though I confirmed this in sqlfiddle.com too.

My tables and elements are:

create table Publisher(pID int PRIMARY KEY, name varchar(255), address varchar(255));
create table Book(ISBN int PRIMARY KEY, name varchar(255), genre varchar(255), price int, copies int, pID int REFERENCES Publisher(pID));
insert into Publisher values(1, 'Oxford University Press', 'Senkosova');
insert into Book values(111, 'Alamut', 'Horror', 50, 100, 1);

I want to natural join Book and Publisher and get the books which is published by Oxford University Press.

This works:

select b.name
from Book as b, Publisher as p
where b.pid = p.pid and p.name ='Oxford University Press';

This does not:

select b.name
from Book as b natural join Publisher as p
where p.name = 'Oxford University Press';

Even this does not:

select *
from Book natural join Publisher;

Why??

Gordon Linoff · Answer 1 · 2016-11-06T16:41:51.097

Do not use natural join, it is a bug waiting to happen. Instead, use an explicit on or using clause:

select b.name
from Book b join
     Publisher p
     using (pid)
where p.name = 'Oxford University Press';

or:

select b.name
from Book b join
     Publisher p
     where b.pid = p.pid
where p.name = 'Oxford University Press';

The problem with natural join is two-fold. First, it bases the columns for comparisons on common names, not even on declared foreign key relationships. Both your tables have a name column as well as an id column, so at a minimum, the natural join query is equivalent to:

select b.name
from Book b join
     Publisher p
     where b.pid = p.pid and b.name = p.name 
where p.name = 'Oxford University Press';

(And there might be other columns that have the same name.) I doubt any books are named 'Oxford University Press', so you have no matches in the data.

The second problem is related. By not including explicitly the columns used for joining, a reader of the query does not know. That makes the query harder to debug and understand. In my case, almost all tables that I create have CreatedAt and CreatedBy columns, so natural joins would never work in my databases anyway.

Thank you for your comprehensive answer! – TugRulz Nov 06 '16 at 16:44 — TugRulz, Nov 06 '16 at 16:44

score 3 · Answer 2 · answered Nov 06 '16 at 16:21

You have more than one pair of columns with the same names in both tables: id and name. So

select *
from book 
natural join publisher;

is equivalent to

select *
from book b
join publisher p on b.pid = p.pid 
and b.name = p.name;

Per the documentation:

NATURAL is a shorthand form of USING: it forms a USING list consisting of all column names that appear in both input tables. As with USING, these columns appear only once in the output table. If there are no common column names, NATURAL behaves like CROSS JOIN.

can't get natural join to work

2 Answers2