Full outer join

Question

Can any one explain this question

Answer 1

required output = output of Cartesian product equal to FULL OUTER JOIN

 

option A and D :-  is pretty clear that it doesn't produce our require output. ( due to A.x is not matching with B.x ===> Cartesian product produce m*n and full outer join produce m+n is always not equal)

 

option B,C and E all are produce our required output.

 

but option B is restricting that your two tables should contain 1 rows only

option C is restricting that your one of table should contain 1 rows only

 

option E is doesn't restricting on number of rows of table but restricting that they must have only one different value on column x and A.x is equal to B.x

take A has 4 rows and B has 3 rows and column x is z ===> Cartesian product = 12 , full outer join = 3+3+3+3 = 12 ( A table first row z is mapped with 3 rows of B, A table second row z is mapped with 3 rows of B etc..

option B is a subset of option E,

 

By comparing option C and E ====> you can conclude that Option E is Better Choice.

Comments

Option E is giving same no of rows...but option b and c are giving same output...

Also in option E table will have some null values...but Cartesian product won't produce null values..

---

Who says always result null values in full outer join??

---

For option E, Take this example

A have following rows

Aid name x

1 a 500

2 b 500

Let B have following rows

Bid name x

101 g 500

102 gg 500

103 GGG 500

Consider the output of Cartesian product and full outer join.. those are not equal??

---

@ Chirag arora, are you getting this?