User's Guide

Part II. Tutorials

Chapter 8. Joining Tables

Many common joins are between two tables related by a foreign key. The most common join restricts foreign key values to be equal to primary key values. The example you have already seen restricts foreign key values in the sales_order table to be equal to the primary key values in the employee table.

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

The query can be more simply expressed using a KEY JOIN.

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 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.

If you look at the diagram of the employee database, 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.

Joining two or more tables

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 using the lines between the 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

company_name	value
Able Inc.	6120
AMF Corp.	3624
Amo & Sons	3216
Amy's Silk Screening	2028
Avco Ent	1752
...

The CAST function used in this query converts the data type of an expression.

Joining tables using natural joins

The NATURAL JOIN operator joins two tables based on common column names. In other words, SQL Anywhere generates a WHERE clause that equates the common columns from each table.

Example

For example, for the following query:

     SELECT emp_lname, dept_name
     FROM employee
     NATURAL JOIN department

the database engine looks at the two tables and determines that the only column name they have in common is dept_id. The following WHERE clause is internally generated and used to perform the join:

     ...

     WHERE employee.dept_id = department.dept_id

Errors using NATURAL JOIN

This join operator can cause problems by equating columns you may not intend to be equated. For example, the following query generates unwanted results:

     SELECT *
     FROM sales_order
     NATURAL JOIN customer

The result of this query has no rows.

The database engine internally generates the following WHERE clause:

     WHERE sales_order.id = customer.id

The id column in the sales_order table is an ID number for the order . The ID column in the customer table is an ID number for the customer . None of them matched. Of course, even if a match were found, it would be a meaningless one.

You should be careful not to use join operators blindly. Always remember that the join operator just saves you from typing the WHERE clause for a foreign key or common column names. You should be conscious of the WHERE clause, or you may be creating queries that give results other than what you intend.