SQL/MX Report Writer Guide

Selecting Data for a Report
HP NonStop SQL/MX Report Writer Guide—527194-002
3-4
Joining Tables
The FROM clause of the SELECT command specifies the names of all tables from
which the SELECT command retrieves data. For example, to retrieve values from the
PARTS table, enter:
>> SELECT * FROM PARTS;
An asterisk in the select list retrieves a value for each column of the table. The table
you specify must be on the current default catalog and schema.
Joining Tables
To select the data needed for the report in Figure 3-1, you must join rows of the three
tables, using the WHERE clause. First, consider the joining of rows from the PARTS
and PARTSUPP tables that have the same PARTNUM value:
>> SELECT *
+> FROM SAMDBCAT.SALES.PARTS, SAMDBCAT.INVENT.PARTSUPP
+> WHERE SAMDBCAT.SALES.PARTS.PARTNUM = SAMDBCAT.INVENT.PARTSUPP.PARTNUM;
Note that the tables are in different schemas, so the table names must be qualified with
the catalog and schema name.
Using the WHERE Clause
The WHERE clause specifies how the rows are to be joined. In Figure 3-2, rows with
the same part number in each table are joined.
The expression in the WHERE clause is called a comparison predicate.
If you omit the WHERE clause, each row of the PARTS table is joined with each row of
the PARTSUPP table. Because this approach results in an inefficient query, you
should include a WHERE clause with a predicate to indicate how to join the tables in
the FROM clause.
Figure 3-2 illustrates the result of joining these tables. Suppose that PARTS contains
two rows and PARTSUPP contains five rows.