Is this correct behavior for natural join?
I have 2 tables:
CREATE TABLE table_1 (rowid integer primary key, name string, join_code string)
CREATE TABLE table_2 (rowid integer primary key, join_code string, type string)
insert the following values:
insert into table_1 (name, join_code) values ('n101', 'one');
insert into table_1 (name, join_code) values ('n102', 'two');
insert into table_1 (name, join_code) values ('n103', 'one');
insert into table_1 (name, join_code) values ('n104', 'one');
insert into table_1 (name, join_code) values ('n105', 'two');
insert into table_2 (join_code, type) values ('one', 'house');
insert into table_2 (join_code, type) values ('one', 'apartment');Then perform the following queries: select * from table_1 natural join table_2;
table_1. rowid, table_1.name, table_1.join_code, table_2. type 1, n101, one, house 2, n102, two, apartment
select * from table_1 inner join table_2 using (join_code);
table_1. rowid, table_1.name, table_1.join_code, table_2.rowid, table_2. type
1, n101, one, 1, house
2, n102, two, 2, apartment
3, n103, one, 1, house
4, n104, one, 1, house
5, n105, two, 2 apartment
It is obvious that SQLite is performing the natural join on both the rowid and the join_code fields. Is this the correct behavior? I would have thought it should ignore the primary key field when performing a natural join.
Philip
