Skip to content

Hello, Join!

2012/02/02

When I got a job as a database developer in 2002 I found out afterwards that the reason they hired me was because I gave a good answer to the question,

What is the difference between an inner and outer join?

Having just received a technology degree with a course in database design I thought this was a pretty softball question. Fast forward to my 2011 job search. This same question was a part of my technical interview about half of the time. I still think it is an easy question, but it’s good to know that some things don’t change in the technology world.

Simply put, an inner join includes everything in both tables where they match on a column (or columns) and an outer join can be everything from both tables or more commonly everything from one table and only records from the second table where they match on a column (or columns). If you already knew the answer then that probably made sense. However, I believe the difference between an inner and outer join can be explained best by an example. I spent a good many years working in payroll so my go-to example is vacation and sick time. Assume we have a table of employees, we’ll call it Employee:

EmployeeID Name
0 Spock
1 James Kirk
2 Hikaru Sulu

And a table with PTO (Paid Time Off) Balances, we’ll call it PTO:

EmployeeID PTOType PTOBalance
0 Sick 100
0 Vacation 100
1 Sick 8
1 Vacation 0

The logical thing to create at this point is a query which combines these two tables so we can list the name along with the PTO balances – an inner join.

Select Name,PTOBalance from 
Employee INNER JOIN PTO on 
Employee.EmployeeID = PTO.EmployeeID

This query will pull back all of the records from the employee table and all of the records from the PTO table where they match on EmployeeID giving you :

Name PTOBalance
Spock 100
Spock 100
James Kirk 8
James Kirk 0

Which is a great PTO report except for one thing. There is one employee missing because there are no records on the PTO table with EmployeeID = 2. Perhaps he has not been employed long enough to have any days off or maybe the Captain just has it in for him. Whatever the reason the information returned probably isn’t what we want in a report. We can switch to an outer join and see all of the employees even if they don’t match to a record on the PTO table. I am using a left outer join because I am listing the Employee table first. It is listed to the left of the other table when you write out the query. Don’t worry about that for now.

Select Name,PTOBalance from 
Employee LEFT OUTER JOIN PTO on 
Employee.EmployeeID = PTO.EmployeeID

This will return the following set:

Name PTOBalance
Spock 100
Spock 100
James Kirk 8
James Kirk 0
Hikaru Sulu  Null

A common problem this can solve is to find all of the employees without any PTO set up. You would do this by

Select Name from 
Employee LEFT OUTER JOIN PTO on 
Employee.EmployeeID = PTO.EmployeeID
WHERE PTO.EmployeeID is null

This will return the following set:

Name
Hikaru Sulu

And there you have it – the difference between an inner and outer join broken down into a basic example. There are only 99 more interview questions you need to know the answer to.

About these ads

From → SQL Server

2 Comments
  1. Do you carry a towel on your intergalactic travels? Great explanation.

    • SQLMD permalink

      I thought you might be my only cross over from the old blog. You’re an Oracle person though right?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 542 other followers