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*/
>Solution :
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.