23 votes 23 votes Given two union compatible relations $R_1(A, B)$ and $R_2 (C, D)$, what is the result of the operation $R_1 \Join_{ A = C \wedge B = D} R_2$? $R_1 \cup R_2$ $R_1 \times R_2$ $R_1 – R_2$ $R_1 \cap R_2$ Databases gate1998 normal relational-algebra + – Kathleen asked Sep 25, 2014 • edited Feb 5, 2018 by go_editor Kathleen 7.2k views answer comment Share Follow See 1 comment See all 1 1 comment reply dhingrak commented Jan 19, 2015 reply Follow Share Please explain 0 votes 0 votes Please log in or register to add a comment.
Best answer 41 votes 41 votes This question is an example of Theta Join, $r \bowtie_\theta s= \sigma_\theta(r \times s)$ The join here will be selecting only those tuples where $A = C$ and $B = D,$ meaning it is the intersection. D option. Arjun answered Jan 23, 2016 • edited Oct 22, 2017 Arjun comment Share Follow See all 16 Comments See all 16 16 Comments reply daksirp commented Aug 17, 2018 reply Follow Share so, what will be the attributes of resultant table : (A B) or (C D) ?? 0 votes 0 votes Verma Ashish commented Aug 17, 2018 reply Follow Share in natural join common attributes are removed. Here all attributes are present only those tuples where A = C and B = D 3 votes 3 votes daksirp commented Aug 17, 2018 reply Follow Share didnt get you exactly, are you saying " all attributes will be present in resultant table " i,e (A B C D) ?? 0 votes 0 votes Verma Ashish commented Aug 17, 2018 reply Follow Share Yes. 1 votes 1 votes daksirp commented Aug 17, 2018 reply Follow Share if that is the case, then Option D will not be correct . In R1 ∩ R2 All (A B C D) will not be there in resultant table, only two attributes should be there in intersection. Check @Arjun Sirs answer. and this answer of @Shaik Masthan : https://gateoverflow.in/234092/equi_joins?show=234161#c234161 0 votes 0 votes Ayush Upadhyaya commented Oct 5, 2018 reply Follow Share Take a small example and see which one goes correct For $R_1$ take tuples (1,2),(3,1),(1,6) and (7,9) For $R_2$ take tuples (3,6),(4,8),(9,2) The result of given RA query comes to be empty and this is equivalent to if we take the intersection of $R_1$ and $R_2$ 5 votes 5 votes daksirp commented Oct 5, 2018 reply Follow Share Option A & Option B will be eliminated. but option 'C' is also Empty set . 0 votes 0 votes Harshada commented Jan 10, 2019 reply Follow Share @daksirp R1-R2 will give all the tuples of R1 for given example and not an empty set. 1 votes 1 votes daksirp commented Jan 10, 2019 reply Follow Share yaa, mistake. tx for correcting. 0 votes 0 votes Venky8 commented May 5, 2021 reply Follow Share Isn’t it an example of equi-join? I’m confused please clarify. 0 votes 0 votes sauravgahlawat commented Jun 6, 2021 reply Follow Share @Venky8 Question above has conditional join (or theta join) which will result in all the tuples which satisfies the condition A=C ^ B=D and the resulting relation will have the schema R(A, B, C, D) i.e, above conditional join is equivalent to σ(A=C ^ B=D)(R1 X R2) 0 votes 0 votes Venky8 commented Jun 7, 2021 reply Follow Share @sauravgahlawat Yes, that is indeed true but I was curious whether the query in question was also an example of equi-join. An equijoin is a theta join using the equality operator. See Source. So above query is example of both theta-join and equi-join. 0 votes 0 votes sauravgahlawat commented Jun 7, 2021 reply Follow Share @Venky8 Okay, but can we use two equality operator in equijoin? shouldn’t it be like: R1 equijoin (A = C) R2 intersection R1 equijoin(B=D) R2 0 votes 0 votes Venky8 commented Jun 7, 2021 reply Follow Share An equijoin is just a theta join with only an equality operator. Whereas, a non-equijoin use joins with operators other than equality operators like <, >, >=, etc. So of course we can use more than one equality operator in equijoin just like it is used in the query given in the question. There is no equijoin operator neither in relational algebra nor SQL. Equijoin is just a type of join, which is theoretical. Your idea is right. Just correcting your relational algebra query: $R1 \Join _{A = C} R2 \; \bigcap \; R1 \Join _{B = D} R2$ 0 votes 0 votes sauravgahlawat commented Jun 7, 2021 reply Follow Share Okay got it. Also I don’t know where are the correct symbols for joins :( Appreciate the correction 1 votes 1 votes Venky8 commented Jun 7, 2021 reply Follow Share $R1 \Join_{c} R2$ is the general join operation with condition predicate c on the columns of R1 and R2. Left outer join: R1 ⟕$_{c}$ R2 Right outer join: R1 ⟖$_{c}$ R2 Full outer join: R1 ⟗$_{c}$ R2 $R1 \Join R2$ is natural join without any condition specified. The columns having the same name will be merged. 2 votes 2 votes Please log in or register to add a comment.