6. MICROSOFT ACCESS QUERIES – ADVANCED

# 6.8.1: Inner Join

All of the joins we have seen up to this point have been inner joins. For a row of one table to be included in the result of an inner join, the row must match a row in the other table. Because all joins so far have also been equi-joins the matching is based on the values of the join fields of one table being equal to the values of the join fields of the other table.

Consider the inner join between Department and Course based on deptCode:

If the tables have the contents shown below:

Course

 Dept Code Course Number Title Description Credit Hours ACS 1453 Introduction to Computers This course will introduce students to the basic concepts of computers: types of computers, hardware, software, and types of application systems. 3 ACS 1803 Introduction to Information Systems This course examines applications of information technology to businesses and other organizations. 3
 Dept Code Dept Name Location Phone Chair ACS Applied Computer Science 3D07 (204) 786- 0300 Simon Lee ENG English 3D05 (204) 786- 9999 April Jones MATH Mathematics 2R33 (204) 786- Peter 0033 Smith

Figure 6.34: Table contents

then the result of running the query is:

 Dept Name Course Number Title Applied Computer Science 1453 Introduction to Computers Applied Computer Science 1803 Introduction to Information Systems

Figure 6.35: Query result

In the above result, notice there is no result line for English or Mathematics; this is because for the sample data there were no rows in Course that joined to the English or Mathematics rows in Department. Both rows in Course have a value of “ACS” in the deptCode field and so they joined to the ACS row in Department.

This query demonstrates a distinguishing characteristic of the inner join: only rows that match other rows are included in the results.

## Exercises

1) Consider the Library database:

• Write a query that joins Loan and Member. List the member name and date due.
• Write a query that joins Loan and Book. List the book title and date due.
• Write a query that joins all three tables and lists the member name, book title, and date due.

2) Consider the two tables A and B below.

Table A

 X Y Z 1 3 5 2 4 6 4 9 9

Table B

 X Y Q 1 3 5 1 4 6 2 4 7 3 4 5
• How many rows are in the result if A and B are joined based on the attribute X?
• How many rows are in the result if A and B are joined based on both attributes X and Y?