SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-55
Types of Join Queries
Both queries produce the same result. The result contains only those rows that satisfy
the join predicate given in the WHERE clause in the first query and the ON clause in
the second query, as follows:
EMP_NUM EMP_NAME ORD_NUM
------- -------- -------
2705 HENNESSY, A. 57
2906 NAKAGAWA, E. 77
3598 CHU, F. 12
--- 3 row(s) selected.
The order of the table names in the queries does not influence join order (which table
is the outer table and which is the inner table).
An inner join discards all rows that do not satisfy the given search condition, so
information about salespersons who have not booked any orders is missing from the
result of this query. If you want such information, you can specify a left join operation.
Left Join
The left join, or left outer join, returns all rows that satisfy the given predicates. In
addition, it returns all rows in the left table that fail to satisfy the join predicates; these
rows, however, have information missing in all of the columns that correspond to the
right table. In other words, the result contains information from the left table, regardless
of whether matching right table rows exist.
This query formulates a left join operation of the tables SALESEMP and ORDERS:
SELECT S.EMP_NUM, S.EMP_NAME, O.ORD_NUM
FROM SALESEMP S LEFT JOIN ORDERS O
ON S.EMP_NUM = O.BOOKED_BY ;
The query returns this result:
EMP_NUM EMP_NAME ORD_NUM
------- -------- -------
2703 MORRISON, J. ?
2705 HENNE0SSY, A. 57
2906 NAKAGAWA, E. 77
3598 CHU, F. 12
4096 CHOW, J. ?
--- 5 row(s) selected.
The left join shows three employees who booked orders and also shows the
employees who did not book any orders, indicated by a question mark (?) in the
ORD_NUM column: J. Morrison (employee number 2703) and J. Chow (employee
number 4096). The question mark indicates a null value, denoting unknown or null
information. These are rows that failed to satisfy the search condition.
If you use host variables to store results in a program, the program must handle the
null values that can result from left join operations.