To retrieve information from multiple tables, there must be a link between the tables. This is commonly known as the key field. It is a unique field within each record. For example, in our telephone address book example, the key field would be telephone number. For Access or other database program to link two tables together, it must create a link between these key fields which must be of the same data type in both tables.
Joins
There are several types of links or joins.
Equal join pulls records that have a match in both tables.
For example, if you were pulling information from a Customer Table and an Order table, if Customer A was in your customer table but had not placed an order, no information about Customer A would display but the information for Customer B would print since there is a matching record in both tables.
Left join pulls all records from the left (first) table if there are no matching records in the right table. In this case Client C would not display since he cannot be found in the order table. Conversely, if you were pulling from the right table, no information about Client D would appear since that company is not in your Client table.
Right join pulls all records from the right table and only matching records in the left table.
If tables in a query aren't joined to one another, either directly or indirectly, the database doesn't know which records are associated with which, so it displays every combination of records between the two tables. Therefore, if each table had 10 records in it, the query's results will contain 100 records (10X10). This result set of every possible combination is called a cross product or Cartesian product. This type of query can take a long time to run.
The point of this explanation is that you must consider what information you actually want and then decide upon which join to use. Equal joins are the most common however if you do need to use an outer join, make sure you place the tables in the correct order.
No comments:
Post a Comment