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
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
--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
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.
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.
In Data View
You can modify the type of join by highlighting the join.
Select the line, right click and select Join Properties
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.
FROM PRODUCTS As P
INNER JOIN (CUSTOMERS As C
INNER JOIN Transactions As T
On C.CustomerID = T.CustomerID)
ON P.ProductID = T.ProductID