from a
join b
It would require knowledge of the schema. I don't know if this is possible in PRQL, or if the transpilation to SQL has to be stateless.
It’s annoying adding another foreign key later and then having previously working queries fail at runtime due to an ambiguous join condition.
SQL addresses this via the natural join keyword `using`, where you enumerate the common columns between the two tables being joined. It isn't too convenient for your example unless your pk naming pattern happens to be `<entity>_id` instead of just `id` (note: this naming pattern has all sorts of other adverse consequences though). But it does provide convenience in some cases without introducing backwards compatibility risks as the schema evolves.
from a
join b on b.a_id = a.a_id
An unreasonable way to achieve that is to put the table name in every column. A more palatable way is to write some clever functions in your schema to scan the information table look for column name clashes (you essentially write a tiny "linter" inside your schema).
Select * from a join b using (a_id)
from a
join b [a_id]
Of course, if you allow naming the relation when you create a foreign key, then you could use the source table-qualified relationship name for joins rather than the target table name, which would be unambiguous (and more communicative of intent).
E.g., for a hypothetical table with two self-fks:
FROM employees ee
INNER JOIN ee.manager mgr
INNER JOIN ee.team_lead leadCompilation does have to be stateless (for performance reasons), but we are planning to add some kind of schema definitions which could also specify foreign keys.
So joins without conditions would be possible, we'll look into it!
What do you think should happen if there are multiple foreign keys connecting the two tables? Should this also work for many-to-many relations with an intermediate table?
If it's not ambiguous, then let me do it. If I rely on ambiguity then throw an exception. In the case of multiple foreign keys, throw an exception, as there's no way to know which one I mean. It'd be nice if I could disambiguate the situation though. Normal SQL allows the `on` clause.
from TableA
inner join TableB on <expression>
from TableA
inner join TableB by ConstraintC
The same goes for the many to many relationship with an intermediate table. It could look something like this...
from TableA
inner join TableB through TableC
As applications grow, and initially simple lookup table semantics get more nuanced, it might be nice to be able to constrain the join on the lookup table like this...
from TableA
inner join TableB through TableC where <expression>
This is where many conveniences that use implicit data run into problems. A small convenience now for the possibility of accidentally breaking because of mostly unrelated changes later is a poor trade off for anyone that wants to have stable and consistent software.
This is likely one of those cases where you're better off with tooling to help make writing the correct unambiguous code easier (or automated away) than introducing a feature which leads to less stable systems in some cases.
Edit: Along the lines of what you note at the end, I would rather see joins able to use named relations as defined in the schema. Of there's a relation from table movie to table actor specifically names roles in the schema, I would rather be able to join movie on roles and have actors joined correctly using that relation, and aliases to roles which I could then use. Then you're using features that are designed and stable and not implicit and subject to changing how or whether they function based on semi-unrelated changes.
That might look like: "from movie relate roles" which is equivalent to "from movie join actor roles on movie.id = roles.movie_id", but because actor.movie_id has a constraint in the schema named roles which restricts it to a movie.id already.
I don't have a specific syntax in mind yet; for illustrative purposes:
defjoin r,m,a = %prejoin_roles() -> { # define a common join path between three relations r,m,a:
from r=ROLES # can hard-code table names or use parameters (which may refer to other parameters)
join m=MOVIES [r.movie_id = m.movie_id]
join a=ACTORS [r.actor_id = a.actor_id]
}
from r,m,a = %prejoin_roles()
select m.title, a.character_name
create table users (id int primary key, name text);
create table things (id int primary key, creator int references users);
from things select [id, creator.name];Compilation should fail and require you to explicitly specify what key to use. Please don’t do anything magic.
Probably the biggest constraint SQL language design has is that its on a live system — things are not compiled at the same time.
select * from a natural join b
(not based on fk constraints though, it will join on all attributes with the same name in the relations)
But I think that's a shortcoming of the client tool, rather than the language.
If SQL tools auto completed the join conditions as best as they could it would probably be a great help.