4 ms·

> Although it’s possible to join using a WHERE clause (an implicit join), prefer an explicit JOIN instead, as the ON keyword can take advantage of the database’

> Although it’s possible to join using a WHERE clause (an implicit join), prefer an explicit JOIN instead, as the ON keyword can take advantage of the database’s index. This implies that WHERE style join can't use indices. I can understand why some would prefer either syntax for readability/style reasons. But the idea that one uses indices and the other not, seems highly dubious. Looking at the postgres manual [1], the WHERE syntax is clearly presented as the main way of inner joining tables. The JOIN syntax is described as an "alternative syntax": > This [INNER JOIN] syntax is not as commonly used as the one above, but we show it here to help you understand the following topics. Maybe some database somewhere cannot optimise queries properly unless JOIN is used? Or is this just FUD? [1] https://www.postgresql.org/docs/13/tutorial-join.html https://www.postgresql.org/docs/13/tutorial-join.html

SigmundA 6y ago
Yes the optimizer should end up with the same plan either way, although the ON syntax is SQL standard.
joelcollinsdc 6y ago
Maybe not for indexes but what about using a sql syntax that is more common and extensible?
FranzFerdiNaN 6y ago
I don’t think I have ever seen that way of doing an inner join in the wild, despite working as a DBA or data engineer for the past 15 years, 10 of those Postgres-only roles.
- matwood 6y ago
  I started my dba roles back with mssql 6.5, and the join using where was all that was supported. I found the join syntax much more clear around intent and moved as soon as it was available.
mtone 6y ago
> 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/deprecation-of-old-style-join-syntax-only-a-partial-thing https://docs.microsoft.com/en-us/archive/blogs/wardpond/depr... [2] https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm https://docs.oracle.com/cd/B19306_01/server.102/b14200/queri... [3] https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/pe... [4] https://dev.mysql.com/doc/refman/5.7/en/join.html https://dev.mysql.com/doc/refman/5.7/en/join.html
- colonwqbang 6y ago
  I'm just quoting the manual here. I have no idea which style is really prevalent in the wild, now or X years ago. 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).
  - mtone 6y ago
    Indeed, they will perform the same or very close (the query plan might differ a bit due to the different orderings). Not sure where the author got that from. I'm complaining about the docs only.
majewsky 6y ago
I prefer the JOIN syntax because I don't have to rewrite everything when I realize later on that I need an OUTER JOIN.
deleted 6y ago
[deleted]
gopalv 6y ago
> 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.

SigmundA 6y ago

Yes the optimizer should end up with the same plan either way, although the ON syntax is SQL standard.

joelcollinsdc 6y ago

Maybe not for indexes but what about using a sql syntax that is more common and extensible?

FranzFerdiNaN 6y ago

I don’t think I have ever seen that way of doing an inner join in the wild, despite working as a DBA or data engineer for the past 15 years, 10 of those Postgres-only roles.

matwood 6y ago
I started my dba roles back with mssql 6.5, and the join using where was all that was supported. I found the join syntax much more clear around intent and moved as soon as it was available.

mtone 6y ago

> 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/deprecation-of-old-style-join-syntax-only-a-partial-thing https://docs.microsoft.com/en-us/archive/blogs/wardpond/depr... [2] https://docs.oracle.com/cd/B19306_01/server.102/b14200/queries006.htm https://docs.oracle.com/cd/B19306_01/server.102/b14200/queri... [3] https://docs.microsoft.com/en-us/sql/relational-databases/performance/joins?view=sql-server-ver15 https://docs.microsoft.com/en-us/sql/relational-databases/pe... [4] https://dev.mysql.com/doc/refman/5.7/en/join.html https://dev.mysql.com/doc/refman/5.7/en/join.html

colonwqbang 6y ago
I'm just quoting the manual here. I have no idea which style is really prevalent in the wild, now or X years ago. 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).
- mtone 6y ago
  Indeed, they will perform the same or very close (the query plan might differ a bit due to the different orderings). Not sure where the author got that from. I'm complaining about the docs only.

majewsky 6y ago

I prefer the JOIN syntax because I don't have to rewrite everything when I realize later on that I need an OUTER JOIN.

deleted 6y ago

[deleted]

gopalv 6y ago

> 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.