| > This syntax is not as commonly used as the one above This is going to need some sources. Is it true today? And why did they put parentheses in the ON condition? Worth nothing that there were variants of the WHERE syntax to support left joins using vendor-specific operators such as A += B, A = B (+) -- those are clearly deprecated today. [1] [2] I have a really hard time finding any source on the internet that recommends using the WHERE style joins. So by extension, I wouldn't expect to be used much anymore except for legacy projects. MS SQL Server docs docs mention ON syntax being "preferred" [3], and MySQL says "Generally, the ON clause serves for conditions that specify how to join tables, and the WHERE clause restricts which rows to include in the result set." [4] The PostgreSQL docs seem misleading and outdated to me. [1] https://docs.microsoft.com/en-us/archive/blogs/wardpond/depr... [2] https://docs.oracle.com/cd/B19306_01/server.102/b14200/queri... [3] https://docs.microsoft.com/en-us/sql/relational-databases/pe... [4] https://dev.mysql.com/doc/refman/5.7/en/join.html |
My goal was only to cast doubt on the idea that WHERE clauses in general can't use indices.
Sure, let's debate what the nicest style is. But let's not claim that our preferred style somehow makes the DB go faster (without some kind of proof).