What is the difference between Left OUTER Join and Right OUTER Join?

Let say we have two tables X and Y.

The result of an LEFT OUTER JOIN of X and Y is all rows of X and common rows between X and Y.

The result of an RIGHT OUTER JOIN of X and Y is all rows of Y and common rows between X and Y.


E.g.

Consider following two tables, with just one column x and y:

x | y
– – -|- –
10 | 30
20 | 40
30 | 50
40 | 60

In above tables (10,20) are unique to table X, (30,40) are common, and (50,60) are unique to table Y.

  • LEFT OUTER JOINA left OUTER JOIN by using following query will give us all rows in X and common rows in X and Y.

    select * from X LEFT OUTER JOIN Y on X.x = Y.y;

    x | y
    — -+—–
    10 | null
    20 | null
    30 | 30
    40 | 40

  • RIGHT OUTER JOINA right OUTER JOIN by using following query will give all rows in Y and common rows in X and Y.

    select * from X RIGHT OUTER JOIN Y on X.x = Y.y;

    x | y
    —– +—-
    30 | 30
    40 | 40
    null | 50
    null | 60

Read the full book at www.amazon.com
Posted in SQL, SQL Interview Questions

Leave a Reply

Your email address will not be published. Required fields are marked *

*