"It seems to me SQL is, paradoxically, too low level, much like assembly language, in the sense that it makes you specify the joins. Over and over and over again. Why doesn't it infer them from the structure of the database (the relations between tables)? If the answer is performance, well, that means we are in agreement, and SQL is not high-level."
First of all, it absolutely can and does infer joins if you use a natural join.[1]
However, a lot of the time you don't want the database to infer the joins because how you join two tables depends a lot on the specifics of your query. Not every join is along a clean primary key/foreign key axis. Sometimes you have datasets from different sources. Othertimes, you have more novel relationships between your tables. The whole point of the relational model is to give the end user a lot of flexibility in querying the database which means you need to specify the joins a lot of the time.
> However, a lot of the time you don't want the database to infer the joins because how you join two tables depends a lot on the specifics of your query.
Some 90%+ of the times, you want exactly the join the database would infer, but natural join is basically useless, so you must repeat the basic join all the way every time.
SQL did it backwards. The simple join keyword should mean natural join over a foreign key. A cross product should be created with the `product` keyword or something similar.
First of all, it absolutely can and does infer joins if you use a natural join.[1]
However, a lot of the time you don't want the database to infer the joins because how you join two tables depends a lot on the specifics of your query. Not every join is along a clean primary key/foreign key axis. Sometimes you have datasets from different sources. Othertimes, you have more novel relationships between your tables. The whole point of the relational model is to give the end user a lot of flexibility in querying the database which means you need to specify the joins a lot of the time.
1. https://docs.oracle.com/javadb/10.8.3.0/ref/rrefsqljnaturalj...