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

Ans: Option C

perform natural join and add spurious tuples from both tables.

\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}

---

All join 

1. Natural Join 

A	B	C
1	5	7

2. Left Outer Join 

A	B	C
1	5	7
3	7	NULL

3. Right Outer Join 

A	B	C
1	5	7
4	NULL	9

4. Full Outer Join  

A	B	C
1	5	7
3	7	NULL
4	NULL	9

---

#two-marks

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

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 ?

---

Actually, natural full outer join isn’t a standard SQL term, but people usually use it to mean a full outer join based on the common columns, like a natural join does ,that is, joining on all common attributes between the two tables. The result will include matching rows, plus unmatched rows from both tables with NULLs filled in where needed.

---

result of R full outer join S is : natual join of R,S+ dangling rows of R +Dangling rows of S with null values in the attributes that are not part of it

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.