User's Guide

Part VI. SQL Anywhere Reference

Chapter 43. Watcom-SQL Statements

Function

To specify the database tables or views involved in a SELECT or UPDATE statement.

Syntax

     ... FROM table-expression, ...

Parameters

     table-expression:
          table-spec
          | table-expression join-type table-spec [ ON condition ]
          | ( table-expression, ... )

     table-spec:
          [userid . ] table-name [ [AS] correlation-name ]

     join-type:
          CROSS JOIN
          | [ NATURAL | KEY ] JOIN
          | [ NATURAL | KEY ] INNER JOIN
          | [ NATURAL | KEY ] LEFT OUTER JOIN
          | [ NATURAL | KEY ] RIGHT OUTER JOIN

Usage

Anywhere.

Permissions

Must be connected to the database.

Side effects

None.

See also

• SELECT statement
• UPDATE statement
• DELETE statement
• "Search conditions"

Description

The SELECT and UPDATE statements require a table list to specify which tables will be used by the statement.

Views Although this description refers to tables, it applies to views unless otherwise noted.

The FROM table list creates a result set consisting of all the columns from all the tables specified. Initially, all combinations of rows in the component tables are in the result set, and the number of combinations is usually reduced by join conditions and/or WHERE conditions.

Tables owned by a different user can be qualified by specifying the user ID. Tables owned by groups to which the current user belongs will be found by default without specifying the user ID (see "Referring to tables owned by groups").

The correlation name is used to give a temporary name to the table for this SQL statement only. This is useful when referencing columns which must be qualified by a table name but the table name is long and cumbersome to type. The correlation name is also necessary to distinguish between table instances when referencing the same table more than once in the same query. If no correlation name is specified, then the table name is used as the correlation name for the current statement.

If the same correlation name is used twice for the same table in a table expression, that table is treated as if it were only listed once. For example, in

     SELECT *
     FROM sales_order
     KEY JOIN sales_order_items,
     sales_order
     KEY JOIN employee

the two instances of the sales_order table are treated as one instance, and is equivalent to

     SELECT *
     FROM sales_order_items
     KEY JOIN sales_order
     KEY JOIN employee

whereas

     SELECT *
     FROM Person HUSBAND, Person WIFE

would be treated as two instances of the Person table, with different correlation names HUSBAND and WIFE.

Joining tables

A JOIN reduces the result set based on the join type and join condition. The join types are described below and the join condition is specified after the keyword ON.

Parentheses can also be used to join one table to more than one other table. For example:

     A JOIN (B,C)

joins table A to both tables B and C.

Table expressions can be arbitrarily complex. For example,

     A JOIN B JOIN C

     A JOIN ( B, C JOIN D )

are legal and meaningful table list expressions. (Any of the valid join types could have been used in the above examples.)

Cross joins

A CROSS JOIN does not restrict the results of the join. The query

     SELECT *
     FROM table1
     CROSS JOIN table2

has a result set as follows:

• As long as table1 is not the same name as table2:
  • The columns of the result set includes all columns in table1 and also all columns in table2.
  • There is one row in the result set for each combination of a row in table1 and a row in table2. If table1 has n1 rows and table2 has n2 rows, the query returns n1 * n2 rows.
• If table1 is the same table as table2, and neither is given a correlation name, the result set is simply the rows of table1.

Generated join conditions

Natural joins and key joins are generated join conditions: that is, the keyword KEY or NATURAL indicates a restriction on the join results.

For a natural join, the generated join condition is based on the names of columns in the tables being joined; for a key join, the condition is based on a foreign key relationship between the two tables.

Natural joins

A NATURAL JOIN restricts the results by comparing the values of columns in the two tables with the same column name. An error is reported if there are no common column names. A join condition can optionally be specified which further restricts the results of the join.

Column names such as Description or Address often cause a NATURAL JOIN to return different results than expected.

Key joins

A KEY JOIN restricts the result set based on a foreign key relationship between the two tables. A join condition can optionally be specified which further restricts the results of the join. A key join is valid if exactly one foreign key is identified between the two tables; otherwise, an error indicating the ambiguity is reported.

Parentheses can also be used to join one table to more than one other table. For example

     A KEY JOIN (B,C)

joins table A to both tables B and C.

