Breaking News
Loading...

left,right,full outer joins





Outer Join:
Records which will not join with the other record set are still included in the result
Left Outer:
Records from the first data set are included whether they have a match or not.Fields from the unmatched bag are set to null.
use the datasets from inner join tutorial
Code:
Userinfo_leftouter = join aa BY user left outer,bb BY user;
Output:

User 1,user 2 and user 5 have matches ,it shows concatenated results.User 4 does not any match in bb,so the bb part has null values. User 3 in bb is not displayed at all.


Right Outer:
The opposite to the left outer join. Records from the second data set are included no matter what. Fields from unmatched bag are set to null.
Code:
Userinfo_leftouter = join aa BY user right outer,bb BY user;
Output:


Full Outer:
Records from both sides are included.For unmatched records the fields from the other bag are set to NULL.
Code:
Userinfo_leftouter = join aa BY user full outer,bb BY user;
Output:


Summary:








- See more at: http://labstrikes.blogspot.in/2012/08/adsense-middle-blog-post.html#sthash.gQgSkqx8.dpuf
 
Toggle Footer