JOIN OPERATOR
The
JOIN
operator specifies how to relate tables in the query. The JOIN
operator is only one of the set operations available in relational
databases.
The
following join types of
join are available in most relational databases:
INNER
OUTER (LEFT.
RIGHT, FULL)
CROSS
Joins may be represented as Venn diagrams, as
shown below along with other common set operations:
INNER
JOIN: Select only those rows that have
values in common in the columns specified in the ON clause.
LEFT,
RIGHT, or FULL 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.
(Note: FULL OUTER JOIN not implemented in Access.)
CROSS
JOIN (not illustrated - not exactly a set operation):
Select all possible combinations of rows and columns
from both tables (Cartesian product). Not available in Access but can
"happen" by not specifying relationships between tables or not setting
up the appropriate joins in a query. (Not A Good Thing - the
query may run for a very long time and
produce a huge, not very useful result set.)
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 and other set operations in
MySQL and Oracle (CROSS JOIN, FULL
OUTER JOIN, INTERSECT, MINUS).
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.