Typically, you have to select data from two or more tables to retrieve complete data for your report. This operation is called a join. You join tables in a database through a common column called a key.

For example, suppose you want to retrieve the orders for every customer. The database, however, stores customer information in a Customers table, and order information in an Orders table, as shown in Figure 4‑32. Both tables contain a column called CustomerID. You can join the customers and the orders table using the CustomerID column.

To retrieve order information for every customer, use the following SELECT statement:

The WHERE clause in this example specifies that the query returns rows where the CustomerID in both tables match. Figure 4‑33 shows the results that the SELECT statement returns.

Alternatively, use the JOIN keyword to select data from the two tables. The rest of this section describes the different types of joins you can use, and the results that each join returns. The following SELECT statement uses INNER JOIN and returns the same results shown in Figure 4‑33:

The INNER JOIN clause returns all rows from both tables where the two CustomerID fields match. If there are rows in the Customers table that do not match rows in the Orders table, those rows are not listed. In the example, Patrick Mason is not listed in the result set because this customer does not have a matching order.

To obtain all the customer names, whether or not a customer has an order, use the LEFT JOIN clause, as shown in the following example:

LEFT JOIN returns all rows from the first (left) table, even if there are no matches in the second (right) table. Figure 4‑34 shows the results of the SELECT statement that uses the LEFT JOIN clause. Here, Patrick Mason is listed in the result set even though he does not have an order, because the record is in the first table.

Conversely, to retrieve all rows from the second table (the Orders table in our example), even if there are no matches in the first table (the Customers table), use the RIGHT JOIN clause, as shown in the following example:

In our example, all the rows in the second table match rows in the first table, so the result is the same as in Figure 4‑33. If, however, the Orders table had contained rows that did not have matches in the Customers table, those rows would also have been returned.

To retrieve all customer names and orders from both tables, even if there are no matching values, you can use the FULL OUTER JOIN clause, as shown in the following example:

In our example, the result is the same as in Figure 4‑34. All the customer names and all the order amounts are returned. Some databases do not support FULL OUTER JOIN. In most cases, you can get the same results using the UNION operator.

Note that in all the examples, the SELECT statements specify the columns being joined: Customers.CustomerID and Orders.CustomerID. You must specify the columns to join. If you do not, the result is what is commonly referred to as a cartesian join. In a cartesian join, all rows in the first table are joined with all rows in the second table. If the first table has 1000 rows and the second table has 10,000 rows, the cartesian join returns 10,000,000 rows, a result you rarely want.

The inner, left, and right joins are the most common types of joins. For more information about these joins and others that your database supports, see the database manufacturer’s documentation.