Joining Related Tables in a Query

A join retrieves related information from more than one table. To create a join in a query, you must designate the primary and foreign keys of the tables involved in the join. (We introduced these concepts in Chapter 1.) For example, consider two related tables, tblCustomer and tblOrder, having the fields shown.

TblCustomer

ID

FirstName

LastName

Address

City

State

PostalCode

Phone

Fax

E-mail

tblOrder

ID

CustomerID

OrderDate

Though tblOrder stores information about orders and tblCustomer stores information about customers, you'll likely want to retrieve a record set showing information about when each customer placed an order, producing output such as the following.

FirstName	LastName	OrderDate
Jane	Winters	9/10/2001
Jane	Winters	8/16/2001
Thurston	Ryan	7/2/2001
Dave	Martin	6/5/2001
Daisy	Klein	4/4/2001
…

Even though the data is stored in separate tables, retrieving a result set like this one is easy to do with a join. So long as your data design has specified that the primary key in tblCustomer (ID) is related to the foreign key (CustomerID) in tblOrder, the correct data will be returned.

Note

In this joined record set, one of the customers is displayed more than once, even though her name was entered in the database only once. This result reflects the fact that she has placed multiple orders. It's a nice feature because you never have to enter the same customer's data in the database twice, but it sometimes means that you get more information back in a query than you want. A variety of tactics may be used for handling this situation, which we discuss later in this chapter.

Expressing a Join in SQL

In SQL Server, you set up a join as an expression of equivalence between two fields, as in

 SELECT FirstName, LastName, OrderDate  FROM tblOrder INNER JOIN tblCustomer ON tblOrder.CustomerID = tblCustomer.ID 

This SQL returns information on all the customers who have related orders in tblOrder. It returns three columns of data the FirstName and LastName fields from tblCustomer and the OrderDate field from tblOrder.

Note that, in a query that includes a join, when the same field appears in two tables, you must include a reference to the base table along with a field name (such as tblOrder.ID rather than simply ID) to denote which table you're talking about. Fortunately, in most cases when you're using the View Designer in VS.NET to create your query, the development environment figures out what you want to do and fills in the missing parts for you automatically. As you've seen already, the examples presented in this book generally include the most concise possible SQL syntax, except where more specificity is required.

Using the View Designer to Create Joins

Because creating joins can be the most complicated part of queries particularly when more than two tables are involved you might find it useful to have some help when creating them. Fortunately, you can use VS.NET's View Designer to create a query comprising a join between multiple tables. Using the designer means that you don't have to memorize complicated SQL join syntax instead, you can create the join graphically, as follows.

1. In Server Explorer, create a new view in the Novelty database.

2. The Add Table dialog appears. Add tblCustomer and tblOrder to your view and then click on Close. The diagram pane of the View Designer window should look like that shown in Figure 2.5.

   Figure 2.5. Creating a join between two tables in the View Designer window

   

Note that the View Designer automatically creates a join between the two tables. The View Designer knows that the primary key field named ID in tblCustomer is related to the CustomerID field in the tblOrder because the relationship between the two tables was explicitly defined when the database was created.

Running the query returns data based on the relationship between customers and orders, as shown in Figure 2.6.

Figure 2.6. A joined query in the View Designer window after it has returned data

Using Outer Joins to Return Additional Data

A conventional join returns records from two tables in which a value in one table's primary key matches a value in a related table's foreign key. But suppose that you want to return all the records on one side of a join whether or not there are related records? In this case, you must use an outer join. For example, the following query lists customers and orders, including customers who do not have any orders outstanding:

 SELECT FirstName, LastName, OrderDate  FROM tblCustomer LEFT OUTER JOIN   tblOrder ON tblCustomer.ID = tblOrder.CustomerID 

Note the tablename.fieldname syntax used in the LEFT JOIN clause. This long name is used to avoid ambiguity because the ID field exists in both tblCustomer and tblOrder. Because it's a LEFT JOIN, the table on the left side of the expression tblCustomer.ID = tblOrder.CustomerID is the one that will display all its data. This query returns the following result set.

FirstName	LastName	OrderDate
John	Smith	1/4/2001
John	Smith	1/9/2001
Jill	Azalia	1/14/2001
Brad	Jones	<NULL>
Daisy	Klein	2/18/2001
Daisy	Klein	3/21/2001
Daisy	Klein	4/4/2001
Dave	Martin	6/5/2001
Betty	Klein	<NULL>
Thurston	Ryan	7/2/2001
Jane	Winters	8/16/2001
Jane	Winters	9/10/2001

This result set comprises all the customers in the database whether or not they have outstanding orders. For those customers without orders, <NULL> appears in the OrderDate field. Null is a special state indicating the absence of data.

There also are right joins. The difference between a left join and a right join simply has to do with which table is named first in the join. (Both left joins and right joins are types of outer joins and both can return identical result sets.)