Its is easy to get confused between inner join vs outer join clauses. Both inner and outer joins are used to combine data from two or more tables; however, there are some key differences! Read this article to find out each command’s strengths and when to use them.
To get the most of this and our other lessons, be sure to practice using the examples!
All the examples for this lesson are based on Microsoft SQL Server Management Studio and the AdventureWorks2012 database. Get started using these free tools using my Guide Getting Started Using SQL Server.
Table of contents
Inner vs Outer Join Clauses
SQL Inner and SQL outer joins combine rows from two or more tables into a single result 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 look up 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
Read More: SQL Joins – The Ultimate Guide >>
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.
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.
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 Person.Person AS P INNER JOIN Person.PersonPhone AS PH ON P.BusinessEntityID = PH.BusinessEntityID AND PH.PhoneNumberTypeID = 3 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:
Keep in mind the inner join only returns row where the match condition is true. In this example, the 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, the outer 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
LEFT OUTER JOIN
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 placed in the column.
You can see this in action below:
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.
Leave a Reply