Re: [hibernate-dev] Why does implicit join translate to inner join?
2018-02-22 13:19 GMT+01:00 Vlad Mihalcea <mihalcea.vlad(a)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