SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-59
Restrictions on Join Queries
In this case, the left join operation displays this hierarchical relationship:
Restrictions on Join Queries
There are a few restrictions on the use of the join operation, as follows:
•
A shorthand view based on any join cannot be specified as the right table of a left
join.
•
A shorthand view whose definition is based on a union of SELECT commands
cannot participate in another join operation.
For additional information about join operations, see the SQL/MP Reference Manual.
Using Views With Joins
For frequently performed join operations, the definition of a view can hide the join
operation from the user and make the columns simpler to access.
The ON Clause and the WHERE Clause in Join Queries
A query can combine inner and left join operations in the same FROM clause.
Therefore, you must indicate which join conditions apply to which join operations, as
follows:
•
Specify join conditions for a left join in the ON clause.
•
Specify join conditions for an inner join or between tables participating in different
left joins in the WHERE clause.
Consider this query:
SELECT S.EMP_NUM, S.EMP_NAME, O.ORD_NUM, R.REG_NAME
FROM REGION R,
SALESEMP S LEFT JOIN ORDERS O
ON S.EMP_NUM = O.BOOKED_BY
AND S.EMP_NUM < 2800
WHERE S.REG_NUM = R.REG_NUM
AND S.REG_NUM IN (6400, 7600) ;
Employee
Department Dependent
011