3 ms·
> Too bad they were not invented until SQL-89, and could thus not be considered when the JOIN syntax we all know was designed and formalized by ANSI in SQL-86.
by steve-chavez 6y ago
> Too bad they were not invented until SQL-89, and could thus not be considered when the JOIN syntax we all know was designed and formalized by ANSI in SQL-86.
TIL!
> After renaming the foreign keys
The idea about using FK as a JOIN target is interesting. While developing a syntax for PostgREST resource embedding[1], I also reached the conclusion that FKs would be a convenient way to join tables(also suggested renaming them as you do here).
IIRC, self joins are still an issue with FK joining.
[1]: https://postgrest.org/en/v7.0.0/api.html#embedding-disambiguation https://postgrest.org/en/v7.0.0/api.html#embedding-disambigu...
- JoelJacobson 6y agoHi Steve, Many thanks for making PostgREST, I'm using it for all my projects! I'm trying to understand the problem you see with self joins. Why would it be a problem? At the end of my comment, I gave an example with a users table, for such a table, an expression such as "FROM users JOIN users->parent" would join-in users two times, but two different rows of users, since "users->parent" would follow the foreign key "parent".