More often than not, you will need to relate together the records from more
than one table in a SQL statement
Using the Where Clause to Connect Tables
You can use the Where clause to bring data from more than one table into the result set of a query.
You can specify more than one table's data in the result set of a query by just specifying
the field names from each table and the table names in the basic syntax of the Select statement.
You will always want to explicitly specify the relation between the tables with either a Where
clause or a Join clause, however, for the combination of the data from the two tables to be meaningful.
Assume, for example, that you would like to see a list of every order's date from an
Orders table in your database along with the customer name for each order. Customer names are held in
a Customers table and the Orders table contains a CustomerID field that keys to the CustomerID
field in the Customers table. An example of a Select clause that would properly return the information
you are seeking would read as follows:
Select [Order Date], [Customer Name] From Orders, Customers Where Orders.[Customer
ID] = Customers.[Customer ID]
If it didn't include the Where clause, the Select statement would return a Cartesian
product of the two tables—that is, it would match every record in Orders with every customer
and return a huge, meaningless result set! Note that you don't have to specify which table
a field comes from so long as that field's name is unique within the tables you have specified
in the From clause. If a field's name isn't unique within the tables used in the query, specify
its originating table with the tablename.fieldname syntax.
Using JOIN Clauses to Connect Tables
You can create a multitable Recordset with a query that uses a JOIN clause. There are two types
of JOIN, and these two JOIN types are implemented by three different possible JOIN clause types.
These types of JOIN and the clauses that implement them are as follows:
-
An equi-join or inner join. This type of join creates records in a result
set only when there are matching records from both tables. You can use an INNER JOIN clause
to create an equi-join.
-
An outer join. Result sets created using this type of join contain all
the records from a specified master table and only those
records from a related lookup table that match the records in the master table. You can implement
an outer join with either the LEFT JOIN or RIGHT JOIN clause. The difference between these two
types of join is the order in which you specify the master and lookup tables.
To specify an equi-join between the Customers table and the Orders table, write a query as in
the following example:
Select [Company Name], [Order Date] From Customers
INNER JOIN Orders ON Customers.[Customer ID] =
Orders.[Customer ID]
The result set would contain only matching information from the Customers and Orders tables.
If you wanted to display a list of customers and the dates of their orders, but you wanted
to include even customers without any orders, you could specify this result set with a LEFT
JOIN, as in the following example:
Select [Company Name], [Order Date] From Customers
LEFT JOIN Orders ON Customers.[Customer ID] =
Orders.[Customer ID]
You could achieve the same effect with a RIGHT
JOIN clause, as follows:
Select [Company Name], [Order Date] From Orders
RIGHT JOIN Customers ON Orders.[Customer ID] =
Customers.[Customer ID]
