JOIN OPERATOR

The JOIN operator specifies how to relate tables in the query.  The JOIN operator is 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:

Result of applying these joins in a query:

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