3 ms·

Why does SQL have both WHERE and ON, plus various kinds of JOINs? It seems to me that it would be simpler to have just have one JOIN which represents the Cartes

Why does SQL have both WHERE and ON, plus various kinds of JOINs? It seems to me that it would be simpler to have just have one JOIN which represents the Cartesian product, plus WHERE for filtering - and then the DB engine could apply optimizations so that queries would actually run as fast as in existing SQL. Am I missing something?

rzzzt 8y ago
Cartesian product could be the default behavior if you select from multiple tables. I'm not sure what the standard says about this one.
- CuriousSkeptic 8y ago
  It is. On the topic as such, besides ON and WHERE there is also USING and NATURAL. Som very influential db-people have argued that NATURAL should be the only supported join. Checkout the third manifesto. ON as such allows for outer joins, something you can’t do starting with a Cartesian product. But Oracle has syntax for outer joins using WHERE if you prefer that.
  - rzzzt 8y ago
    Aah, very good! Outer joins could be achieved with NVL and a suitable default value, if you don't want to do the (+) thing? Edit: you don't get to pair all rows from one table with an all-NULL "fictional" row from the other table in a cartesian product, so the answer is no
barrkel 8y ago
The bit your missing is the awkwardness of expressing outer joins. The way Oracle does it is an existence proof of its awkwardness. The 'FROM' clause defaults to Cartesian product for multiple tables. You can then specify your join conditions in the where clause, but it makes outer joins harder to write - Oracle does them with special (+) operator, but it's clumsy. It doesn't (shouldn't) make much difference at all to the database whether you specify your join condition after the `ON` or in the `WHERE` - if it doesn't hoist `WHERE` conditions into the join clause, and potentially change the join order because of it, it's missing big optimization opportunities.
- craigds 8y ago
  Can you expand on how oracle does it? I can't imagine any reasonable way to do outer joins in a where clause. For inner joins the grandparent is correct and it should never matter whether you use ON or a where clause.
  - barrkel 8y ago
    https://stackoverflow.com/questions/22678643/old-style-oracle-outer-join-syntax-why-locate-the-on-the-right-side-of-the https://stackoverflow.com/questions/22678643/old-style-oracl... Of course it's weird and ugly. I don't think there's anything to recommend it over ANSI join syntax.
- iiioooppp 8y ago
  Ah... that makes sense. I have experimented a bit since my original comment and so far have not been able to come up with a syntactically elegant way of implementing LEFT JOIN in an imaginary MySQL that had inner JOIN as the only JOIN. This actually makes me wonder... if SQL only had inner JOIN, what would be the syntactically most elegant way of implementing the other JOINs? The various replies in this comment tree make me suppose that the answer would depend on the precise flavor of SQL being used.

rzzzt 8y ago

Cartesian product could be the default behavior if you select from multiple tables. I'm not sure what the standard says about this one.

CuriousSkeptic 8y ago
It is. On the topic as such, besides ON and WHERE there is also USING and NATURAL. Som very influential db-people have argued that NATURAL should be the only supported join. Checkout the third manifesto. ON as such allows for outer joins, something you can’t do starting with a Cartesian product. But Oracle has syntax for outer joins using WHERE if you prefer that.
- rzzzt 8y ago
  Aah, very good! Outer joins could be achieved with NVL and a suitable default value, if you don't want to do the (+) thing? Edit: you don't get to pair all rows from one table with an all-NULL "fictional" row from the other table in a cartesian product, so the answer is no

barrkel 8y ago

The bit your missing is the awkwardness of expressing outer joins. The way Oracle does it is an existence proof of its awkwardness. The 'FROM' clause defaults to Cartesian product for multiple tables. You can then specify your join conditions in the where clause, but it makes outer joins harder to write - Oracle does them with special (+) operator, but it's clumsy. It doesn't (shouldn't) make much difference at all to the database whether you specify your join condition after the `ON` or in the `WHERE` - if it doesn't hoist `WHERE` conditions into the join clause, and potentially change the join order because of it, it's missing big optimization opportunities.

craigds 8y ago
Can you expand on how oracle does it? I can't imagine any reasonable way to do outer joins in a where clause. For inner joins the grandparent is correct and it should never matter whether you use ON or a where clause.
- barrkel 8y ago
  https://stackoverflow.com/questions/22678643/old-style-oracle-outer-join-syntax-why-locate-the-on-the-right-side-of-the https://stackoverflow.com/questions/22678643/old-style-oracl... Of course it's weird and ugly. I don't think there's anything to recommend it over ANSI join syntax.
iiioooppp 8y ago
Ah... that makes sense. I have experimented a bit since my original comment and so far have not been able to come up with a syntactically elegant way of implementing LEFT JOIN in an imaginary MySQL that had inner JOIN as the only JOIN. This actually makes me wonder... if SQL only had inner JOIN, what would be the syntactically most elegant way of implementing the other JOINs? The various replies in this comment tree make me suppose that the answer would depend on the precise flavor of SQL being used.