Join the World

By ServerWatch Staff (Send Email)
Posted Mar 11, 2001


by David FaourWhen accessing data, we often want to answer questions that requires input from multiple tables. While there are many ways to accomplish the task, the use of proper join techniques, depending upon your question, will ensure that you are getting the answers you want.

When accessing data, we often want to answer questions that requires input from multiple tables. While there are many ways to accomplish the task, the use of proper join techniques, depending upon your question, will ensure that you are getting the answers you want.

 The most commonly used join types are inner-joins, and outer-joins. An inner-join may also be called a natural-join or an equi-join.  Inner joins will return only those rows where there is a match in both tables. An outer join will return all of the rows in table1, and only the matching information from table2.

 So, how would we normally use these? Consider the following problem. You are a sales manager, and you want to know which customers have ordered from you in the thirty days, so that you can send a thank you note. The code would look something like the following.

 

Select C.[Field List], O.[Field List] from Orders O join Customers C on O.OrderDate>Today-30 and O.CustomerID=Customers.CustomerID

 

There are several things going on here. First I have given the tables the aliases O and C. To alias a table, the syntax is as follows select alias.field from tablename alias where...and so on. Second, the Orders table is the most limiting factor, I am only interested in customers with matching records in the orders table, and the orders must have been placed in the last 30 days. Because the Orders table is the most limiting, I am putting it first in the table list. I have also placed the O.OrderDate>Today-30 requirement in the join clause. Assume I had written the code as follows

select fields

from O

join C

on O.customerID=C.CustomerID

where O.OrderDate>Today-30       

This would not be efficient. Customers that had placed an order at ANY time would be joined onto the orders table, and then the rows would be filtered for date.

 In the first example, the rows are eliminated for date before joining the customers.

Page 1 of 2


Comment and Contribute

Your name/nickname

Your email

(Maximum characters: 1200). You have characters left.