Macromedia Dreamweaver 8 UNLEASHED [Electronic resources]

Joins

Up to this point, you have focused primarily on extracting data from a single table. Depending on how advanced your database becomes, at times you might want to extract data from multiple tables at once. If that is the case, you will need to use

joins . Although there are several types of joins, two types will be covered here:

• Inner joins

• Outer joins

Inner Joins

Of the different types of joins, inner joins are by far the most popular. Inner joins enable you to see all the records of two tables that have a relationship established. Remember that the Employees and the CreditCards tables have a one-to-many relationship established. The two tables in the Dorknozzle database resemble the following:

Assume that you wanted to extract the information from the Employees table for EmployeeID 2. If not for inner joins, you would have to use two SELECT statements as follows:

SELECT * 
FROM Employees 
WHERE EmployeeID = 2

and

SELECT * 
FROM CreditCards 
WHERE EmployeeID = 2

You can begin to see how tedious this could get, not to mention that it is completely inefficient. To solve this problem, an INNER JOIN could be used as follows:

SELECT 
Employees.EmployeeID, Employees.Name, 
CreditCards.Type, CreditCards.Number
FROM Employees 
INNER JOIN CreditCards 
ON Employees.EmployeeID = CreditCards.EmployeeID

The join effectively produces one virtual table with the following results:

Notice that the preceding table now becomes more efficient and manageable. Also notice that, rather than referencing the names of the tables, you used the TableName.Field notation. This is crucial when using joins; otherwise, you would end up with two EmployeeIDs without a direct reference to its corresponding table.

NOTE

Note the use of the ON operator in the preceeding SQL INNER JOIN statement. The ON operator instructs the SQL statement to join two tables on a specific primary and foreign key pairing.

Outer Joins

Outer joins enable rows to be returned from a join in which one of the tables does not contain matching rows for the other table. Suppose that you have two tables that contain the following information:

Consider the following INNER JOIN statement, issued on the preceding tables:

SELECT 
Employees.Name, Employees.AddressID, 
Address.AddressID, Address.Address 
FROM Employees
INNER JOIN Address 
ON Employees.AddressID = Address.AddressID

It returns the following results:

Notice that the record that did not contain an AddressID was excluded. Now consider the following OUTER JOIN statement:

SELECT
Employees.Name, Employees.AddressID, 
Address.AddressID, Address.Address 
FROM Employees
OUTER JOIN Address ON Employees.AddressID = Address.AddressID

The results of this statement are slightly different:

As you can see, in the case of the OUTER JOIN, all data is returned, even if no address is present for Cammy.