Joining tables using key joins

Key joins are an easy way to join tables related by a foreign key. For example:

SELECT emp_lname, id, order_date 
FROM sales_order 
KEY JOIN employee

gives the same results as a query with a WHERE clause that equates the two employee ID number columns:

SELECT emp_lname, id, order_date
FROM sales_order, employee 
WHERE sales_order.sales_rep = employee.emp_id

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 asiqdemo 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, sales_order, sales_order_items and product single foreign-key relationships between each pair of these tables.

SELECT company_name, 
CAST( SUM(sales_order_items.quantity * 
product.unit_price) AS INTEGER) AS value 
FROM customer 
KEY JOIN sales_order 
KEY JOIN sales_order_items 
KEY JOIN product 
GROUP BY company_name

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.