SQL/MP Query Guide

Retrieving Data: How to Write Queries
HP NonStop SQL/MP Query Guide—524488-003
1-60
The ON Clause and the WHERE Clause in Join
Queries
SQL first performs an inner join of the REGION table with the SALESEMP table. The
result table of this inner join operation is then left joined with the ORDERS table.
Because SALESEMP appears on the left of the keywords LEFT JOIN, SALESEMP is
the table that is preserved from the left join operation.
The query returns this result:
EMP_NUM EMP_NAME ORD_NUM REG_NAME
------- -------- ------- ------------
2703 MORRISON, J. ? USA
2705 HENNESSY, A. 57 USA
--- 2 row(s) selected.
The ON Clause
For each occurrence of the LEFT JOIN keywords in the FROM clause, there is a
corresponding ON clause.
The predicates in the ON clause specify the conditions for the left join evaluation.
These conditions determine whether rows can be joined together or whether the rows
from the preserved table are preserved through null augmentation.
In the former example, there was one left join operation:
SALESEMP S LEFT JOIN ORDERS O
ON S.EMP_NUM = O.BOOKED_BY
AND S.EMP_NUM < 2800
The table preserved in each left join can, in turn, be inner-joined or left-joined with
another table.
The WHERE Clause
Predicates in the WHERE clause specify join conditions and define restrictions on
individual tables named in the FROM clause.
In the previous example, the WHERE clause joins the REGION table with the
SALESEMP table and specifies a restriction on the SALESEMP table:
WHERE S.REG_NUM = R.REG_NUM
AND S.REG_NUM IN (6400, 7600)
In a left join query, predicates from the WHERE clause that apply to a table
participating in a left join operation are evaluated as follows:
•
If a predicate contains columns from the right table, the predicate is evaluated after
the left join operation: that is, only after null augmentation is performed.
•
Otherwise, the predicate can be evaluated before or after the left join operation.