This is exactly the kind of advice I was looking for. Thanks again!
Ben > On Oct 27, 2019, at 4:04 PM, Simon Slavin <slav...@bigfraud.org> wrote: > > On 27 Oct 2019, at 10:45pm, Benjamin Asher <benashe...@gmail.com> wrote: > >> Query A >> >> SELECT * FROM tab1 LEFT JOIN tab2 ON tab2.x='constant' WHERE >> tab1.x='constant’; >> >> Query B >> >> SELECT * FROM tab1 LEFT JOIN tab2 ON tab1.x=tab2.x WHERE tab1.x=‘constant' > > Your use of JOIN here is extremely usual. Depending on your data, you either > don't need it, or it does nothing. > > Suppose you have many rows in tab1 where x='constant' and also many rows in > tab2 where x='constant'. What are you expecting the engine to do with them ? > Should it match one tab1 row with one tab2 row ? Or should it generate a > set of every possible combination ? This tells you what your ON clause > should be. > > Generally, a constant belongs in a WHERE clause. To answer your original > question > >> Is there an advantage to putting WHERE-type filtering in the ON constraint >> vs leaving it in the WHERE clause for LEFT JOINs? > > SQLite reads the entire statement and does things to figure out the best way > to execute it given the indexes available. The kind of deconstruction you > did to the query in your head, happens inside the SQL engine. So we cannot > give you an answer "always put it in ON" or "always put it in WHERE". > Instead we say "Don't make your query look weird just to save a millisecond. > Have your query make sense, so someone who reads it understands what you are > doing.". > > If you want to worry about speed or efficiency, worry about making useful > indexes for your tables instead of exactly how to phrase your query. > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
