SQLConfiguration SQLConfiguration


Select Data  «Prev  Next»
Lesson 4 Retrieving data from multiple tables with inner and outer joins
Objective Construct SELECT queries to retrieve data from multiple tables

Retrieving Data from Multiple Tables with inner and outer Joins

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

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