SQL inner vs outer join

SQL Inner vs. Outer Joins.

One thing that I get a lot of questions on regarding SQL queries, is in joining tables.  Basically, there are two types of joins, inner, and outer.

With an inner join, in order for a logical record to be returned, there has to be corresponding data in both tables, for the fields being joined.  For an outer join, all data from one table is returned, even if there is no corresponding data in the other table.  Which table returns all records with an outer join, depends on the order the tables are specified, and whether left or right join is used.

Lets look at the following simple tables:

Table1:
     Field1    Field2
     ABC      1
     DEF      2
     GHI       3

Table2:
     Field3    Field4
     ABC      4
     DEF      5
     JKL       7

Let us select everything from these tables, with an inner join where Table1.Field1=Table2.Field3.  The syntax in MS-SQL would be "SELECT * FROM Table1 INNER JOIN Table2 ON Table1.Field1=Table2.Field3".  This will give the following:

     Field1    Field2    Field3    Field4
     ABC      1           ABC      4
     DEF      2           DEF       5

Only the records from the tables that have the same value for Table1.Field1 and Table2.Field3 are combined to create logical records.

Now, lets look at the same tables, using left and right joins.  First, note that "left" and "right" are determined from the order that the tables are defined.  In other words, the table being selected from is the "left" table, and the table being joined is the "right" table.  So, doing a left outer join on our tables, the syntax would be "SELECT * FROM Table1 LEFT OUTER JOIN Table2 ON Table1.Field1=Table2.Field3".  In this case, Table1 is the "left" table, and Table2 is the "right" table.  Using the command "SELECT * FROM Table1 JOIN Table2 ON Table2.Field3=Table1.Field1" is the same, since left and right are determined by the order the tables are defined, and not the order of the "join on" clause.  This will give the following results:

    Field1    Field2    Field3    Field4
     ABC     1           ABC      4
     DEF     2           DEF      5
     GHI      3           NULL     NULL

All records from Table1 are returned.  If there is a match on Table2, then the value of those fields are returned.  Otherwise the fields from Table2 are NULL.

As expected, using a right outer join will give the opposite results.  So, "SELECT * FROM Table1 RIGHT OUTER JOIN Table2 ON Table1.Field1=Table2.Field3" will give the following:

     Field1    Field2    Field3    Field4
     ABC      1           ABC      4
     DEF      2           DEF       5
     NULL     NULL    JKL        7

All records from Table2 are returned, with the fields from Table1 set null where there is no matching record.

There is one final case, and that is the full outer join.  In this case, all records from both tables are returned.  Any fields with non-matching records return NULL.  So, "SELECT * FROM Table1 FULL OUTER JOIN Table2 ON Table1.Field1=Table2.Field3" will return:

     Field1    Field2    Field3    Field4
     ABC      1           ABC      4
     DEF      2           DEF       5
     GHI       3           NULL     NULL
     NULL    NULL     JKL        7

Those are the basic principles of joining SQL tables.  Joining tables can be as complex as you need to get the results that you require.  Multiple tables can be joined, not just two as in these examples.  Additionally, you can use a combination of inner and outer joins in the same statement.  So, you can inner join TableA to TableB, left outer join TableA to TableC, right outer join TableB to TableD, and so on.

Dave

Leave a Reply