GATE CSE 2015 Set 2 | Question: 32

Question

Consider two relations $R_1(A,B)$ with the tuples $(1,5), (3,7)$ and $R_2(A,C) = (1,7),(4,9)$.

Assume that $R(A,B,C)$ is the full natural outer join of $R_1$ and $R_2$. Consider the following tuples of the form $(A,B,C):$

$a = (1,5,null), b=(1,null,7), c=(3,null,9), d=(4,7,null), e=(1,5,7), $

$ f=(3,7,null),  g=(4,null,9).$

Which one of the following statements is correct?

• A. $R$ contains $a, b, e, f, g$ but not $c, d$.
• B. $R$ contains all $a, b, c, d, e, f, g$.
• C. $R$ contains $e, f, g$ but not $a, b$.
• D. $R$ contains $e$ but not $f, g$.

Comments

I think this image is self explanatory

All the best for GATE

Answer 1

$R_1(A,B): \begin{array}{|c|c|} \hline \textbf{A} & \textbf{B} \\\hline \text {1} &  \text{5 }\\\hline \text{3} & \text{7} \\\hline \end{array}$

$R_2(A,C): \begin{array}{|c|c|} \hline \textbf{A} & \textbf{C} \\\hline \text {1} &  \text{7}\\\hline \text{4} & \text{9} \\\hline \end{array}$

Now , if we do full natural outer join:
$$\begin{array}{|c|c|} \hline \textbf{A} & \textbf{B} & \textbf{C}\\\hline \text {1} &  \text{5 } & \text{7}\\\hline \text{3} & \text{7} & \text{NULL} \\\hline  \text{4} & \text{NULL} & \text{9} \\\hline \end{array}$$
So, option (C) is correct.

Comments

only 1 match between two tables so that row will have all the values in 3 7 case

A - 3, B-7 no C so bcz it is FOJ so put that row in the table with null

---

@kshitij arunabh because in R2 9 of C is with 4 of A, thats why in R(A,B,C) 9 is with (4, null, 9) 

---

I still doesn't understood this problem. If we are joining both tables like natural join then there must be only one tuple 1 5 7. But if we are doing full outer join then there would be 5 tuple possible 1 5 7, 1 5 null, 3 7 null, 1 null 7, 4 null 9.

But what is natural full outer join ?

Answer 2

Ans C. Full outer join means take all matching rows from left table and right table based on common columns.

Comments

ok clear.

---

More info about joins:

https://stackoverflow.com/questions/42265203/difference-between-natural-full-outer-join-and-full-outer-join

---

Ya same question .. No one has explained that.