3 ms·

The syntax for joins is unnecessarily complex. The difference in behaviour is determined by the operator used for comparison between the two tables. If there ex

The syntax for joins is unnecessarily complex. The difference in behaviour is determined by the operator used for comparison between the two tables. If there existed modifiers for the operators to be sensitive to nulls, no need for special syntax would be needed. If we define /<op> to make the operator include nulls on the left side and <op>\ to include nulls on the right side, we can do joins of all kinds without the JOIN ON syntax. Example. inner join select * from a,b where a.id=b.id left join select * from a,b where a.id=\b.id right join select * from a,b where a.id/=b.id outer join select * from a,b where a.id/=\b.id

jontro 8y ago
How would you solve a left join like this with that kind of syntax? LEFT JOIN ( inner join table2 ON table2.ID = table1.table2id and table2.othertype = 1234 ) on table1.outerid = outertable.Id
- Svip 8y ago
  The subquery would be in the WHERE clause, and it would be called for each row, thus making it N times slower than the JOIN syntax. There is a reason why this style has been abandoned.
  - kijin 8y ago
    Modern RDBMS's are smart enough to figure out when the result of a subquery doesn't depend on rows outside of it. Then the subquery is run only once, and its result is treated as a temporary table to be JOINed.
    - Svip 8y ago
      For most simple queries, that is correct. But if the query gets complicated, and/or the tables involved have several indexes, where several might be useful, then it might make the wrong decision. I've written plenty of complicated queries, where I have to help the query optimiser along. Although, the most modern I've had this experience with is SQL Server 2014, but that definitely still needed guiding a few times.
- snidane 8y ago
  How about select * from outertable, (select *, outerid from table1,table2 where table1.table2id = table2.ID and table2.othertype = 1234) t12 where outertable.Id =\ t12.outerid
Svip 8y ago
That's the old join syntax before ANSI SQL was designed. Except your operators look like "* =" and "= *" (without whitespace of course, blame HN's markdown parser). Sybase supported this syntax well into its version 15.x (maybe still in version 16?). And Sybase did not (does not?) support full outer joins. It may seem simple on the surface, but once you get into complex queries, it's a horrible syntax, and it's easy to forget the relations between the tables, particularly with several tables (4+) involved. As someone who has converted an old and large SQL code base from non-ANSI JOINs to ANSI JOINs, I am happy to see this style of syntax abandoned.
- noisy_boy 8y ago
  IIRC, Oracle also supports this "* =" and "= *" syntax. I started with that and then learned ANSI JOINs; was difficult initially because I was used to the former syntax but I would agree that ANSI JOINs are more clearer/cleaner. That and the "with" clause makes queries so much more readable.
- arafa 8y ago
  Can't agree more, having been in a very similar position. From what I understand (based on the book SQL for Smarties), it's also more efficient for the query planner in some cases (especially when filter/non-join predicates are added to the JOIN).
codeulike 8y ago
But sometimes you want to outer join on the ID matching and some other condition, and thats where the JOIN syntax gets more useful, because the 'outer' then obviously applies to a whole series of logic statements that are and/or'd together. If you write reports its not long before you start hitting really obscure joins and conditions and subqueries and so on.
3pt14159 8y ago
Eh—I would have designed SQL differently if I were in charge (especially for casts, function definitions, and string munging) but leaving that aside, I disagree that the JOIN ON syntax is unnecessarily complex. We do not write programming languages solely for computers. We write them for humans too. Humans make mistakes and come at codebases at all manners of experience, both in breadth and depth. For example, it may literally be someone's first time looking at SQL when they're confronted with the following: SELECT * FROM classrooms, enrolments WHERE classrooms.id=\classroom_id; It is inherently less clear than the following: SELECT * FROM classrooms LEFT JOIN enrolments ON classrooms.id = classroom_id; It's easier to figure out bugs too, because it leaves more room to understand what the person intended to do rather than what they actually did. For example, say the classrooms table had, due to legacy issues, a nullable id field, but we didn't want to include those classrooms. Someone may have inadvertently typed: SELECT * FROM classrooms LEFT JOIN enrolments ON classrooms.id = classroom_id WHERE classroom_id IS NOT NULL; // ^ should have been classrooms.id Because the NOT NULL criteria is in the where clause, it's clear it's not supposed to impact the join. It's supposed to be part of the filter. I know it's the same thing practically, but they're conceptually different. Further, as you get into more complex queries (unions, recursive, cross joins, aggregations) stuffing everything into one hairy WHERE statement gets impossible to understand because it does everything all at once.

