What is the difference between explicit and implicit JOIN in JPA? (performance)

Question

These days I'm reading about JPA. I've learned that it is possible to use explicitor implicit JOIN in JPQL.

Explicit join

em.createQuery(“SELECT b.title, p.name FROM Book b JOIN b.publisher p”).getResultList();

Implicit join

em.createQuery(“SELECT b.title, b.publisher.name FROM Book b”).getResultList();

The source of these examples: link

My question is: Is there any difference in terms of performance between explicit and implict JOIN?

---

UPDATE

I've read what you've written @MatteoBaldi and @Kayaman, I've done some tests and I want to share the results with you.

I've created two entities: students and course, and I had a manyToOne (many students attend one course). I've used EcpliseLink Implementation of JPA.

Query = select students and dummyFiled from course, scenarios of execution:

1. manyToOne (defaultFetch -> eager), implicit JOIN. Result: a single SQL query that does all the work.
2. manyToOne (Fetch - > lazy), implicit JOIN. Result: the same SQL query as 1.
3. manyToOne (defaultFetch - > eager), explicit JOIN. Result: the same SQL query as 1.
4. manyToOne (Fetch - > lazy), explicit JOIN. Result: the same SQL query as 1.

So in my environement of test (EclipseLink,..) I had the same SQL query that was generated from my JPQL queries. So I can say that the performance will be same (ofcourse, I say again in my conditions of tests, I hope that someone can confirm/correct my results and make a general rule.

Answer 1

They are parsed differently, so they can end up as different SQL queries depending on the query, entity relationships and other such things. Ideally there should be no difference as long as the JPQL queries are doing the same thing, but it doesn't always work that way.

The recommended way is to use explicit joins, and that has other advantages such as specifying JOIN FETCH on lazy relationships. This question concentrates a bit too much on performance, as obviously if one of them were more performant but gave the same results, there would be no reason to use the slower one.

Enabling SQL logging to see the generated queries is a good way to verify that your application is making the queries you expect, no matter which syntax you use. You can't just rely on the JPQL, you need to know and understand your database so you're not just using an "obfuscation layer" as a_horse_with_no_name likes to call ORM frameworks ;)

Answer 2

Usually, I always explicit every JOIN. The main objective is to be more clear about what the query are doing and how, having a more predictable SQL with future changes on the query.

But I can give you an example where the implicit JOIN have more performance than a explicit JOIN, even this gain of performance is very tiny in most of the cases and, personally, not pays the risk (I will explain what risk later).

Imagine that you are trying to group all books with the same publisher. With explicit JOIN, this is the JPQL:

 SELECT count(publisher.id) 
 FROM FROM Book b
 JOIN b.publisher publisher
 WHERE publisher.id = 1
 GROUP by publisher.id

This query is readable, without any repetition and any change in this query (like add another WHERE condition) will maintain the generated SQL as expected.

Now, with implicit JOIN:

 SELECT count(b.publisher.id) 
 FROM FROM Book b
 WHERE b.publisher.id = 1
 GROUP by b.publisher.id

We have by b.publisher.id repeated three times. If you add another condition, there is a risk that the JPQ interpretation from Hibernate change the generated SQL, doing unnecessary JOIN like explained on Kayaman answer.

But this JPQL with implicit JOIN don't do a extra JOIN to publisher table on the generated SQL, because he can use the id from published_id:

 SELECT Count(b.published_id) AS col_0_0_ 
 FROM   Book b
 WHERE  b.published_id = ? 
 GROUP  BY b.published_id

And the SQL explicit JOIN with an extra JOIN:

 SELECT Count(published.id) AS col_0_0_ 
 FROM   Book b
 INNER JOIN published published1_ 
           ON b.published_id = published1_.id 
 WHERE  published_id = ? 
 GROUP  BY published_id

But this only happens if the foreign key from publisher is in the book side. In this case is the right approach, but there are some mapping where the foreign key could be in any side of the table, so the explicit and implicit JPQLs queries can generate the same SQL.

And at my experience, explicit JOIN is always a better choice. The Hibernate does not always understand that the same implicit JOIN duplicated on the same query is part of the same JOIN, creating not optimized/weird SQLs.

Answer 3

They should be the same but in reality it depends mainly on the underlying database. A quick way to test the performance in your current environment is to enable SQL logging, trace the native queries your jpql is traslated into and try those directly with a SQL client.