SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-54
Types of Join Queries
Do not confuse inner and left join operations with join strategies, such as sort merge,
key-sequenced merge, nested, and hash. Join strategies refer to the mechanisms SQL
uses to perform joins. These strategies are discussed in Section 3, Improving Query
Performance Through Query Design.
Example 1-9 shows the sample tables for several of the join examples that follow.
Inner Join
This query formulates an inner join of the tables SALESEMP and ORDERS:
SELECT S.EMP_NUM, S.EMP_NAME, O.ORD_NUM
FROM SALESEMP S, ORDERS O
WHERE S.EMP_NUM = O.BOOKED_BY ;
You can also write this query using the INNER JOIN keywords as follows:
SELECT S.EMP_NUM, S.EMP_NAME, O.ORD_NUM
FROM SALESEMP S INNER JOIN ORDERS O
ON S.EMP_NUM = O.BOOKED_BY ;
Note. Certain combinations of left joins, inner joins, and the UNION operator are not valid in a
single SELECT statement. For more information, see the SQL/MP Reference Manual.
Example 1-9. Sample Tables for Join Examples
SALESEMP Table
EMP_NUM EMP_NAME REG_NUM MGR_NUM
------- -------- ------- -------
2703 MORRISON, J. 7600 2705
2705 HENNESSY, A. 7600 6554
2906 NAKAGAWA, E. 6400 6554
3598 CHU, F. 6470 2906
4096 CHOW, J. 6420 3598
REGION Table
REG_NUM REG_NAME REG_HQLOC
-------- -------- ---------
6400 JAPAN 900
6420 HONG KONG 920
6470 TAIWAN 970
7600 USA 100
ORDERS Table
ORD_NUM CUST_NUM BOOKED_BY
------- --------- ---------
12 729 3598
57 283 2705
77 1064 2906