SEMIJOIN to a Procedure
A SEMIJOIN to a procedure is the logical equivalent of a semijoin to a table.
Syntax
<table_expression>
[LEFT OUTER | RIGHT OUTER |INNER |FULL OUTER] PROCEDURE JOIN
<procedure> ProcedureAlias
ON <condition_expression>
This syntax conveys that for each unique-value set of procedure inputs, the procedure on the right is called once. The results from each call are combined and treated as a row that is fed into the join. The join operates like a nonprocedure-join of the same type.
Remarks
• The special syntax given here always has a procedure on the right side and allows you to deviate from the normal rule that a procedure’s input parameters must be literal expressions.
• When using this syntax, the procedure’s input parameters can include references to any item from the table expression on the left, and only from that context. That is, only values from inside the left-side subquery can be used. The values from other scopes cannot be used.
• All the input value combinations are tracked and are not repeated to call the procedure again.
• Regarding using the PROCEDURE keyword:
— Without the PROCEDURE keyword, your procedure is called exactly once.
— With the keyword, your procedure is called zero or more times, depending on the left side of the join.
Example
(T1 LEFT OUTER JOIN T2 ON T1.x = T2.x)
INNER PROCEDURE JOIN
MyProc(T1.y+T2.y) P1 ON (T1.z = P1.z)