5 ms·

This isn't just a matter of some constant programmer overhead, like SQL queries taking 20% longer to write. 20% longer to write than what alternative? And how

This isn't just a matter of some constant programmer overhead, like SQL queries taking 20% longer to write. 20% longer to write than what alternative? And how is this being measured? And.. am I missing something? By far the most common case for joins is following foreign keys. SQL has no special syntax for this: select foo.id, quux.value from foo, bar, quux where foo.bar_id = bar.id and bar.quux_id = quux.id Why can't this be expressed as an INNER JOIN? And can't some of these subqueries be written using a WHERE EXISTS or a windowing function?

erezsh 5y ago
> Why can't this be expressed as an INNER JOIN? `from foo, bar, quux` is an inner join, it's a shorthand syntax. He's lamenting that he has to keep specifying and matching ids, when the database can figure it out on its own from the foreign keys.
- pdkl95 5y ago
  They should use NATURAL JOIN if auto-selection of the join keys is that important. I wouldn't recommend relying on that type of automagic behavior because it is very brittle; adding a column to a table might accidentally break existing queries.
  - kinjba11 5y ago
    A natural join selects all matching names which is not the same as what the article is saying. The database already knows about foreign keys. Why do I have to say select * from A a join B b on b.Id = A.OtherId SQL IDEs will auto-suggest that "on b.Id = A.OtherId" because it's the foreign key and could be inferred. That's what you need 99% of the time.
    - cerved 5y ago
      Partly for persistence, I would imagine. The query you write today should function the same tomorrow and a year from now. If you want this implicit behavior, you may use natural joins
    - pdkl95 5y ago
      > A natural join selects all matching names Yes, which is why I called it "brittle". It can easily break if you aren't careful with column names. However, if someone really wanted (unwisely, in my opinion) to have the DB automatically handle their inner joins, they could use NATURAL JOIN. This would require being very careful with you column names, which is why I (strongly) recommend doing it the usual way with INNER JOIN. > the foreign key and could be inferred Only in simple cases. If there are multiple foreign keys referencing the same table, automagically inferring joins will probably do the wrong thing, just like NATURAL JOIN. > IDEs will auto-suggest The editor/IDE is where this type of automagic inference should be done! You have the opportunity to inspect the suggestion and fix it if necessary, instead of hoping the DB does the right thing at runtime.
  - erezsh 5y ago
    > adding a column to a table might accidentally break existing queries Just a column, no. Adding a new foreign key might, but that's something that a type-checker/compiler can let you know about. > They should Use NATURAL JOIN NATURAL JOINs are better than nothing, but they're flawed too. Column names are supposed to convey meaning or intention, not just the type of data that they contain.
  - faho 5y ago
    Yes, but that's only automagic and brittle because it's not an actual join on the foreign key. It's a join on whatever happens to have the same name! Which sometimes happens to be the foreign key. The point here is that SQL doesn't have a way of specifying "join on whatever the foreign key is, and nothing else".
- initplus 5y ago
  Depending on your database, it may be worse than just shorthand syntax. I encountered once a DB (that shall remain nameless) where replacing this syntax with the actual JOIN keyword resulted in dramatically better query plans everywhere it was used.
progre 5y ago
While some of their complaints are legit I think most of the SQL in this article are straw men. Most of it can be made more readable and more performant.
- kinjba11 5y ago
  Care to back up those claim with evidence? The SQL queries are toy examples. Experienced SQL users don't write SQL like in the article because they know SQL's pitfalls and avoid them. It doesn't mean SQL isn't full of pitfalls and bad decisions (like many very old programming languages still in use).
  - progre 5y ago
    Evidence? This is just, like, my opinion, man. But nested subqueries and old style joins... ripe for rewrite if I had say.
