I think this image is self explanatory

All the best for GATE

26 votes

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?

- $R$ contains $a, b, e, f, g$ but not $c, d$.
- $R$ contains all $a, b, c, d, e, f, g$.
- $R$ contains $e, f, g$ but not $a, b$.
- $R$ contains $e$ but not $f, g$.

36 votes

Best answer

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

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

0

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

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

0

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

5 votes

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

2

No full outer join doesnt mean that.It means take all tuples irrespective of any condition and print all the tuples putting null where the attributes are not applicable.Its cardinality turns out to be n(A) + n(B) option a is correct.

0

How C is the answer?

How 3rd tuple (4,null,9) is coming? if that happens then i think from left outer join (1,5,null)tuple also should come.

How 3rd tuple (4,null,9) is coming? if that happens then i think from left outer join (1,5,null)tuple also should come.