Kristoffer Danielsson wrote: > Q: Does foreign keys affect NATURAL JOINs? > > I haven't tested this. Does this NATURAL JOIN produce an inner join or a > cartesian product?
The presence of foreign key constraints has no effect on the results of any queries, using natural joins or otherwise. A natural join between 2 tables/rowsets gives you a cartesian product if and only if none of the column names are the same. A natural join gives you an inner join if and only if at least one column name is the same in both tables/rowsets. > Anyway, I think this should be documented. What I have described above is normal/proper behavior in relational or SQL DBMSs. What details exactly are you proposing need explicit documentation versus what would be going too far or stating the obvious? > SQLite 3.6.18 sample (NO foreign keys): > > CREATE TABLE t1 (a INTEGER PRIMARY KEY, b INTEGER NOT NULL); > > CREATE TABLE t2 (x INTEGER PRIMARY KEY, y INTEGER NOT NULL); > > SELECT * FROM a NATURAL JOIN b; -- Cartesian product! I think you meant to say: SELECT * FROM t1 NATURAL JOIN t2; ... and yes, this is indeed a cartesian product since (a,b)∩(x,y) is the empty set. > SQLite 3.6.19 sample (using foreign keys): > > CREATE TABLE t1 (a INTEGER PRIMARY KEY, b INTEGER NOT NULL); > > CREATE TABLE t2 (x INTEGER PRIMARY KEY, FOREIGN KEY(y) REFERENCES t1(a) ); > > SELECT * FROM a NATURAL JOIN b; -- Inner join?? I think you meant to say: CREATE TABLE t2 (x INTEGER PRIMARY KEY, y INTEGER NOT NULL, FOREIGN KEY(y) REFERENCES t1(a) ); SELECT * FROM t1 NATURAL JOIN t2; ... and no, this is also a cartesian product since (a,b)∩(x,y) is the empty set. -- Darren Duncan _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
