-1

I have a confusion. Suppose there two relation with common attribite A. Now is (R natural join S)=(R join S where join condition A=A)? Natural join returns a common column A Do simple join return two columns with same name AA or 1 common column A due to relational algebra which is defined in set theory ??

Constantin Groß
  • 10,719
  • 4
  • 24
  • 50
simranjit singh
  • 19
  • 4
  • 1
    It depends on the definition of the Relational Algebra. In some books the (normal) join requires that the attributes of the two relations have different names. So if you want to join a table with itself, first you have to rename all the attributes in one of the two operands. – Renzo Jun 05 '18 at 19:50
  • There s no "simple join". Give a reference to and/or definition of the "relational algebra" you are talking about. Because there is more than one & they differ even in what a relation is. Explain what you mean by "simple join". SQL inner join is a combination of a sql's particular kind of product ("cross join") & a restriction/selection ("on"). Also: What does "AA" mean? What does "return AA or A" mean? Use enough words, phrases & sentences to clearly say what you mean. PS Set theory is one thing & RA is another. The definitions of RA can be given in terms of set arithmetic, that's all. – philipxy Jun 06 '18 at 00:54
  • NOT sql. I am talking about relational algebra. and i have edited the question. @philipxy is this clear enough. – simranjit singh Jun 06 '18 at 04:05
  • Thank you @Renzo – simranjit singh Jun 06 '18 at 04:06
  • You haven't addressed anything in my comment. Your edit involved "AA" but what is that? Tell us your textbook & quote the definition of natural join & quote the defition of whatever other join you mean & explain what you think each side of the equality looks like based on the definitions & tell us where you are stuck. – philipxy Jun 06 '18 at 14:45
  • Possible duplicate of [Difference between a theta join, equijoin and natural join](https://stackoverflow.com/q/7870155/3404097) – philipxy Jun 06 '18 at 19:40
  • @ConstantinGroß How about the 3 spelling mistakes & 2 punctuation mistakes in the body & the title not really making sense? – philipxy Oct 26 '19 at 09:40
  • @philipxy I was only focusing on the title, because that's what brings people in via search engines and it's the first thing you see of a question. Or is there any rule about having to clean up the whole question thoroughly when editing? (genuine question, not trying to be snappish). Normally I fix other mistakes too, when I see them, but I think this question requires some major rephrasing and I'm not sure I even understand its current phrasing good enough to be able to help with this. – Constantin Groß Oct 26 '19 at 09:47
  • @ConstantinGroß The policy is, edit as much as possible. Editing bumps a post to the front page to more eyes. (Hence I saw this.) For low-rep people whose edits are reviewed edits should be worth bothering 3 reviewers over. (You can google meta.stackexchange questions re edits.) But I get the idea of a just-title or just-tag edit. Usually one can improve a title. ("difference between" essentially just means "explain these multiple things I didn't look up or explore".) Though I get that this post might be too unclear for you to. I'm not interested in cleaning this up until the asker does. – philipxy Oct 26 '19 at 10:12
  • @philipxy thanks for the clarification, I'll keep that in mind for the future! – Constantin Groß Oct 26 '19 at 10:16

1 Answers1

1

There's an example of a Natural Join here. As @Renzo says, there are many variants. And SQL is different again. So I'll keep to what wikipedia shows.

Most important: the join condition applies to all attributes in common between the two arguments. So you need to say "two relations with A being their only common attribute". The only common attribute is DeptName in that wikipedia example. There can be many common attributes, in general.

Yes, joining means forming tuples in the result by pairing tuples from the argument that have same values in the corresponding common attributes. So you have same value with same attribute name. It would be pointless repeating both attributes in the result, because you'd be repeating the values. The example shows there's a single attribute DeptName in the result.

Beware that different dialects of Relational Algebra use different symbols and notations. So the bare bowtie (⋈) for Natural Join can be suffixed with a boolean condition, making a theta-join (θ-join) or equi-join -- see that example. The boolean condition is between differently-named attributes, and might use any comparison operator. So both attribute names and their values appear in the result.

Set theory operations apply because each tuple is a set of name-value pairs. The result tuples are the union of a tuple from each argument -- providing that union is a valid tuple. That is, providing same-named n-v pairs have same value.

AntC
  • 2,623
  • 1
  • 13
  • 20
  • There's not much point sending askers to RA definitions instead of asking for theirs, because there are very different variants of the RA. Eg the link you gave gives an uncommon definition of theta-join shoehorned into an RA with headings as sets of attributes & natural join as fundamental join, but theta-join belongs in RAs with headings as lists of attributes & product as fundamental join. Eg the link you give does *not* have "both attribute names and their values appear in the result", it says "θ-join is defined only if the headers of S and R are disjoint". – philipxy Jun 06 '18 at 15:03