Question: subquery is not allowed in on clause?
Answer: * The ON clause cannot include any subqueries. * Column references in an ON clause must only reference columns of tables that are in the scope of the ON clause. * Scalar fullselects are not allowed in the expressions of an ON clause. ... * The ON clause cannot include an XMLQUERY or XMLEXISTS expression.