Kristoffer Danielsson wrote: > 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!
It doesn't. In fact "t1 NATURAL JOIN t1" would do the exact opposite, because *all* of the columns have the same names, and moreover because both rowsets being joined are the same rowset, the result should be identical to if you said "t1 INTERSECT t1", which is the same as if you simply said "t1" without a join at all. Natural joining something with itself results in itself, and is analogous to "1 * 1 = 1" in math. > Why does the statement below NOT yield a cartesian product? > > SELECT COUNT(*) FROM (t1) NATURAL JOIN (t1); -- Several magnitudes faster > than the query above! This statement should have an identical result to the first one. Having parenthesis around each t1 should make no difference. > 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"! If SQLite is treating the above 2 queries differently, I would think that an error. Are you sure that's what's happening? If you are natural joining a table to itself, or intersecting a table with itself, or unioning a table with itself, then hopefully the optimizer is smart enough to replace that operation with simply the table itself. -- Darren Duncan _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
