JOIN OPERATOR
The JOIN
operator specifies how to relate tables in the query.
Types of
join:
INNER
OUTER (LEFT.
RIGHT, FULL)
CROSS
INNER
JOIN: Select only those rows that have
values in common in the columns specified in the ON clause.
OUTER
JOIN: Select all rows from the table on
the left (or right, or both) regardless of whether the other table has values
in common and (usually) enter NULL where data is missing.
CROSS
JOIN: Select all possible combinations of rows and columns
from both tables (Not A Good Thing - may run for a very long time and produce a huge result set that may not be
useful.)
Access uses
the ANSI (American
National Standards Institute) style, with the JOIN and ON keywords. Access, MySQL, and Oracle all use similar
syntax, with more join types and options in MySQL and Oracle (CROSS JOIN, FULL
OUTER JOIN).
Older
syntax includes the join condition in the WHERE clause (theta style). Note the number of rows and columns in the
result set for the Orders Query and try the same example (omit columns if you
don't want to type so much), using the older style and table aliases, as
follows:
SELECT
o.OrderID, o.CustomerID, o.EmployeeID, o.OrderDate, o.RequiredDate,
o.ShippedDate, o.ShipVia, o.Freight, o.ShipName, o.ShipAddress, o.ShipCity,
o.ShipRegion, o.ShipPostalCode, o.ShipCountry, c.CompanyName, c.Address,
c.City, c.Region, c.PostalCode, c.Country
FROM
Customers c, Orders o
WHERE
c.CustomerID = o.CustomerID;
NOTE: Compare this query in design view with the
ANSI style query. MS Access runs this
query correctly but cannot represent it in the usual way In
the graphical query interface.
Other
examples:
Alphabetical List of Products: includes data from the Products and
Categories tables, in this case all columns from Products (Products.*) and one
from Categories
Order Details Extended:
includes data from the Order Details and Products tables and a
calculated column (Extended Price) that uses UnitPrice, Quantity, and Discount
from Order Details to come up with the discounted total for each line item.