JOIN

A join enables you to use a single SELECT statement to query two or ore tables simultaneously
There are three main types of join:

• Self Join - special type of inner join where a table is joined to itself

• Inner Join - most common

• Outer Join - Left -

• Outer Join - Right -

• Outer Join - Full -

• Cross Join - ??

--4 different types of JOIN - joins are used to combine records from multiple tables
--Inner Joins (or Equi Join) - lets you retrieve data from multiple tables when there are matching rows.
--Outer Joins - lets you retrieve data from multiple tables even when there is no matching row.
----Left Join - displays every record from the table on the left of these keywords
----Right Join - displays every record from the table on the right on these keywords
--Cross Joins
--Self Joins - lets you join the table to itself by using a table alias

Qualifying the Tables

When you are querying from multiple tables it is very important to always qualify the column names with the name of the table
TableName.ColumnName

Outer Joins

These are used to retrieve all records from multiple tables even when there is no matching record in one (or both) of the joined tables.
The results of an outer join will be the results from an inner join plus the records that do not match in one of the other tables.

Natural Joins

This is a special type of INNER JOIN where the only difference is that only one of the "joined" fields is returned (since they must both contain the same data it is unnecessary to return both these columns).
You cannot specify the join attributes yourself since this is done automatically by using the columns which have the same names.
This does not take into account primary or foreign keys.

Equi-Join

Modifying Joins

In Data View
You can modify the type of join by highlighting the join.
Select the line, right click and select Join Properties
SS

What is a Nested Join ?

The nested join is performed first
The results of the nested join are then joined to the Products table.
It doesn't matter what table you join first as inner joins are associative.

SELECT       T.* 
             P.ProductName 
             C.FirstName 
             C.LastName 
FROM         PRODUCTS As P 
INNER JOIN  (CUSTOMERS As C 
                   INNER JOIN Transactions As T 
                   On C.CustomerID = T.CustomerID) 
ON           P.ProductID = T.ProductID