Differences between an inner and outer Join

In the previous lesson, you learned how to retrieve data from a single table. However, because the concept of a relational database requires you to normalize data into multiple relational tables, very often you must retrieve data from several tables at the same time.

• Inner vs. outer joins
  The data that is retrieved depends on whether you construct an inner join^[1] (which is the default) or an outer join^[2]. An outer join takes the form of a left join or a right join, depending on which side of the JOIN statement the tables are listed. This will become clear in a moment.

Consider the data in the diagram below:

Notice that the Employees table contains three records with EmployeeIDs of 1001, 1002, and 1003. However, EmployeeID 1002 does not have any time entered into the Timesheets table. I will discuss the syntax for the joins in a minute, but conceptually, if you perform an inner join on the EmployeeID in the two tables, only the timesheet records for EmployeeID 1001 and 1003 will be returned. This might be exactly what you want, especially for billing purposes.
However, if you want to be able to see everyone who has not given you time yet, you will need to perform an outer join on the EmployeeID column in the two tables so that you return all the time for EmployeeID 1001 and 1003 and a null value for time for EmployeeID 1002. The diagram below shows the results of selecting all of the data in the tables with an inner and outer join.
In the next lesson, you will learn how to construct inner and outer joins.

Inner Join

EmployeeID	AnnualTime
1001	305
1003	500

Outer Join

EmployeeID	AnnualTime
1001	305
1002	NULL
1003	500

[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 guarantees the return of every row of data that you expect will be returned, but that also can require more resources in order to execute. Includes the left outer join, right outer join, and full outer join.