Both inner and outer joins can be 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. You can get started using these free tools using my Guide Getting Started Using SQL Server.
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 an 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: rgb(0, 128, 0);">Person.Person</span> AS P
<span style="color: rgb(255, 0, 0);">INNER JOIN</span>
<span style="color: rgb(0, 128, 0);">Person.PersonPhone</span> AS PH
ON <span style="color: rgb(0, 0, 255);">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 rows 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
, 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
<span style="color: rgb(255, 0, 0);">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.