| Lesson 3 | Retrieving data from multiple tables with inner and outer joins |
| Objective | Describe the differences between an inner and outer join. |
Differences Between an Inner and Outer Join
In the previous lesson, you learned how to retrieve data from a single table. However, because a relational database requires you to normalize data into multiple related tables, you will very often need to retrieve data from several tables at the same time.
Inner vs. Outer Joins
An
The data that is retrieved depends on whether you construct an inner join[1] (the default) or an outer join[2]. An outer join takes the form of a left join, a right join, or a full join, depending on which side, or both sides, of the
INNER JOIN returns only rows with matching values in both tables. An OUTER JOIN returns those same matches, plus the non-matching rows from one or both tables, left, right, or full, filling in NULL for whatever column had no match.
The data that is retrieved depends on whether you construct an inner join[1] (the default) or an outer join[2]. An outer join takes the form of a left join, a right join, or a full join, depending on which side, or both sides, of the
JOIN statement you want preserved even when there is no match. This will become clear in a moment.
Consider the data in the tables below:
Employees Table
|
Timesheets Table
|
Notice that the Employees table contains three records, EmployeeIDs 1001, 1002, and 1003, but EmployeeID 1002 has no entry in the Timesheets table. The syntax for constructing these joins is covered in the next lesson, but conceptually: if you perform an inner join on EmployeeID across the two tables, only the timesheet records for EmployeeID 1001 and 1003 are returned. This might be exactly what you want, especially for billing purposes, since it only shows employees who have actually logged time.
However, if you want to see everyone who has not yet logged time, you need a left outer join on EmployeeID, preserving every row from the Employees table (the left table) regardless of whether a match exists in Timesheets. That returns the time for EmployeeID 1001 and 1003 as before, plus a
Notice that the choice between the two is not a question of which join is "correct" in some general sense; it depends entirely on the question you are trying to answer. Ask payroll "whose logged time should we pay out this period?" and the inner join's three-row answer, Mann and Jones only, is exactly right; Smith has nothing to pay out yet. Ask a manager "which of my employees still need to submit their time?" and that same inner join would hide the one person the manager actually needs to see. The outer join's extra row, EmployeeID 1002 with a
However, if you want to see everyone who has not yet logged time, you need a left outer join on EmployeeID, preserving every row from the Employees table (the left table) regardless of whether a match exists in Timesheets. That returns the time for EmployeeID 1001 and 1003 as before, plus a
NULL for EmployeeID 1002's AnnualTime, since Smith exists as an employee but has no timesheet entry yet.
Notice that the choice between the two is not a question of which join is "correct" in some general sense; it depends entirely on the question you are trying to answer. Ask payroll "whose logged time should we pay out this period?" and the inner join's three-row answer, Mann and Jones only, is exactly right; Smith has nothing to pay out yet. Ask a manager "which of my employees still need to submit their time?" and that same inner join would hide the one person the manager actually needs to see. The outer join's extra row, EmployeeID 1002 with a
NULL AnnualTime, is not a flaw to work around; it is the answer to that second question.
| EmployeeID | AnnualTime |
|---|---|
| 1001 | 305 |
| 1003 | 500 |
| EmployeeID | AnnualTime |
|---|---|
| 1001 | 305 |
| 1002 | NULL |
| 1003 | 500 |
Definitions
- Inner join. A link between tables in a database that displays only the rows with a match, a true value of the join condition, in both tables. An inner join does not guarantee that every row from either table will appear in the results; a row with no match on the other side simply does not show up.
- Outer join. A link between tables that preserves every row from one side (a left or right outer join) or both sides (a full outer join), even when there is no matching row on the other side, filling the missing columns with
NULL. Because the database has to account for these unmatched rows as well as the matched ones, an outer join can require more resources to execute than an inner join on the same tables.
Summary
A short recap before the next lesson:
- An inner join returns only the rows that match in both tables; a row with nothing to match on the other side simply does not appear.
- An outer join preserves every row from one side (left or right) or both sides (full), filling in
NULLwhere no match exists. - Which join is right depends on the question being asked, not on which one returns "more" or "correct" data; the Employees and Timesheets example answers two legitimately different questions with two different joins.
- This lesson covered the concept only. The next lesson covers the actual Transact-SQL syntax for constructing both kinds of joins.
In the next lesson, you will learn how to construct inner and outer joins.
[1] Inner join: a link between tables in a database that displays only the rows with a match (true value of the join condition) for both join tables. An inner join does not guarantee the return of every row of data that you expect will be returned.
[2] Outer join: a link between tables that preserves every row from one side, or both sides, of the join, even when there is no matching row on the other side, but that also can require more resources to execute. Includes the left outer join, right outer join, and full outer join.