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.
Hello my name is Kris. I’m here because I am passionate about helping non-techie people to overcome their fear of learning SQL.
I know what it is like to not know where to start or whether the time spent learning is worth the effort. That is why I am here to help you to:
- Get started in an easy to follow step-by-step manner.
- Use your time wisely so you focus on what is important to learn to get the most value from your time.
- Answer your questions. Really! Just post a comment and I’ll respond. I’m here to help.
It wasn’t long ago that I was helping a colleague with some reporting. She didn’t know where to start and soon got overwhelmed and lost as she didn’t know SQL.
I felt really bad, as she was under pressure to get some summary information to her boss, the built-in reports were falling short, and to make them better would require her to know SQL. At that time that seemed impossible! It in dawned on me, it doesn’t have to be that way.
Then I discovered a way for anyone with the desire to easily learn SQL. I worked with my co-worker, started to teach her what I learned and soon she was able to write reports and answer her boss’ questions without getting stressed or ploughing hours into manipulating data in Excel.
It hasn’t always been easy. Sometimes the information seems abstract or too conceptual. In this case I’ve found out that a visual explanation is best. I really like to use diagrams or videos to explain hard-to-grasp ideas.
Having video, pictures, and text really help to reinforce the point and enable learning.
And now I want to help you get the same results.
The first step is simple, click here http://www.essentialsql.com/get-started-with-sql-server/