SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
2-204
MXCI Examples of SELECT
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
. . . . . . . . . . . . . . .
207 420 33000 420 ENGINEER
. . . . . . . . . . . . . . .
568 300 39500 300 SALESREP