Joining tables using key joins

Key joins are an easy way to join tables related by a foreign key. The following example returns the same results as a query with a WHERE clause that equates the two employee ID number columns:

SELECT Surname, 
  EmployeeID,
  OrderDate 
FROM SalesOrders 
KEY JOIN Employees

SELECT Surname,
  EmployeeID, 
  OrderDate
FROM SalesOrders, Employees 
WHERE SalesOrders.SalesRepresentative = Employees.EmployeeID

The join operator (KEY JOIN) is just a short cut for typing the WHERE clause; the two queries are identical.

In the diagram of the iqdemo database, in Introduction to Sybase IQ, foreign keys are represented by lines between tables. Anywhere that two tables are joined by a line in the diagram, you can use the KEY JOIN operator. Remember that your application must enforce foreign keys in order to ensure expected results from queries based on key joins.

Two or more tables can be joined using join operators. The following query uses four tables to list the total value of the orders placed by each customer. It connects the four tables customer, SalesOrders, SalesOrderItems and Products single foreign-key relationships between each pair of these tables.

SELECT CompanyName, 
  CAST( SUM(SalesOrderItems.Quantity * 
  Products.UnitPrice) AS INTEGER) AS Value 
FROM Customers 
KEY JOIN SalesOrders 
KEY JOIN SalesOrderItems 
KEY JOIN Products 
GROUP BY CompanyName

The CAST function used in this query converts the data type of an expression. In this example the sum that is returned as an integer is converted to a value.