On Sat, Jan 10, 2009 at 7:58 AM, Lukas Haase <lukasha...@gmx.at> wrote: > D. Richard Hipp schrieb: >> On Jan 9, 2009, at 3:16 PM, Lukas Haase wrote: >>> SELECT t.topic, t.length >>> FROM printgroup AS pg1 >>> LEFT JOIN printgroup AS pg2 ON pg1.printgroup = pg2.printgroup >>> LEFT JOIN topics AS t ON t.topicID = pg2.topicID >>> LEFT JOIN topic_ids AS ti ON ti.topicID = pg1.topicID >>> WHERE ti.topic_textID = 'XXXX' >>> ORDER BY pg2.topicID ASC; >> >> You seem very fond of using LEFT JOINs in places where they do not >> make good sense. > > Yes, I started with mySQL 3 many years ago. At the beginning I only knew > about LEFT JOINs and used them. Now I think I also know the other types > of JOINs but I still use LEFT JOINs very often, just by habit. And with > mySQL I never had performance problems with them. > >> What is it that you think a LEFT JOIN does? > > (A LEFT JOIN B) joins together table A and B while all records are taken > from A and only records that match both are takes from B. If a record > from A has no corresponding data in B, the values are NULL. > >> How is >> a LEFT JOIN different than an ordinary inner JOIN? > > INNER JOIN takes *all* records from both tables, A and B. Generally, the > resultset will be larger.
all the rows from both tables A and B *that match* the join condition... in other words, unlike a LEFT (or a RIGHT) JOIN, which would include even those rows where only A (or B) match but show a NULL value for the B (or A) table, an INNER JOIN, aka, just JOIN, would usually have a smaller result set. Unless and until both tables provided a match (and stuffing NULL is not a match), a row would not be included in the result set. > >> I ask because I >> suspect that your answer will reveal misconceptions about LEFT JOINs >> which, when rectified, will cause most of your performance issues to >> go away. > > Maybe my I think too much in "left joining" but I did not know that > there is so much difference in performance. > > Best Regards, > Luke > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
