PRQL / prql

PRQL is a modern language for transforming data — a simple, powerful, pipelined SQL replacement
https://prql-lang.org
Apache License 2.0
9.92k stars 217 forks source link

Natural Joins #723

Open max-sixty opened 2 years ago

max-sixty commented 2 years ago

Another idea on the Joins: https://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqljnaturaljoin.html

A NATURAL JOIN is a JOIN operation that creates an implicit join clause for you based on the common columns in the two tables being joined. Common columns are columns that have the same name in both tables.

aljazerzen commented 2 years ago

Related discussion: https://news.ycombinator.com/item?id=31898312

The concern exposed was that if a database schema is changed this also changes the query, without throwing errors or warnings.

Another alternative proposal was "join paths":

    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

We are planning to have some kind of schema definition as a part of the language, so this is not so far off. But I not quite sure on what exactly is the problem we are trying to solve here.

snth commented 2 years ago

Just to point out that if the database schema is changed, then potentially any query with a SELECT * might change so even something simple like:

from tbl

There were a lot of good points in that thread, like avoiding ambiguity, allowing you to specify not to use FKs when required, etc ...

I still want to go over it again but the one that stood out to me was using the FK name.