Associativity and commutativity of inner joins sql

Question

Let us say I have this schema.

Boats
_____
bid
bname

Reserves
________
sid
bid
date

Sailors
_______
sid
sname

I know that inner joins are supposed to be both associative and commutative, but I cannot really understand why.

Given the query:

SELECT sname, bname
FROM (Sailors NATURAL INNER JOIN Boats) NATURAL INNER JOIN Reserves

I am thinking that this should return null since Sailors and Boats have no common fields, while:

SELECT sname, bname
FROM (Sailors NATURAL INNER JOIN Reserves) NATURAL INNER JOIN Boats

should return the names of Sailors and the names of Boats they reserved.

Please tell me why inner joins are then supposed to be both commutative and associative.

Thanks!

Answer 1

SELECT sname, bname
FROM (Sailors NATURAL INNER JOIN Boats) NATURAL INNER JOIN Reserves

I am thinking that this should return null since Sailors and Boats have no common fields, . . .

In PostgreSQL a natural join between two tables that have no common columns behaves like a cross join.

create table boats (
  bid integer primary key,
  bname varchar(15)
);

create table sailors (
  sid integer primary key,
  sname varchar(15) 
);

insert into boats values (1, 'One'), (2, 'Two'), (3, 'Three');
insert into sailors values (1, 'One'), (2, 'Two'), (3, 'Three');

SELECT sname, bname
FROM (Sailors NATURAL INNER JOIN Boats);

sname    bname
--
One      One
One      Two
One      Three
Two      One
Two      Two
Two      Three
Three    One
Three    Two
Three    Three