51–60 of 354 posts
> By far the most common case for joins is following foreign keys. SQL has no special syntax for this You can use NATURAL JOIN select * from foo natural join bar Works as long as the keys are named the same. However, a lot of people have a habit of naming keys differently in the two tables.
This makes naming key columns differently a defence technique, so you stop people from using natural joins.
Earlier quoted context omitted.
Comparing SQL to those other languages doesn't really make sense. Their purpose is different. For what SQL does, the syntax makes a lot of sense because it is a completely different paradigm. I think it's dismissive to refer to SQL as merely a 70s experiment. It is used so widely today still
What advantages do you think SELECT a, b, c FROM d has over even a trivial modernisation like, say, table(d) |> select(a, b, c) ?
Earlier quoted context omitted.
> 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.
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.
select * from A a join B b on b.Id = A.OtherId
As someone who only uses SQL a couple of times a year, I feel that SQL shares the same fate as everything in IT: invented almost 50 years ago, not with today's world in mind, it has been blown up somewhat. Reminds me a bit of JavaScript: everything that can be done in JavaScript, will be done in JavaScript.
Like after C followed C++ and here Java and others there will be new DSL and techniques on top of SQL.
The article has its merits. Better abstractions for different use cases.
It would be really cool if databases had an Option type. Then you could remove all the NULLs. Although you can mark a column as NOT NULL, that restriction doesn't "travel": it isn't present for function inputs/outputs, subquery results, etc. Adding it to the type system gives you a lot more mileage. And then joins could be option-aware: an inner join would have outputs matching the input types, but an outer join woul…
A nullable T column _is_ an Option column, with NULL representing "Empty" or "No Value".
Earlier quoted context omitted.
> 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.
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.
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.
SQL was not made for programmers alone. It has been invented also for not so technical people so that verboseness and overhead is part of the deal. > When Ray and I were designing Sequel in 1974, we thought that the predominant use of the language would be for ad-hoc queries by planners and other professionals whose domain of expertise was not primarily data- base management. We wanted the language to be simple enoug…
Earlier quoted context omitted.
> 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.
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.
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".
Earlier quoted context omitted.
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.
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.