Just to clarify,
You propose that the JP-QL ON syntax essentially add additional SQL constraints to the ON clause, is that correct?
In this case, I'd suggest that we use a different name than ON (would we one day want to support the full replacement of the ON clause). I know that's the reason why we've chosen the WITH keyword.
Emmanuel
On 11 mars 2011, at 22:30, Linda DeMichiel wrote:
> Outer joins with ON conditions have been requested a number of times.
>
> The issue is that the current JPQL and criteria API syntax do not
> support capturing the semantics that SQL provides.
>
> Chapter 4 of the JPA spec states that outer joins "enable the
> retrieval of a set of entities where matching values in the join
> condition may be absent."
>
> The issue appears with the semantic difference between restrictions
> applied in the WHERE clause and in the SQL outer join ON clause.
>
> The SQL semantics for the outer join
>
> SELECT A.*, B.*
> FROM A LEFT JOIN B
> ON C1 AND C2 ... Cn
>
> are the following:
>
> SELECT A.*, B.*
> FROM A, B
> WHERE C1 AND C2 ... AND Cn
>
> UNION ALL
>
> SELECT A.*, NULL, NULL, ..., NULL
> FROM A
> WHERE A.* NOT IN (SELECT A.*
> FROM A, B
> WHERE C1 AND C2 ... AND Cn )
>
>
> This means that if an A tuple ai does not join with any B tuple according to
> the condition C1 AND C2 ... AND Cn, then the tuple [ai.*, NULL, ....,
> NULL] *will* appear in the query result (even if some predicate condition Cj
> applies only to the A columns of A and is false for ai).
>
>
> To illustrate more concretely, the following example was posted
> in a request on my blog (thanks Bernard, if you are reading!)
>
>
> Let's say you want a list of car dealers and how many new
> vehicles they're selling.
>
> In SQL you can say:
>
> SELECT d.name, count( v.id )
> FROM dealer d LEFT OUTER JOIN vehicle v
> ON v.dealer_id = d.dealer_id AND v.type = 'New'
> GROUP BY d.name
>
> In JPQL, you only have the WHERE clause which limits your results,
> making the outer join useless:
>
> SELECT d.name, count( v.id )
> FROM dealer d LEFT OUTER JOIN d.vehicleList v
> WHERE v.type = 'New'
> GROUP BY d.name
>
>
> Were we to include support for the "ON" syntax and semantics,
> the JPQL equivalents would be the following:
>
> SELECT d.name, count(v.id)
> FROM dealer d LEFT OUTER JOIN d.vehicle v
> ON v.type = 'New'
> GROUP BY d.name
>
> and
>
> SELECT d.name, count(v.id)
> FROM dealer d LEFT OUTER JOIN d.vehicle v
> WHERE v.type = 'New'
> GROUP BY d.name"
>
> In the first of these queries, all dealers are included in the result,
> including dealers with no vehicles that qualify.
>
> In the second, we're only looking at dealers that have at least one
> new vehicle.
>
>
> I propose that we should add support for the JPQL syntax
>
> LEFT [OUTER] JOIN join_association_path_expression [AS] identification_variable
> [ON conditional_expression]
>
> with the semantics that:
>
> SELECT a, b.x1, b.x2, ... bxn
> FROM A a LEFT JOIN a.b b
> ON c1...cn
>
> is equivalent to the semantics of
>
> SELECT a, b.x1, b.x2, ... bxn
> A a JOIN a.b b
> WHERE c1...cn
>
> unioned with
>
> SELECT a, null1, null2, ... nulln
> FROM A a
> WHERE a NOT IN (SELECT a' FROM A a' JOIN B b'
> WHERE c1...cn)
>
>
> For the criteria API equivalent, we could add methods
>
> on(Expression<Boolean> restriction);
> and
>
> on(Predicate... restriction);
>
> to the Join interface.
>
> I'm not however sure what it is best that these methods return.
>
> To be complete, we'd also need the method
>
> Predicate getOn();
>
> on the Join interface as well.
>
>
> Finally, we should also discuss whether we should support the use of
> ON with inner joins. SQL supports this, with the semantics that it is
> equivalent to the use of WHERE.
>
>
> Opinions??
>
> thanks,
>
> -Linda
>
>
>