SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-52
Combining Data From More Than One Table
from each table. The join predicate determines whether or not the columns satisfy a
given search condition. Defining Predicates on page 1-30 describes how to use
predicates to narrow the range of searching. The same principles apply to join queries.
If columns satisfy the condition, the join operation selects the desired columns,
concatenates the rows, and returns them to the result table.
If you specify multiple tables in the FROM clause but do not specify search conditions,
SQL forms a Cartesian product (or cross product) by concatenating, in turn, each row
of each table with every other row of every other table.
A Cartesian product involving two tables, one with M rows and the other with N rows, is
of size M x N. Therefore, the use of join predicates is likely to reduce the size of the
result. Furthermore, use of join predicates can reduce the amount of work done by
SQL to produce the result. For more information, see Section 3, Improving Query
Performance Through Query Design.
In the query in Figure 1-3 on page 1-53, the joining columns are
EMPLOYEE.EMPNUM and DEPT.MANAGER. The predicate is:
WHERE EMPLOYEE.EMPNUM = DEPT.MANAGER
SQL concatenates those rows from the two tables, EMPLOYEE and DEPT, that have
the same values in EMPNUM and MANAGER, respectively.