Another kind of join?
in [DB2]
|
From: Lennart on 7 Sep 2009 12:41 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
From: Troels Arvin on 7 Sep 2009 16:24 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/474591/which-are-the-sql-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
From: Serge Rielau on 7 Sep 2009 19:49 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
From: Lennart on 8 Sep 2009 06:41 On Sep 7, 10:24 pm, Troels Arvin <tro...(a)arvin.dk> 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/474591/which-are-the-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
From: Lennart on 8 Sep 2009 06:43
On Sep 8, 1:49 am, Serge Rielau <srie...(a)ca.ibm.com> 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 |