Joins in a Query

Joins in a Query
In a relational database, a "join" is a condition that connects rows of different tables. For example in an employee database, the Employee table may be joined to the Department table by the join condition

Department.DeptCode = Employee.DeptCode

which indicates how to look up or match the department that the employee works in.

The fact that the joining column names are same in the two tables is common practice, but is actually irrelevant. The join has to be explicitly set up by a user at either the database level (joins are declared while the database is being created, and stored in the database, as you can do in Access 2.0) or at the application level (when a program uses the tables it has to be told what joins should be used, as you have to do with dBase III files). If you include more than one table and do not indicate join conditions, the results data set will be the so-called "Cartesian product," where each row of the first table will be permuted with each row of the second. If the first table has 10 rows and the second 15, you will get 150 rows in the result set.

When you specify a join you need to specify whether it is a so-called "inner" or "outer" join. For the user these will be expressed as the following three options.

Include only those rows where Department.DeptCode equals Employee.DeptCode. (An Inner Join).
Include all rows from Department and only those rows from Employee where Department.DeptCode equals Employee.DeptCode (A Left Outer Join).
Include all rows from Employee and only those rows from Department where Department.DeptCode equals Employee.DeptCode (A Right Outer Join)

When tables included in the query condition contain identical column names, it is assumed that those columns are joined as an inner join, and the Joins in Query list box will initially contain these joins.