Thanks.
This leads me to the next question. Why does the statement below yield a cartesian product? SELECT COUNT(*) FROM t1 NATURAL JOIN t1; -- Sloooooooow! Why does the statement below NOT yield a cartesian product? SELECT COUNT(*) FROM (t1) NATURAL JOIN (t1); -- Several magnitudes faster than the query above! Sure, the query is brain-damaged, but this could happen "by accident" in my software. I'd expect SQLite to optimize this to simply "t1"! > Date: Sat, 17 Oct 2009 21:08:31 -0700 > From: dar...@darrenduncan.net > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Foreign keys + NATURAL JOIN > > 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 _________________________________________________________________ Windows Live: Dina vänner får dina uppdateringar från Flickr, Yelp och Digg när de skickar e-post till dig. http://www.microsoft.com/windows/windowslive/see-it-in-action/social-network-basics.aspx?ocid=PID23461::T:WLMTAGL:ON:WL:sv-se:SI_SB_3:092010 _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