gwd 5y ago
For SQLite I use 'natural join': select foo_id, quux.value from foo natural join bar natural join quux Unfortunately this doesn't actually use the foreign key relation; it matches on the same element name. So you have to have `foo.bar_id` and `bar.bar_id`, as well as `bar.quux_id` and `quux.quux_id`. But I find that actually makes queries more readable.
- euroderf 5y ago
  I find that the use of this technique, plus banning NULLs (handle them in application code), promotes user sanity. My 0,02€.

erezsh 5y ago

> Why can't this be expressed as an INNER JOIN? `from foo, bar, quux` is an inner join, it's a shorthand syntax. He's lamenting that he has to keep specifying and matching ids, when the database can figure it out on its own from the foreign keys.

pdkl95 5y ago
They should use NATURAL JOIN if auto-selection of the join keys is that important. I wouldn't recommend relying on that type of automagic behavior because it is very brittle; adding a column to a table might accidentally break existing queries.
- kinjba11 5y ago
  A natural join selects all matching names which is not the same as what the article is saying. The database already knows about foreign keys. Why do I have to say select * from A a join B b on b.Id = A.OtherId SQL IDEs will auto-suggest that "on b.Id = A.OtherId" because it's the foreign key and could be inferred. That's what you need 99% of the time.
  - cerved 5y ago
    Partly for persistence, I would imagine. The query you write today should function the same tomorrow and a year from now. If you want this implicit behavior, you may use natural joins
  - pdkl95 5y ago
    > A natural join selects all matching names Yes, which is why I called it "brittle". It can easily break if you aren't careful with column names. However, if someone really wanted (unwisely, in my opinion) to have the DB automatically handle their inner joins, they could use NATURAL JOIN. This would require being very careful with you column names, which is why I (strongly) recommend doing it the usual way with INNER JOIN. > the foreign key and could be inferred Only in simple cases. If there are multiple foreign keys referencing the same table, automagically inferring joins will probably do the wrong thing, just like NATURAL JOIN. > IDEs will auto-suggest The editor/IDE is where this type of automagic inference should be done! You have the opportunity to inspect the suggestion and fix it if necessary, instead of hoping the DB does the right thing at runtime.
- erezsh 5y ago
  > adding a column to a table might accidentally break existing queries Just a column, no. Adding a new foreign key might, but that's something that a type-checker/compiler can let you know about. > They should Use NATURAL JOIN NATURAL JOINs are better than nothing, but they're flawed too. Column names are supposed to convey meaning or intention, not just the type of data that they contain.
- faho 5y ago
  Yes, but that's only automagic and brittle because it's not an actual join on the foreign key. It's a join on whatever happens to have the same name! Which sometimes happens to be the foreign key. The point here is that SQL doesn't have a way of specifying "join on whatever the foreign key is, and nothing else".
initplus 5y ago
Depending on your database, it may be worse than just shorthand syntax. I encountered once a DB (that shall remain nameless) where replacing this syntax with the actual JOIN keyword resulted in dramatically better query plans everywhere it was used.

progre 5y ago

While some of their complaints are legit I think most of the SQL in this article are straw men. Most of it can be made more readable and more performant.

kinjba11 5y ago
Care to back up those claim with evidence? The SQL queries are toy examples. Experienced SQL users don't write SQL like in the article because they know SQL's pitfalls and avoid them. It doesn't mean SQL isn't full of pitfalls and bad decisions (like many very old programming languages still in use).
- progre 5y ago
  Evidence? This is just, like, my opinion, man. But nested subqueries and old style joins... ripe for rewrite if I had say.

gwd 5y ago

For SQLite I use 'natural join': select foo_id, quux.value from foo natural join bar natural join quux Unfortunately this doesn't actually use the foreign key relation; it matches on the same element name. So you have to have `foo.bar_id` and `bar.bar_id`, as well as `bar.quux_id` and `quux.quux_id`. But I find that actually makes queries more readable.

euroderf 5y ago
I find that the use of this technique, plus banning NULLs (handle them in application code), promotes user sanity. My 0,02€.