GATE CSE 1998 | Question: 1.33

Question

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$?

• A. $R_1 \cup R_2$

• B. $R_1 \times R_2$

• C. $R_1 – R_2$

• D. $R_1 \cap R_2$

Comments

Detailed Video Solution: https://youtu.be/H4-GIPOm2DE?feature=shared

NOTE that the given "join" operation is Equi-Join, & hence the Schema of the result will be $(A,B,C,D)$, So, technically, no option is correct.

BUT Authors like Raghu Ramakrishnan project only one attribute from the equated attributes in Equijoin.

The schema of given Equijoin operation, as per Navathe & Ullman, will be $(A,B,C,D).$

The schema of given Equijoin operation, as per Raghu Ramakrishnan, will be $(A,B).$

Neverthless, Option D is the best choice. Watch the Video Solution above.

---

taking small examples can help :
A  B           C   D
1  2            5   9
2  3            2   3
4  3            3   1
5  1            8   4
given expr will return (2  3)

Answer 1

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.

Comments

so, what will be the attributes of resultant table :

(A B) or (C D) ??

---

in natural join common attributes are removed. Here all attributes are present

only those tuples where A = C and B = D

---

didnt get you exactly, are you saying " all attributes will be present in resultant table " i,e (A B C D) ??

---

Yes.

---

if that is the case, then Option D will not be correct . 

In R₁ ∩ R₂

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

 

---

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$

---

Option A & Option B will be eliminated.

but option 'C' is also Empty set .

---

@daksirp R1-R2 will give all the tuples of R1 for given example and not an empty set.

---

yaa, mistake. tx for correcting.

---

Isn’t it an example of equi-join? I’m confused please clarify.

---

@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)

---

@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.

---

@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

---

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$

---

Okay got it. Also I don’t know where are the correct symbols for joins :( Appreciate the correction

---

$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.

 

---

From Union Compatible,we can't say that R1 U R2 means option A although there is a equi join?

---

isnot join operations gives us schema which takes attribute of both table
here (ABCD) is the schema right?

---

.

---

Right ABCD will be the final schema here

---

The question can be equally transformed to : r1(ab) natural join r2(ab) with renaming cd

Answer 2

✍️ Example:

Let’s say:

R₁(A, B):

A	B
1	2
3	4
5	6

R₂(C, D):

C	D
3	4
5	6
7	8

🔗 Operation: R₁ ⋈A₌C ∧ B₌D R₂

We match rows where:

• A = C

• B = D

So we look for tuples that are identical in both tables.

✅ Matching tuples:

• ⟨3, 4⟩

• ⟨5, 6⟩

🎯 Result:

A	B	C	D
3	4	3	4
5	6	5	6

This is just the intersection of R₁ and R₂.