A join involving parentheses is valid if there is an unambiguous join for each table listed in the parentheses.

A KEY JOIN with a view is valid if there is a valid KEY JOIN with exactly one of the tables in the FROM table list of the view definition.

There are two conditions where the meaning of a KEY JOIN is ambiguous. In the first condition, there are two tables A and B where A has a foreign key for B and B has a foreign key for A. The second condition occurs when a table A has two foreign keys for a table B. In either case, the primary table must have a correlation name which is the same as the role name of the foreign key. Otherwise an error will be reported. For example, every SQL Anywhere database has a table called SYSTABLEPERM to hold permission information. Every row in the permission table has two foreign keys for the table describing user IDs (SYSUSERPERM). One foreign key is for the user ID giving the permission (role name grantor) and the other is the user ID getting the permission (role name grantee). A KEY JOIN for the grantor would look like the following:

     SYSUSERPERM grantor
     KEY JOIN SYSTABLEPERM

and a KEY JOIN for both would look like:

     SYSUSERPERM grantor
     KEY JOIN SYSTABLEPERM
     KEY JOIN SYSUSERPERM grantee

INNER JOIN and OUTER JOIN

All joins described thus far have been INNER JOINs, which is the default. Each row of

     customer INNER JOIN sales_order

contains the information from one customer row and one sales_order row. If a particular customer has placed no orders, there will be no information for that customer.

     A LEFT OUTER JOIN B

includes all rows of table A whether or not there is a row in B that satisfies the join condition. If a particular row in A has no matching row in B, the columns in the join corresponding to table B will contain the NULL value. Similarly,

     A RIGHT OUTER JOIN B

includes all rows of table B whether or not there is a row in A that satisfies the join condition.

Join conditions

A join condition can be specified for any join type except CROSS JOIN. The simplest form of join condition is to use it instead of using a KEY or NATURAL join. In the sample database, the following are equivalent:

     SELECT *
     FROM sales_order
     JOIN customer
     ON sales_order.cust_id = customer.id
     SELECT *
     FROM sales_order
     KEY JOIN customer

The following two are also equivalent:

     SELECT *
     FROM department
     JOIN employee
     ON department.dept_id = employee.dept_id
     SELECT *
     FROM department
     NATURAL JOIN employee

With INNER joins, specifying a join condition is equivalent to adding the join condition to the WHERE clause. However, this is not true for OUTER joins.

A join condition on an OUTER join is part of the join operation. For example, the statement:

     SELECT *
     FROM employee
     KEY LEFT OUTER JOIN Skill
     ON skill_name = 'COBOL'

produces a table containing all employees whether or not they have the skill COBOL. Those who do not have the skill COBOL will have the NULL value in the Skill columns. On the other hand, the query:

     SELECT *
     FROM employee
     KEY LEFT OUTER JOIN Skill
     WHERE skill_name = 'COBOL'

can be thought of as two separate stages.

1. The first stage creates the table specified by the FROM clause: SELECT * FROM employee KEY LEFT OUTER JOIN Skill which has at least one row for each employee. Employees who do not have the skill COBOL will have the NULL value in the Skill table columns. For all other employees, there will be one row for each of their skills.
2. The second stage takes this result and applies the condition:

        WHERE skill_name = 'COBOL'
        
   

   which removes employees without any skills (since the condition is UNKNOWN), and only keeps the skill COBOL for the other employees. Thus, the result has no rows with the NULL value in the Skill columns, so it is clearly different from the result achieved using the join condition.

OUTER joins with join conditions can be complicated. If you are having problems using a join condition, try removing the WHERE clause from the statement to verify that the join is retrieving the rows you expected.

Join abbreviations

SQL Anywhere provides the following abbreviations for joins.

table1 [INNER | LEFT OUTER | RIGHT OUTER] JOIN table2 If there is no ON join condition specified, the join is assumed to be a KEY join. All key joins are a SQL Anywhere extension. Note that KEY JOIN is not assumed if a join condition is specified.

table1 JOIN table2 ON join-condition The default join type is an INNER JOIN.

Examples

The following are valid FROM clauses:

     ... FROM employee

     ... FROM employee NATURAL JOIN department

     ... FROM customer
     KEY JOIN sales_order
     KEY JOIN sales_order_items
     KEY JOIN product