jontro 8y ago

How would you solve a left join like this with that kind of syntax? LEFT JOIN ( inner join table2 ON table2.ID = table1.table2id and table2.othertype = 1234 ) on table1.outerid = outertable.Id

Svip 8y ago
The subquery would be in the WHERE clause, and it would be called for each row, thus making it N times slower than the JOIN syntax. There is a reason why this style has been abandoned.
- kijin 8y ago
  Modern RDBMS's are smart enough to figure out when the result of a subquery doesn't depend on rows outside of it. Then the subquery is run only once, and its result is treated as a temporary table to be JOINed.
  - Svip 8y ago
    For most simple queries, that is correct. But if the query gets complicated, and/or the tables involved have several indexes, where several might be useful, then it might make the wrong decision. I've written plenty of complicated queries, where I have to help the query optimiser along. Although, the most modern I've had this experience with is SQL Server 2014, but that definitely still needed guiding a few times.
snidane 8y ago
How about select * from outertable, (select *, outerid from table1,table2 where table1.table2id = table2.ID and table2.othertype = 1234) t12 where outertable.Id =\ t12.outerid

Svip 8y ago

That's the old join syntax before ANSI SQL was designed. Except your operators look like "* =" and "= *" (without whitespace of course, blame HN's markdown parser). Sybase supported this syntax well into its version 15.x (maybe still in version 16?). And Sybase did not (does not?) support full outer joins. It may seem simple on the surface, but once you get into complex queries, it's a horrible syntax, and it's easy to forget the relations between the tables, particularly with several tables (4+) involved. As someone who has converted an old and large SQL code base from non-ANSI JOINs to ANSI JOINs, I am happy to see this style of syntax abandoned.

noisy_boy 8y ago
IIRC, Oracle also supports this "* =" and "= *" syntax. I started with that and then learned ANSI JOINs; was difficult initially because I was used to the former syntax but I would agree that ANSI JOINs are more clearer/cleaner. That and the "with" clause makes queries so much more readable.
arafa 8y ago
Can't agree more, having been in a very similar position. From what I understand (based on the book SQL for Smarties), it's also more efficient for the query planner in some cases (especially when filter/non-join predicates are added to the JOIN).

codeulike 8y ago

But sometimes you want to outer join on the ID matching and some other condition, and thats where the JOIN syntax gets more useful, because the 'outer' then obviously applies to a whole series of logic statements that are and/or'd together. If you write reports its not long before you start hitting really obscure joins and conditions and subqueries and so on.

3pt14159 8y ago

Eh—I would have designed SQL differently if I were in charge (especially for casts, function definitions, and string munging) but leaving that aside, I disagree that the JOIN ON syntax is unnecessarily complex. We do not write programming languages solely for computers. We write them for humans too. Humans make mistakes and come at codebases at all manners of experience, both in breadth and depth. For example, it may literally be someone's first time looking at SQL when they're confronted with the following: SELECT * FROM classrooms, enrolments WHERE classrooms.id=\classroom_id; It is inherently less clear than the following: SELECT * FROM classrooms LEFT JOIN enrolments ON classrooms.id = classroom_id; It's easier to figure out bugs too, because it leaves more room to understand what the person intended to do rather than what they actually did. For example, say the classrooms table had, due to legacy issues, a nullable id field, but we didn't want to include those classrooms. Someone may have inadvertently typed: SELECT * FROM classrooms LEFT JOIN enrolments ON classrooms.id = classroom_id WHERE classroom_id IS NOT NULL; // ^ should have been classrooms.id Because the NOT NULL criteria is in the where clause, it's clear it's not supposed to impact the join. It's supposed to be part of the filter. I know it's the same thing practically, but they're conceptually different. Further, as you get into more complex queries (unions, recursive, cross joins, aggregations) stuffing everything into one hairy WHERE statement gets impossible to understand because it does everything all at once.