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

[Steve Ebersole]

Yes, this could be made sensitive to where the implicit join occurs.

However, there is another, better way... explicit joins.  In our opinion
originally (and nothing has changed my mind about this) it is better to be
consistent in how implicit joins are handled.  It is far easier to impart
to users that "implicit inner joins are always inner joins" as opposed to
"well implicit joins are interpreted relative to the association being
joined".  Not to mention, adjusting the type of SQL join used for implicit
jois means I can no longer just look at the query and know what is
happening in terms of SQL joins - which is bad.

Not to mention, IMO interpreting these as inner joins is more OO-ish.  I
realize there are different points of view on this, but again, if you want
explicit joining characteristics you can, you know, declare thew joins
explicitly.

On Thu, Feb 22, 2018 at 7:14 AM Lukas Eder <lukas.eder at gmail.com> wrote:

> 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
> _______________________________________________
> hibernate-dev mailing list
> hibernate-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-dev
>