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:

Figure \(\PageIndex{1}\): Standard equi-join.

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

Figure \(\PageIndex{2}\): 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:

Figure \(\PageIndex{3}\): Choosing inner join or outer join.

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.

---

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:

Figure \(\PageIndex{4}\): Inner join.

If the tables have the contents shown below:

Table \(\PageIndex{1}\): Course table contents.

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

Table \(\PageIndex{2}\): Department table contents.

Department
Dept Code	Dept Name	Location	Phone	Chair
ACS	Applied Computer Science	3D07	[phone]	Simon Lee
ENG	English	3D05	[phone]	April Jones
MATH	Mathematics	2R33	[phone]	Peter Smith

then the result of running the query is

Table \(\PageIndex{3}\): Query result.

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

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 (Set 1)

Exercise \(\PageIndex{1}\)

Consider the Library database:

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

Exercise \(\PageIndex{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

1. How many rows are in the result if A and B are joined based on the attribute X?
2. How many rows are in the result if A and B are joined based on both attributes X and Y?

---

Outer Join

Consider the Company database. Suppose we wanted to produce a report that lists each department and its employees, and that we must include every department. The two tables would be joined based on equal values of the dept id field. We want all departments and we know that an inner join will not include a department if there are no employees for the department to join to. To get all departments included when we are joining two tables we must use an outer join.

Consider the query that is started below:

Figure \(\PageIndex{5}\): Initial query.

By default the join is an inner join, but with MS Access you can get an outer join if you edit the relationship and specify either option 2 or option 3, as shown in the dialogue below:

Figure \(\PageIndex{6}\): Default property is option 1.

By choosing option 2 your query will include all departments whether or not the department can join to an employee. If there is no employee for a department to join to, then the row is joined to a row of nulls. When you do this notice the change in the relationship line – it is now a directed line; this is how MS Access illustrates outer joins:

Figure \(\PageIndex{7}\): Outer join – all rows of Department.

The first few rows of the result are:

Table \(\PageIndex{4}\): Query result.

deptId	department	dept	lastName
4	Special Operations
3	Sales	3	Long
3	Sales	3	Craft
3	Sales	3	Watkins

Notice that the Special Operations department joined to a null row.

Exercises (Set 2)

Exercise \(\PageIndex{3}\)

Consider the Company database and list each department and the number of employees in the department.

Exercise \(\PageIndex{4}\)

Consider the Orders database.

1. Create a query to list each customer and their orders (order id and order date). Are there any customers who have not placed an order?
2. Modify the above query to list each customer and the number of orders they have placed (include all customers).

Exercise \(\PageIndex{5}\)

Consider the library database.

1. Create a query that will list every book and the date it was borrowed. Include all books in your result.
2. Create a query to list every library member and the dates they borrowed books. Include all members
3. Try creating a query that will list books that have never been borrowed.
4. Try creating a query to list any members who have not borrowed a book.

---

Cartesian Product

Suppose you create a query, but without a join criteria. This is easily done by clicking on the relationship line and deleting it. When criteria for matching rows is not present, then each row of one table will join to each row of the other table.

This type of join is called a Cartesian Product and these can easily have very large result sets. If Department has 4 rows and Employee has 100 rows then the Cartesian Product has (4x100=) 400 rows. Databases used in practice have hundreds, thousands, even millions of rows; a Cartesian Product may take a long, long time to run.

Exercises (Set 3)

Exercise \(\PageIndex{6}\)

Consider the Sales database and its Store and Product tables. Construct a query to list the storeID and the productID. When you add Store and Product to the relationships area there is a line joining the two tables. Delete the join line. Run the query. Notice how many rows there are; the number of rows in the result set is the number of stores times the number of products.Exercise \(\PageIndex{1}\)

Exercise \(\PageIndex{7}\)

Consider the Sales database and its Store, Product, and Sales tables. Suppose we want to obtain a list that shows for each store and product the total quantity sold. Note that the end user wants to see every store and product combination.

The second query is developed as a join between the query CP and the table Sales. CP is outer-joined to Sales in order that every combination of Store and Product is in the final result.

Hint

An approach you can use with MS Access is to create two queries. The first of these performs a cross product of store and product (call this CP).

---

Self-Join

A self-join, also called a recursive join, is a case where a table is joined to itself.

Consider the Company database and suppose we must obtain a list of employees who report to another employee named Raphael Delaney (i.e. List the employees Raphael Delaney supervises). To do this we need to find the row in Employee for Raphael Delaney and then join that row to other rows of Employee where the supervisor field is equal to the empId field for Raphael. When we build the query in MS Access we simply add the Employee table to the relationships area twice. One copy of Employee will be named Employee_1. Consider the following query:

Figure \(\PageIndex{8}\): Self-join.

Note the following:

• the criteria specifies the row in Employee will be that of Raphael Delaney
• the join line connects supervisor to empId and so rows of Employee_1 will be employees who report to Raphael.

Exercises (Set 4)

Exercise \(\PageIndex{8}\)

Consider the Genealogy database and develop queries to obtain:

1. the father of Peter Chan.
2. the mother of Peter Chan.
3. the father and mother of Peter Chan.
4. the children of Peter Chan.
5. the grandchildren of Peter Chan.

Exercise \(\PageIndex{9}\)

Consider the Orders database and the Employee table.

1. Write a query to list the employee who does not report to anyone.
2. Write a query to list each employee and the number of employees they supervise.

---

Anti-Join

Suppose we need to list persons in our Company database that are not supervising anyone. One way of looking at this problem is to say we need to find those people that do not join to someone else based on the supervises relationship. That is, we need to find those employees whose employee id does not appear in the supervisor field of any employee.

To do this with MS Access we can construct a query that uses an outer join to connect an employee to another employee based on employeeID equaling supervisor, but where the supervisor value is null. That is, we are looking for an employee who, in an outer join, does not join to another employee. See the query below:

Figure \(\PageIndex{9}\): Anti-join.

This query involves a join, specifically an outer join, and because it retrieves that rows that do not join, it is sometimes referred to as a special case – an anti-join.

Exercise \(\PageIndex{10}\)

Consider the Genealogy database and develop a query to find people that do not have any sons.

Exercise \(\PageIndex{11}\)

Consider the Genealogy database and develop a query to find people that do not have any daughters.

---

Non-Equi Join

A non-equi join is any join where the join criteria does not specify equals, “=”.

Suppose we wish to list all persons in the Genealogy database who are younger than, say, Peter Chan. One approach to getting the results is to join the row for Peter Chan to another row in Person where the birthdate of Peter Chan is greater than the birthdate of the other person. This type of join would be a “greater than” join as opposed to an equi-join. Proceed in the following way:

1. Add Person to the relationships area twice so there is a Person table and a Person_1 table. If there are any relationship lines delete them.
2. In the criteria line for Person fields: for firstName type “Peter” and for LastName type “Chan”.
3. In the criteria line for birthDate in Person_1 type “> [Person].[birthDate]”

   

   Figure \(\PageIndex{10}\): Non-equi join.

   In this way you are creating a “greater than” join.

4. Include attributes from Person_1 to display these younger people.
5. Run your query.

Exercises (Set 5)

Exercise \(\PageIndex{12}\)

Consider the genealogy database.

1. Run the example from above.
2. Modify the example to list those people who are older than Peter Chan.