Filter
Top Products
[Figure: Example of specifying conditions in WHERE clause for joining tables]
In Example 1, because the "COLA = COLZ" condition is specified in the WHERE clause, the only rows fetched are
those in which the values of columns COLA and COLZ are the same.
When multiple tables are specified in a FROM clause, columns with the same name may exist in more than one table.
To specify a column name that may exist in more than one table, qualify the column name by preceding it with a table
name and a period. For example, if the column name COLA exists in both tables TBL1 and TBL2, specify column
COLA in table TBL1 as "TBL1.COLA" or "SCM.TBL1.COLA." Similarly, specify column COLA in table TBL2 as
"TBL2.COLA" or "SCM.TBL2.COLA".
As an example of joining multiple tables, the following examines the status of orders mentioned at the beginning of
this chapter.
Example 2:
In this example, CUSTOMER, PRODUCT, and ORDERQTY are fetched from the ORDER table and
the STOCK table. The column names are specified with a table name qualifier. (In reality, the ORDER
table and the STOCK table do not contain columns with the same name, so table name qualifiers need
not be specified.) Table name qualifiers are only required when multiple tables specified in a FROM
clause contain columns with the same name.
SELECT ORDER. CUSTOMER, STOCK. PRODUCT, ORDER. ORDERQTY
FROM STOCKS. ORDER, STOCKS. STOCK
WHERE ORDER. PRODNO = STOCK. ITMNO
59