Join

Combine source table records, used within an SQL FROM clause.

Syntax
      ... FROM table1 LEFT JOIN table2
         ON table1.field1 compopr table2.field2

      ... FROM table1 RIGHT JOIN table2
         ON table1.field1 compopr table2.field2

      ... FROM table1 INNER JOIN table2
         ON table1.field1 compopr table2.field2

Key
   table1, table2   The tables from which records are combined.

   field1, field2   The fields to be joined.
                    The fields must be of the same data type and
                    contain the same kind of data, but they 
                    do not need to have the same name.

   compopr          Any relational comparison operator:
                     =  <  >  <=  >=  or  <>

The INNER JOIN is the most common type of join. Inner joins combine records from two tables whenever there are matching values in both tables.

The LEFT JOIN operation will create a left outer join. This will include all the records from table1 (left), even if there are no matching values for records in table2 (right).

The RIGHT JOIN operation will create a right outer join. This will include all of the records from table2 (right) of two tables, even if there are no matching values for records in table1 (left).

You can join any two numeric fields of like types. For example, you can join on AutoNumber and Long fields because they are like types. However, you cannot join Single and Double types of fields.

Examples

' In SQL
SELECT CategoryName, ProductName
FROM Categories LEFT JOIN Products
ON Categories.CategoryID = Products.CategoryID;

“When making a fire people like to join you, when cleaning the ashes you are often alone” ~ African Proverb

Related

DCount - Count the number of records in a table/query.
Avg (SQL) - Average
Max (SQL) - Return the maximum value from a query.
Min (SQL) - Return the minimum value from a query.
Sum (SQL) - Add up the values in a query result set.