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?
