Click here to Skip to main content
15,867,453 members
Articles / Programming Languages / SQL

What is the Difference between Inner and Outer Joins?

Rate me:
Please Sign up or sign in to vote.
4.57/5 (9 votes)
31 Dec 2015MIT4 min read 9.8K   4  
Difference between inner and outer joins

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

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:

SQL
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 Joins and 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 BusinessEntityIDs 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.

SQL
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

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.

License

This article, along with any associated source code and files, is licensed under The MIT License


Written By
Easy Computer Academy, LLC
United States United States
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/

Comments and Discussions

 
-- There are no messages in this forum --