Natural join vs inner join giving different number of rows for a simple query in sqlite3?-CodePudding

I've been under the impression from my class and googling that the only difference between an inner join and a natural join is that a natural join only returns one copy of the column you're joining on, whereas an inner join returns both. So I was surprised to discover in the following (simplified) example that a natural join returns 2 rows (which seems correct to me), whereas an inner join returns 4 (which seems wrong).

Simplified example:

/* Delete the tables if they already exist */
drop table if exists Movie;
drop table if exists Reviewer;
drop table if exists Rating;

/* Create the schema for our tables */
create table Movie(shared1 int, unshared2 text);
create table Rating(shared1 int, unshared3 int);

insert into Movie values(1, 'a');
insert into Movie values(2, 'b');
insert into Rating values(1,3);
insert into Rating values(1,3);

then the sql results...

sqlite> select * from Movie natural join Rating;
1|a|3
1|a|3

/*seems correct*/

sqlite> select * from Movie inner join Rating;
1|a|1|3
1|a|1|3
2|b|1|3
2|b|1|3

/*seems strange and/or wrong*/

sqlite> select * from Movie inner join Rating on Movie.shared1 = Rating.shared1;
1|a|1|3
1|a|1|3

/*seems right*/

CodePudding user response：

In this query:

select * from Movie inner join Rating

you use INNER JOIN without an ON clause.

This is equivalent to a CROSS JOIN:

select * from Movie cross join Rating

Typically an INNER JOIN should have an ON clause but in SQLite it can be omitted.

From Determination of input data (FROM clause processing):

If the join-operator is "CROSS JOIN", "INNER JOIN", "JOIN" or a comma (",") and there is no ON or USING clause, then the result of the join is simply the cartesian product of the left and right-hand datasets.

See the demo.