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:

 

image
                                              Figure 6.33: Inner join

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?

License

Share This Book