6.8: Inner and Outer Joins

Whenever we use a query to retrieve data from two or more tables, the database query processor performs an operation called a join. In this section, we discuss inner joins, outer joins, and Cartesian products. Following that we discuss some interesting special cases: self-join, anti-join, non-equi joins.

If we have previously established relationships between tables, and if we have more than one table in a query, then MS Access will create joins based on those relationships. If necessary, we can alter, delete, or include new relationships.

MS Access creates joins where rows join if the join fields are equal in value; such joins are called equi-joins. If we create a query for the University database and add the Department and Course tables to the relationships area of the query we have:

If you edit the relationship line (double-click it), you see the join properties:

Here, we can see the join is based on the common attribute deptCode. If you click on the Join Type button, you will get information on the type of join used. You will see (as the following diagram shows) that Access has selected the first of three options:

Joins can be further characterized as inner or outer joins. Option 1 is an inner join.

Options 2 and 3 are outer joins. One of these would also be called a Left Outer Join and the other a Right Outer Join. If you examine the SQL statement generated you will see which is used; Left and Right choices are related to the textual expression of the SQL statement – which table name is leftmost/rightmost in the From clause.