| > the WHERE syntax is clearly presented as the main way of inner joining tables. As someone who debugs a lot of SQL, I prefer the ON clause for one major reason - it is easy to notice it if it is missing. So there was a customer who had a wrote 300 terabytes of intermediate data out of a badly written query, which wasn't caught because they ran the equivalent of "select * from orders, customers ... " and ended up commenting out the o_cust_id = c_cust_id in the bottom of the where clause while they were messing about with it. And another example of a CTE which was missing the ON clause, but the outer query did have a where clause and that was great till someone else cut-pasted the CTE into a materialized view & tried to rebuild it. > Maybe some database somewhere cannot optimise queries properly unless JOIN is used? Until Apache calcite came in, Apache Hive could not always find the table which was joined against out of a where clause (so you'd find TPC-H queries which were planned as cross-products between the same table with different aliases etc - like Query 7 was badly planned from the beginning of Hive till Hive 1.2 and only optimally planned in Hive 3.x). But SQL engines have gotten much better over the years & most people should write it for readability than for execution order, but the readability is really why I like the ON syntax, particularly while chop-debugging a big query written by some poor guy who was translating COBOL into SQL. |