Lesson 4	Retrieving data from multiple tables with inner and outer joins
Objective	Construct `SELECT` queries to retrieve data from multiple tables

Inner and Outer Joins - SQL Server

Retrieving Data from Multiple Tables

The syntax for an inner and outer join is essentially the same, the only difference is in the keywords that you use.
A join takes the following basic syntax:

column_name is the name of the column from which you are requesting data. If you wish to retrieve data from all columns, you can use the * wildcard.

ltable_name is the name of the table involved on the left side of the join.

rtable_name is the name of the table involved on the right side of the join.

lcolumn_name is the name of the column in the ltable_name that will be used to perform the join in the rtable_name.

rcolumn_name is the name of the column in the rtable_name that will be used to perform the join in the ltable_name.

search_conditions is the way you specify the actual rows in the table that you want to retrieve. If you omit the WHERE clause, every row in the table will be retrieved. So specifying a WHERE clause in your queries is generally a good idea.

Join Keyword Syntax

Specifying a left outer join

For example, to select all the data from both tables, even if the employee does not have any time in the Timesheets table, you would construct a left outer join like this:

SELECT employees.employeeid,timesheets.annualtime
FROM employees LEFT JOIN timesheets 
ON employees.employeeid = timesheets.employeeid

Specifying a right outer join

The statement above ensures that the table listed on the left side of the LEFT JOIN clause returns every row of data, then looks up the corresponding value in the join table. Alternatively, you can rearrange the order of the tables and perform a RIGHT JOIN to produce the same results, like this:

SELECT employees.employeeid,timesheets.annualtime
FROM timesheets RIGHT JOIN employees 
ON employees.employeeid = timesheets.employeeid

Specifying an inner join

To specify an inner join, because the INNER keyword is optional, you can omit the LEFT or RIGHT keyword and specify a join with the following statement:

SELECT employees.employeeid,timesheets.annualtime
FROM timesheets JOIN employees 
ON employees.employeeid = timesheets.employeeid

In the next lesson, you will learn how to give alternate names to your tables and columns within your queries. This is known as aliasing.

Multiple Table Inner Join - Exercise

Click the Exercise link below on the left to practice creating outer joins.
Multiple Table Inner Join - Exercise