[hibernate-dev] Why does implicit join translate to inner join?

[Lukas Eder]

2018-02-22 13:19 GMT+01:00 Vlad Mihalcea <mihalcea.vlad at gmail.com>:

> Hi,
>
> One possible reason was to have a single way of treating implicit joins.
>

Sure, but if paths generated only outer joins, your statement would still
be true.


> In WHERE and ORDER BY clauses, implicit join makes sense to render an
> INNER JOIN.
>

I agree with WHERE for most cases, but I decidedly do not agree with ORDER
BY. It is even more surprising when an ORDER BY clause implicitly filters
the results due to the presence of an implicit join path. For example (not
sure if that works in JPQL right now, but I don't see anything wrong with
the concept):

   SELECT c.firstName, c.lastName
   FROM customer c
   ORDER BY c.address.city.country.code

So, this query would implicitly filter out customers without addresses (or
whose addresses had no cities, etc.) rather than applying default NULLS
FIRST / NULLS LAST semantics?

There's also a case where an outer join is useful in the WHERE clause,
namely with IS NULL predicates:

   SELECT c.firstName, c.lastName
   FROM customer c
   WHERE c.address.city.country.code IS NULL

In this case, the predicate could act like an Elvis operator on the whole
path as it evaluates to true if *any* of the values is null (address, city,
country, or country code). In the current case of generating inner joins,
only country codes that are NULL are retained. This is the only case I can
see where both join types would be reasonable in their own ways. For
consistency, I'd still opt for outer joins in this case as well.

Lukas