How do I use 'joins' in my Access database?

Joins, Joins, and More Joins

The ability to join two or more tables and extract the data in them is one of the most powerful features of any relational database. Knowing how to create the different types of joins, and when you would want to use them, can go a long way towards making your database application even more useful.

There are basically three types of joins û inner, outer and cross joins. Cross joins are aptly named, because if you try to perform one on a large database, your users and systems programmers will get very cross at you. A cross join merges two tables on every record in a geometric fashion û every record of one table is combined with every record from the other table. Two tables of 100 records each in a cross join will create a table of 10000 (100 x 100) records. Imagine the result set with tables of 20,000 or 30,000 records!

So weÆll ignore the cross join for the moment and concentrate on the other two.

An INNER join is the most common type of join, and is used to match two tables based on values of a common field. It can be thought of as the INTERSECTION of the two sets of data. Remember Venn diagrams? Two interlocking circles û the INNER join is the part of the two circles thatÆs in between BOTH of them.

This is the kind of Join that Access defaults to û when you drag a join line from CLIENTS to ORDERS based on CLIENT ID, you get a set of ONLY those clients who have placed orders.

The OUTER join is a type of join that returns ALL of the members of one set, and ANY matching members of the other set. In our Venn diagram, its ONE ENTIRE circle, and the part of the other circle that overlaps.

To get this type, join our mythical Clients table to the equally mythical Orders table, but right-click the JOIN line and select type #2 û ALL clients, and any orders that match. So you will see those clients who have not placed an order, as well as those who have.

So far, so good. This is easier than you thought, isnÆt it?

But the real power of joins is that they let you dig even deeper into the mysteries of your database, to show you whatÆs NOT there. (What? I hear you ask...stick with me.)

LetÆs assume for the sake of argument that you have a Client / Orders type of system. Just two tables, with a one-to-many relationship. You havenÆt built in referential integrity, either because it is inappropriate, or you donÆt know what it means. (ThatÆs ok, IÆll explain that too, and tell you when you WANT it, and when you donÆt.) If the data in the two tables is generated independently, there are many questions you can answer with the correct kind of join.

For example, ôGive me a list of clients who have NEVER placed an orderö. You might try doing this with a standard inner join and a count of Orders = 0, but this isnÆt exactly correct. You would get NO records û the join can only grab those clients for whom there is at least one matching order record. Your result set would be NULL.

As a side note, itÆs very important that you understand the difference between NULL and zero û null records just donÆt exist. If you understand that, you can see how what you really need is a join where the ORDER records are NULL.

Create a #2 type join (ALL CLIENTS), but place a criteria on the key field of your ORDERS table of ôIS NULLö. This will give you a list of ALL the clients who do not have a matching record in the orders table. In our Venn Diagram, this is the ôouterö part of our left circle (clients) that does not overlap with any part of the right circle (orders). Some people call this a ôleft outer joinö, because itÆs only the part of the left circle thatÆs not matched by anything in the right circle.

The key here is the use of IS NULL as a criteria on the RIGHT side of the join. YouÆre in effect saying, ôShow me a list of all the clients for which there is NO MATCH in the Orders table.ö

Depending on the structure of your database, you can also turn this join around, to show any orders that you donÆt have a client for anymore (you deleted the client record, but his order is still out there) This kind of situation is a bit more rare, with the advent of referential integrity, but it could show up. In this case, just draw your join line from ORDERS to CLIENTS and set the Join to #2, ôALL ORDERS and any MATCHING clientsö, and then set your CLIENT key criteria to ôIS Nullö. YouÆll get a list of the orders for which there is no matching client. A RIGHT outer join

As you can see, setting the appropriate join type and using the IS NULL constraint can show you a lot about your database that you may not have thought possible.

A note on Referential Integrity

Referential Integrity is a constraint you can place on a join that forbids entering data on the æmanyÆ side of a relationship if there is no matching æoneÆ side record. In other words, do not let me enter an order for a customer number that does not exist in the customer table already. This is usually a good idea, as it helps to keep your database accurate.

Another feature of Referential Integrity is the capability of a relational database to æcascadeÆ changes from one table to another. It involves both record changes and record deletions.

Using referential integrity, for example, on our clients and orders database would mean that if we were to delete a record on the ONE side (a client ), the database would automatically go and delete all of the MANY side (orders) records that match. This could be a very useful option, and would remove the need for ever having to do a right outer join such as I just explained. There would never be any æorphanÆ orders.

Referential integrity can also be used to UPDATE records that have changed. If you change the VALUE of the one-side matching field, it finds the matches on the MANY side and updates them. For example, you change or re-assign a customer number, from ô27ö to ôA270ö. This operation would then go in to the orders table, and find all of the old ô27ö values and change them to match the new ôA270ö customer number value. Again, this also removes the possibility of creating ôorphanö many-side records.

Referential Integrity is a very useful database option, but you will need to determine in which relationships it is appropriate.