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!

Mike Sherrill 'Cat Recall' · Answer 1 · 2014-11-08T16:49:50.177

0

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

edited Nov 08 '14 at 16:49

answered Nov 08 '14 at 16:39

Mike Sherrill 'Cat Recall'

91,602
17
122
185

so then, a change in the ordering or grouping in inner joins will only affect the speed of the query? – user3903214 Nov 09 '14 at 04:42
1

I don't know, but I doubt that the speed would be different. I'd expect the query planner to pick just about the same execution plan for both queries. You can test by stuffing some random(ish) data into the tables, then using [EXPLAIN](http://www.postgresql.org/docs/current/static/sql-explain.html). – Mike Sherrill 'Cat Recall' Nov 09 '14 at 06:14
1

@user3903214 But if you are selecting and grouping (and maybe aggregating) then that corresponds to a combination of other operations so then it matters how combinations of those operators act, not just combinations of join only. – philipxy Nov 12 '14 at 21:09

Associativity and commutativity of inner joins sql

1 Answers1