What is the Difference between Inner Joins and Outer Joins?

Both inner and outer joins are used to combine rows from two or more tables into a single result.  This is done using a join condition.  The join condition specifies how columns from each table are matched to one another.  In most cases the aim is to find equal values between tables, and include those matches.

The most common case for this is when you’re matching the foreign key of one table to the primary key of another, such as when using and ID to lookup a value.

Though both inner and outer joins include rows from both tables when the match condition is successful, they differ in how they handle a false match condition.

Inner joins don’t include non-matching rows; whereas, outer joins do include them.

Let’s dig a little deeper into the mechanics of each

Inner Join Mechanics

An inner join is used to return results by combining rows from two or more tables.

In its simplest case, where there is no join condition, an inner join would combine all rows from one table with those from another.  If the first table contained three rows, and the second, four, then the final result would contain twelve (3 x 4 = 12) !

The purpose of the join condition is to limit which rows are combined.  In most cases we limit rows to those matching a column.

If a person has more than one phone number, then more than one match is made.  From this you can see we may get more rows returned than we have for each person.

Tables to Join

Conversely, if a person has no phone number, then there won’t be an entry in PersonPhone, and no match made.  That particular person won’t be included in the results, as only those with matches are included.

Let’s try an example.

Suppose the HR Manager wants to create a phone directory.  They want the person’s first name, last name, title, and phone numbers.  What query could you use to create this?

Here is one that would do the trick:

SELECT   P.FirstName,
         P.LastName,
         P.Title,
         PH.PhoneNumber
FROM     <span style="color: #008000;">Person.Person</span> AS P
         <span style="color: #ff0000;">INNER JOIN</span>
         <span style="color: #008000;">Person.PersonPhone</span> AS PH
         ON <span style="color: #0000ff;">P.BusinessEntityID = PH.BusinessEntityID
         AND PH.PhoneNumberTypeID = 3</span>
ORDER BY P.LastName

The INNER JOIN specifies which tables to join and the match condition for doing so.  The condition PH.Phone NumberTyeID = 3 limits the query to work numbers.

If you run the above, you get the following results:

Inner Join Results

Keep in mind the inner join only returns row where the match condition is true.  In this example, rows where the BusinessEntityID’s don’t match aren’t included.  This could be an issue if a person doesn’t have a phone number as those employees wouldn’t be on the list.

If you wish to include these employees you can use an Outer join.

Outer Join Mechanics

An outer join is used to return results by combining rows from two or more tables.  But unlike an inner join, theouter join will return every row from one specified table, even if the join condition fails.

Take the phone directory example above.  If the HR manager wanted to list every employee regardless of whether they had a work phone number, then using an outer join would make it so.

SELECT   P.FirstName,
         P.LastName,
         P.Title,
         PH.PhoneNumber
FROM     Person.Person AS P
         <span style="color: #ff0000;">LEFT OUTER JOIN</span>
         Person.PersonPhone AS PH
         ON P.BusinessEntityID = PH.BusinessEntityID
         AND PH.PhoneNumberTypeID = 3
ORDER BY P.LastName

You can learn more about left and right outer joins in this article, for now just understand that when a LEFT OUTER JOIN is used, all rows for the table in the FROM clause are included in the result, even if a match isn’t found with the other table.

When a match isn’t found, then a NULL is place in the column.

You can see this in action below:

Outer Join Results

Notice in the example the PhoneNumber for Catherine Abel is NULL.  This is because Catherine’s work number isn’t listed, and no match was found during the join.

If this would have been an inner join, then this row wouldn’t have been included in the results.

The post What is the Difference between Inner and Outer Joins? appeared first on Essential SQL.