6. MICROSOFT ACCESS QUERIES – ADVANCED
6.8.2: 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:
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:
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:
For our purposes here, we added the Special Operations department to Department. And now the first few rows of the result are:
Notice that the Special Operations department joined to a null row.
Exercises
1) Consider the Company database and list each department and the number of employees in the department.
2) Consider the Orders database:
- 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?
- Modify the above query to list each customer and the number of orders they have placed (include all customers).
3) Consider the library database:
- Create a query that will list every book and the date it was borrowed. Include all books in your result.
- Create a query to list every library member and the dates they borrowed books. Include all members
