On Wed, Dec 29, 2021, at 10:46, Peter Eisentraut wrote:
>In the 1990s, there were some SQL drafts that included syntax like
Do you remember if it was in the beginning/middle/end of the 1990s?
I will start the work of going through all drafts tomorrow.
/Joel
On Wed, Dec 29, 2021, at 16:28, Tom Lane wrote:
>Peter Eisentraut writes:
>> In the 1990s, there were some SQL drafts that included syntax like
>> JOIN ... USING PRIMARY KEY | USING FOREIGN KEY | USING CONSTRAINT ...
>> AFAICT, these ideas just faded away because of other priorities, so if
>>
On Wed, Dec 29, 2021, at 16:28, Tom Lane wrote:
>Peter Eisentraut writes:
>> In the 1990s, there were some SQL drafts that included syntax like
>> JOIN ... USING PRIMARY KEY | USING FOREIGN KEY | USING CONSTRAINT ...
>> AFAICT, these ideas just faded away because of other priorities, so if
>>
Peter Eisentraut writes:
> In the 1990s, there were some SQL drafts that included syntax like
> JOIN ... USING PRIMARY KEY | USING FOREIGN KEY | USING CONSTRAINT ...
> AFAICT, these ideas just faded away because of other priorities, so if
> someone wants to revive it, some work already exists.
On 12/28/21 15:10, Tom Lane wrote:
> Vik Fearing writes:
>> On 12/28/21 8:26 PM, Joel Jacobson wrote:
>>> Can with think of some other suitable reserved keyword?
>> I don't particularly like this whole idea anyway, but if we're going to
>> have it, I would suggest
>> JOIN ... USING KEY ...
On 28.12.21 20:45, Vik Fearing wrote:
I don't particularly like this whole idea anyway, but if we're going to
have it, I would suggest
JOIN ... USING KEY ...
since USING currently requires a parenthesized list, that shouldn't
create any ambiguity.
In the 1990s, there were some SQL
Vik Fearing writes:
> On 12/28/21 8:26 PM, Joel Jacobson wrote:
>> Can with think of some other suitable reserved keyword?
> I don't particularly like this whole idea anyway, but if we're going to
> have it, I would suggest
> JOIN ... USING KEY ...
That would read well, which is nice, but
On 2021-Dec-27, Joel Jacobson wrote:
> >On Mon, Dec 27, 2021, at 15:48, Isaac Morland wrote:
> >I thought the proposal was to give the FK constraint name.
> >However, if the idea now is to allow leaving that out also if there
> >is only one FK, then that's also OK as long as people understand it
> How about JOIN WITH?
I'm -1 on this, reusing WITH is just likely to cause confusion because WITH
can appear other places in a query having an entirely different meaning.
I'd just avoid that from the start.
>> Can with think of some other suitable reserved keyword?
>FOREIGN? Or even spell out
On 12/28/21 8:26 PM, Joel Jacobson wrote:
> On Mon, Dec 27, 2021, at 19:15, Tom Lane wrote:
>> NATURAL JOIN is widely regarded as a foot-gun that the SQL committee
>> should never have invented. Why would we want to create another one?
>>
>> (I suspect that making the constraint name optional
"Joel Jacobson" writes:
> Since my last email in this thread, I've learned KEY is unfortunately not a
> reserved keyword.
> This probably means the proposed "JOIN KEY" would be problematic, since a
> relation could be named KEY.
> Can with think of some other suitable reserved keyword?
On Mon, Dec 27, 2021, at 19:15, Tom Lane wrote:
> NATURAL JOIN is widely regarded as a foot-gun that the SQL committee
> should never have invented. Why would we want to create another one?
>
> (I suspect that making the constraint name optional would be problematic
> for reasons of syntax
Isaac Morland writes:
> On Mon, 27 Dec 2021 at 03:22, Joel Jacobson wrote:
>> However, I see one problem with leaving out the key columns:
>> First, there is only one FK in permission pointing to role, and we write a
>> query leaving out the key columns.
>> Then, another different FK in
>
>
> First, there is only one FK in permission pointing to role, and we write a
> query leaving out the key columns.
> Then, another different FK in permission pointing to role is later added,
> and our old query is suddenly in trouble.
>
>
We already have that problem with cases where two tables
On Mon, Dec 27, 2021, at 17:03, Isaac Morland wrote:
> On Mon, 27 Dec 2021 at 10:20, Joel Jacobson wrote:
>
> Foreign key constraint names have been given the same names as the referenced
> tables.
>
> While I agree this could be a simple approach in many real cases for having
> easy to
On Mon, 27 Dec 2021 at 10:20, Joel Jacobson wrote:
> Foreign key constraint names have been given the same names as the
> referenced tables.
>
While I agree this could be a simple approach in many real cases for having
easy to understand FK constraint names, I wonder if for illustration and
Joel Jacobson schrieb am Mo., 27. Dez. 2021, 16:21:
> >On Mon, Dec 27, 2021, at 15:48, Isaac Morland wrote:
> >I thought the proposal was to give the FK constraint name.
> >However, if the idea now is to allow leaving that out also if there
> >is only one FK, then that's also OK as long as
>On Mon, Dec 27, 2021, at 15:48, Isaac Morland wrote:
>I thought the proposal was to give the FK constraint name.
>However, if the idea now is to allow leaving that out also if there
>is only one FK, then that's also OK as long as people understand it can break
>in the same way NATURAL JOIN can
On Mon, 27 Dec 2021 at 03:22, Joel Jacobson wrote:
> However, I see one problem with leaving out the key columns:
> First, there is only one FK in permission pointing to role, and we write a
> query leaving out the key columns.
> Then, another different FK in permission pointing to role is
On Sun, Dec 26, 2021, at 23:25, Corey Huinker wrote:
> My second guess would be:
> FROM permission p
> LEFT JOIN role AS r ON [FOREIGN] KEY [(p.col1 [, p.col2 ...])]
>
> where the key spec is only required when there are multiple foreign keys in
> permission pointing to role.
>
> But my first
>
>
>
> Perhaps this would be more SQL idiomatic:
>
> FROM permission p
>LEFT JOIN ON KEY role IN p AS r
>LEFT JOIN team_role AS tr ON KEY role TO r
>LEFT JOIN ON KEY team IN tr AS t
>LEFT JOIN user_role AS ur ON KEY role TO r
>LEFT JOIN ON KEY user IN ur AS u
>
>
My second
On Sun, Dec 26, 2021, at 22:38, Joel Jacobson wrote:
> FROM permission p
> LEFT JOIN ON KEY p.role r
> LEFT JOIN team_role tr ON KEY role TO r
> LEFT JOIN ON KEY tr.team t
> LEFT JOIN user_role ur ON KEY role TO r
> LEFT JOIN ON KEY ur.user u
Hm, might be problematic to reuse dot
When you join by id, the join is unique. You can have combinations of
fields, with multiple fields. Is it a maximum fields question.
Isaac Morland schrieb am So., 26. Dez. 2021,
22:37:
> On Sun, 26 Dec 2021 at 16:24, Joel Jacobson wrote:
>
>
>> I think if we combine the ON KEY ... TO ... part
On Sun, Dec 26, 2021, at 22:24, Joel Jacobson wrote:
> FROM permission p
>LEFT JOIN ON KEY p.permission_role_id_fkey r
>LEFT JOIN team_role tr ON KEY team_role_role_id_fkey TO r
>LEFT JOIN ON KEY tr.team_role_team_id_fkey t
>LEFT JOIN user_role ur ON KEY user_role_role_id_fkey TO r
On Sun, 26 Dec 2021 at 16:24, Joel Jacobson wrote:
> I think if we combine the ON KEY ... TO ... part of my idea, with your
> idea, we have a complete neat solution.
>
> Maybe we can make them a little more similar syntax wise though.
>
> Could you accept "ON KEY" instead of "FOREIGN KEY" for
On Sun, Dec 26, 2021, at 21:49, Isaac Morland wrote:
> Right, sorry, that was sloppy of me. I should have noticed that I wrote "tr->
> ... AS tr". But in the case where the "source"
> (referencing) table is already in the join, what's wrong with allowing my
> suggestion? We do need another way
On Sun, Dec 26, 2021, at 19:52, Joel Jacobson wrote:
>LEFT JOIN role r ON KEY p.permission_role_id_fkey
Ops! I see this doesn't quite work.
We're missing one single bit of information.
That is, we need to indicate if the foreign key is
a) in the table we're currently joining
or
b) to some
On Sun, 26 Dec 2021 at 14:37, Joel Jacobson wrote:
> Let's look at each row your example and see if we can work it out.
> I've added the "FROM permission p" and also "AS [table alias]",
> otherwise the aliases you use won't exist.
>
> > FROM permission p
>
> This row is obviously OK. We now
On Sun, Dec 26, 2021, at 20:02, Isaac Morland wrote:
> Is it going too far to omit the table name? I mean, any given foreign key can
> only point to one other table:
That actually how I envisioned this feature to work way back, but it doesn't
work, and I'll try to explain why.
As demonstrated,
On Sun, 26 Dec 2021 at 01:47, Joel Jacobson wrote:
> On Sat, Dec 25, 2021, at 21:55, Joel Jacobson wrote:
> > FROM permission p
> > LEFT JOIN role r WITH p->permission_role_id_fkey = r
> > LEFT JOIN team_role tr WITH tr->team_role_role_id_fkey = r
> > LEFT JOIN team t WITH
On Sun, Dec 26, 2021, at 19:54, Sascha Kuhl wrote:
> Could you make
>
> JOIN key ?
Not sure what you mean.
Perhaps you can explain by rewriting the normal query below according to your
idea?
SELECT *
FROM permission p
LEFT JOIN role r ON p.role_id = r.id
Given the foreign key on "permission"
Could you make
JOIN key ?
Joel Jacobson schrieb am So., 26. Dez. 2021, 19:52:
> On Sun, Dec 26, 2021, at 19:33, Sascha Kuhl wrote:
> > The Syntax is great. Which language does it come from. I consider it not
> german. But I understand it mathematically.
> > Great extension.
>
> It doesn't come
On Sun, Dec 26, 2021, at 19:33, Sascha Kuhl wrote:
> The Syntax is great. Which language does it come from. I consider it not
> german. But I understand it mathematically.
> Great extension.
It doesn't come from any language. But I've seen similar features in ORMs, such
as the jOOQ Java
On Sat, Dec 25, 2021, at 21:55, Joel Jacobson wrote:
> FROM permission p
> LEFT JOIN role r WITH p->permission_role_id_fkey = r
> LEFT JOIN team_role tr WITH tr->team_role_role_id_fkey = r
> LEFT JOIN team t WITH tr->team_role_team_id_fkey = t
> LEFT JOIN user_role ur WITH
On Sat, Dec 25, 2021, at 22:06, David G. Johnston wrote:
> On Saturday, December 25, 2021, Joel Jacobson wrote:
> * Would require changes to the SQL standard, i.e. SQL committee work
>
> Huh?
I mean, one could argue this is perhaps even the wrong forum to discuss this
idea,
since it's a
On Saturday, December 25, 2021, Joel Jacobson wrote:
>
> I've revisited the idea to somehow use foreign keys to do joins,
>
>
-1
> This is somewhat addressed by the USING join form, but USING has other
> drawbacks, why I tend to avoid it except for one-off queries.
>
I find this sufficient.
Hi,
I've revisited the idea to somehow use foreign keys to do joins,
in the special but common case when joining on columns that exactly match a
foreign key.
The idea is to add a new ternary operator, which would be allowed only in the
FROM clause.
It would take three operands:
1)
37 matches
Mail list logo
