SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—540440-003
2-200
MXCI Examples of SELECT
•
Select data from more than one table by specifying the table names in the FROM
clause and specifying the condition for selecting rows of the result in the WHERE
clause:
SELECT jobdesc, first_name, last_name, salary
FROM persnl.employee E, persnl.job J
WHERE E.jobcode = J.jobcode AND
E.jobcode IN (900, 300, 420);
JOBDESC FIRST_NAME LAST_NAME SALARY
------------ ------------ --------------- -----------
SALESREP TIM WALKER 32000.00
SALESREP HERBERT KARAJAN 29000.00
...
ENGINEER MARK FOLEY 33000.00
ENGINEER MARIA JOSEF 18000.10
...
SECRETARY BILL WINN 32000.00
SECRETARY DINAH CLARK 37000.00
...
--- 27 row(s) selected.
This type of condition is sometimes referred to as a join predicate. The query first
joins the EMPLOYEE and JOB tables by combining each row of the EMPLOYEE
table with each row of the JOB table; the intermediate result is the Cartesian
product of the two tables.
This join predicate specifies that any row (in the intermediate result) with equal job
codes is included in the result table. The WHERE condition further specifies that
the job code must be 900, 300, or 420. All other rows are eliminated.
The four logical steps that determine the intermediate and final results of the
previous query are:
1. Join the tables.
2. Drop rows with unequal job codes.
EMPLOYEE Table JOB Table
EMPNUM ... JOBCODE ... SALARY JOBCODE JOBDESC
EMPLOYEE Table JOB Table
EMPNUM ... JOBCODE ... SALARY JOBCODE JOBDESC
1 100 175500 100 MANAGER
. . . . . . . . . . . . . . .
75 300 32000 300 SALESREP
. . . . . . . . . . . . . . .
178 900 28000 900 SECRETARY
. . . . . . . . . . . . . . .