Restricting a join

First Guide to SQL Anywhere Studio
PART 3. Basic SQL
CHAPTER 13. Joining Tables

Restricting a join

The most natural way to make a join useful is to insist that the sales_rep in the sales_order table be the same as the one in the employee table in every row of the result. Then each row contains information about an order and the sales rep responsible for it.

Example 1

To do this, add a ON phrase to the previous query to show the list of employees and their course registrations:

SELECT *
FROM sales_order JOIN employee
   ON sales_order.sales_rep = employee.emp_id

The table name is given as a prefix to identify the columns. Although not strictly required in this case, using the table name prefix clarifies the statement, and is required when two tables have a column with the same name. A table name used in this context is called a qualifier.

The results of this query contain only 648 rows (one for each row in the sales_order table). Of the original 48,600 rows in the join, only 648 of them have the employee number equal in the two tables.

Example 2

The following query is a modified version that fetches only some of the columns and orders the results.

SELECT employee.emp_lname, sales_order.id,
   sales_order.order_date
FROM sales_order JOIN employee
   ON sales_order.sales_rep = employee.emp_id
ORDER BY employee.emp_lname

If there are many tables in a SELECT command, you may need to type several qualifier names. This typing can be reduced by using a correlation name.

Correlation names

A correlation name is an alias for a particular instance of a table. This alias is valid only within a single statement. You create a correlation name by putting a short form for a table name immediately after the table name, separated by the word AS. You then must use the short form as a qualifier instead of the corresponding table name.

SELECT e.emp_lname, s.id, s.order_date
FROM sales_order AS s JOIN employee AS e
   ON s.sales_rep = e.emp_id
ORDER BY e.emp_lname

Here, two correlation names s and e are created for the sales_order and employee tables.

If you change e.emp_id back to employee.emp_id, Adaptive Server Anywhere reports an error. If you make a correlation name for an instance of a table, you must use that correlation name when qualifying which table a column is from; you cannot use the original table name anymore.

For Info For a further application of correlation names, see Self joins.