What is the difference between INNER join and OUTER join in SQL?

Let say we have two tables X and Y.

The result of an INNER JOIN of X and Y is X intersect. It is the INNER overlapping intersection part of a Venn diagram.

The result of an OUTER JOIN of X and Y is X union Y. It is the OUTER parts of a Venn diagram.

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.

  • INNER JOINAn INNER JOIN by using following query will give the intersection of the two tables X and Y. The intersection is the common data between these tables.

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

    x | y
    — +–
    30 | 30
    40 | 40

  • OUTER JOIN A full OUTER JOIN by using following query will us the union of X and Y. It will have all the rows in X and all the rows in Y. If some row in X has not corresponding value in Y, then Y side will be null, and vice versa.

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

    x | y
    —– + —–
    10 | null
    20 | null
    30 | 30
    40 | 40
    null | 60
    null | 50

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 *

*