Tuesday, November 1, 2011

Difference between Inner and Outer Join in SQL

Joins are used to combine the data from two tables, with the result being a new, temporary table.
The temporary table is created based on column(s) that the two tables share, which represent meaningful column(s) of comparison.
The goal is to extract meaningful data from the resulting temporary table.
Joins are performed based on something called a predicate, which specifies the condition to use in order to perform a join.
A join can be either an inner join or an outer join, depending on how one wants the resulting table to look.

Example:
Suppose you have two Tables, with a single column and data as follows:

Table1: ID1                        Table2: ID2
            A                                        C 
            B                                        D
            C                                        E
            D                                        F

Inner Join Query would look like:

select * from Table1 INNER JOIN Table2 ON Table1.ID1 = Table2.ID2

You would get the following result:

ID1    ID2
C       C  
D       D

Left Outer Join Query would look like:

select * from Table1 LEFT OUTER JOIN Table2 ON Table1.ID1 = Table2.ID2

You would get the following result:

ID1    ID2
A       null 
B       null
C        C
D        D

Full Outer Join Query would look like:

select * from Table1 FULL OUTER JOIN Table2 ON Table1.ID1 = Table2.ID2

You would get the following result:

 ID1    ID2
 A       null 
 B       null
 C        C
 D        D
null      E
null      F

Source: https://www.nilebits.com/blog/2010/11/difference-between-inner-and-outer-join-in-sql/

No comments:

Post a Comment