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:

EmployeeID

Name

1

Ada

2

Agnes

3

Cammy

4

Dave

...

...


EmployeeID

Type

Number

1

Visa

6345438789678076

2

Visa

3456878097356256

3

Visa

3276798579737256

4

Visa

4357568356245244

...

...

...


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:

EmployeeID

Name

Type

Number

1

Ada

Visa

6345438789678076

2

Agnes

Visa

3456878097356256

3

Cammy

Visa

3276798579737256

4

Dave

Visa

4357568356245244

...

...

...

...


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:

EmployeeID

Name

AddressID

2

Ada

45634

4

Agnes

34754

5

Cammy

 

10

Dave

97895

...

...

...


AddressID

Address

45634

555 Sample St., San Diego

34754

343 Chestnut Rd., San Diego

97895

523 Main St., San Diego

...

...


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:

Name

AddressID

Address

Ada

45634

555 Sample St., San Diego

Agnes

34754

343 Chestnut Rd., San Diego

Dave

97895

523 Main St., San Diego


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:

FirstName

AddressID

Address

Ada

45634

555 Sample St., San Diego

Agnes

34754

343 Chestnut Rd., San Diego

Dave

97895

523 Main St., San Diego

Cammy

  


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




Macromedia Dreamweaver 8 Unleashed
Macromedia Dreamweaver 8 Unleashed
ISBN: 0672327600
EAN: 2147483647
Year: 2005
Pages: 237
Authors: Zak Ruvalcaba

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net