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 to Retrieve 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.
The syntax for both inner joins and outer joins in SQL is quite similar; the main difference is in the keywords used to specify the type of join. Let’s go through each one with examples.
Inner Join Syntax and Example
An inner join only returns records that have matching values in both tables.
Syntax:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Example:
Consider two tables: `Employees` and `TimeOff`.
Employees Table:

| EmployeeID | Name     |
|------------|----------|
| 1001       | Alice    |
| 1002       | Bob      |
| 1003       | Charlie  |

TimeOff Table:

| EmployeeID | AnnualTime |
|------------|------------|
| 1001       | 305        |
| 1003       | 500        |

Using an inner join to find the `EmployeeID` and `AnnualTime` for employees with matching records in both tables:

SELECT Employees.EmployeeID, TimeOff.AnnualTime
FROM Employees
INNER JOIN TimeOff
ON Employees.EmployeeID = TimeOff.EmployeeID;

Result:

| EmployeeID | AnnualTime |
|------------|------------|
| 1001       | 305        |
| 1003       | 500        |

In this result, only `EmployeeID`s 1001 and 1003 are included because they have matching records in both tables.
Outer Join Syntax and Example
An outer join returns all records from one table (and optionally from both), including those that don't have matching records in the other table. If there is no match, it returns `NULL` for columns from the other table.
Syntax:

SELECT columns
FROM table1
LEFT (or RIGHT or FULL) OUTER JOIN table2
ON table1.column = table2.column;

LEFT OUTER JOIN returns all records from the left (first) table, and the matched records from the right (second) table.
RIGHT OUTER JOIN returns all records from the right table, and the matched records from the left table.
FULL OUTER JOIN returns all records when there is a match in either table.

Example:
Using a LEFT OUTER JOIN to find all employees and their `AnnualTime` if available:

SELECT Employees.EmployeeID, TimeOff.AnnualTime
FROM Employees
LEFT OUTER JOIN TimeOff
ON Employees.EmployeeID = TimeOff.EmployeeID;

Result:

| EmployeeID | AnnualTime |
|------------|------------|
| 1001       | 305        |
| 1002       | NULL       |
| 1003       | 500        |

Here, `EmployeeID` 1002 is included even though it doesn't have a matching record in the `TimeOff` table. The `AnnualTime` for this employee is shown as `NULL` because there is no corresponding data in the `TimeOff` table.
Summary

Inner Join: Uses INNER JOIN and only returns records with matches in both tables.
Outer Join: Uses LEFT OUTER JOIN, RIGHT OUTER JOIN, or FULL OUTER JOIN to return all records from one or both tables, even if some don’t have matching records in the other table.

While the syntax structure remains consistent, you specify `INNER`, `LEFT OUTER`, `RIGHT OUTER`, or `FULL OUTER` to control how unmatched records are handled. A join takes the following basic syntax:

Syntax for the Join Operation in SQL Server

SELECT , ...n FROM ltable_name [LEFT JOIN | RIGHT JOIN | INNER JOIN] rtable_name ON lcolumn_name = rcolumn_name [WHERE <search_conditions>]

**Explanation:**

`<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.

1) 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.

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

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

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

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

6) 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.

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