Another kind of join?

I'm thinking about a join that uses foreign keys to determine the on
clause (contrary to the natural join which matches column names),
let's call it relational join in lack of a better word. Does anyone
know if such thing has been discussed in the sql community? In that
case, any references? For example:

1) What would A relational join B mean if there is no foreign key
between A and B? Cross join or empty set?
2) What about transitive dependencies, i.e. If A -> B -> C, what would
A relational join C mean?
etc

Any thoughts anyone?

/Lennart

Lennart wrote:
> I'm thinking about a join that uses foreign keys to determine the on
> clause (contrary to the natural join which matches column names), let's
> call it relational join in lack of a better word.

I agree - I've often thought that this would be great. Another naming
suggestion: "referential join"?

We are not the only ones wanting that. See, for example:
http://stackoverflow.com/questions/4...-improvements-
you-are-waiting-for


> 1) What would A relational join B mean if there is no foreign key
> between A and B?

My suggestion: Throw an error.


> If A -> B -> C, what would A relational join C mean?

It would just be syntactic sugar of A refjoined to B, refjoined to C.

--
Troels

For typed tables DB2 supports that exact syntax for "reference columns".
At the time we built this we also prototyped "->" to follow RI
relationships in regular tables.
The fun things one does in research... ;-)


--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

On Sep 7, 10:24*pm, Troels Arvin wrote:
> Lennart wrote:
> > I'm thinking about a join that uses foreign keys to determine the on
> > clause (contrary to the natural join which matches column names), let's
> > call it relational join in lack of a better word.
>
> I agree - I've often thought that this would be great. Another naming
> suggestion: "referential join"?

Yes, that is probably a better name.

>
> We are not the only ones wanting that. See, for example:http://stackoverflow.com/questions/4...-sql-improveme...
> you-are-waiting-for
>

Thanks for the link, there are a couple of other suggestions on the
page that I like as well (and of course, some suggestions that I don't
like at all ;-).

> > 1) What would A relational join B mean if there is no foreign key
> > between A and B?
>
> My suggestion: Throw an error.

Mm, that makes sense.

>
> > If A -> B -> C, what would A relational join C mean?
>
> It would just be syntactic sugar of A refjoined to B, refjoined to C.
>

Yes. I havent really thought it over, but I think B should be hidden
in the rest of the query. I.e. it would behave as if it where defined
in an anonymous view.


/Lennart

On Sep 8, 1:49*am, Serge Rielau wrote:
> For typed tables DB2 supports that exact syntax for "reference columns".

Do you know if it ever has been discussed for normal tables?


/Lennart

Lennart wrote:
> On Sep 8, 1:49 am, Serge Rielau wrote:
>> For typed tables DB2 supports that exact syntax for "reference columns".
>
> Do you know if it ever has been discussed for normal tables?
Discussed and prototyped. But never brought forward to ANSI.
This was 13 years ago.
I was a student then working on OO in Datajoiner :-)

Cheers
Serge
--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab

On 8 Sep, 13:11, Serge Rielau wrote:
> Lennart wrote:
> > On Sep 8, 1:49 am, Serge Rielau wrote:
> >> For typed tables DB2 supports that exact syntax for "reference columns".
>
> > Do you know if it ever has been discussed for normal tables?
>
> Discussed and prototyped. But never brought forward to ANSI.

A real pity IMO. It would remove a lot of clutter from the average sql
query, and improve both readability and writability.

> This was 13 years ago.
> I was a student then working on OO in Datajoiner :-)
>

You have come a long way since ;-)


/Lennart