| 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:
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