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
