What is the difference between JOIN and JOIN FETCH when using JPA and Hibernate

Question

Please help me understand where to use a regular JOIN and where a JOIN FETCH. For example, if we have these two queries

FROM Employee emp
JOIN emp.department dep

and

FROM Employee emp
JOIN FETCH emp.department dep

Is there any difference between them? If yes, which one to use when?

Rheo

you can find it here link read 14.3. Associations and joins – Ragin

I have gone through that documententation but still don't know where should I use a JOIN and where a JOIN FETCH. – Rheo

If you have @oneToOne mapping set to FetchType.LAZY and you use second query(because you need Department objects to be loaded as part of Employee objects) what Hibernate will do is, it will issue queries to fetch Department objects for every individual Employee object it fetches from DB. Later in the code you might access Department objects via Employee to Department single-valued association and Hibernate will not issue any query to fetch Department object for the given Employee. Remember Hibernate still issues queries equal to the number of Employees it has fetched. – Hawkshaw

To assist in the doc hunt ~ Fetching Strategies – Dabster

how to handle if there was no data in department table – Content

@ShameeraAnuranga I think in that case you will need a LEFT OUTER JOIN. – Rheo

Answer 1

In this two queries, you are using JOIN to query all employees that have at least one department associated.

But, the difference is: in the first query you are returning only the Employes for the Hibernate. In the second query, you are returning the Employes and all Departments associated.

So, if you use the second query, you will not need to do a new query to hit the database again to see the Departments of each Employee.

You can use the second query when you are sure that you will need the Department of each Employee. If you not need the Department, use the first query.

I recomend read this link if you need to apply some WHERE condition (what you probably will need): How to properly express JPQL "join fetch" with "where" clause as JPA 2 CriteriaQuery?

Update

If you don't use fetch and the Departments continue to be returned, it is because your mapping between Employee and Department (a @OneToMany) are setted with FetchType.EAGER. In this case, any HQL (with fetch or not) query with FROM Employee will bring all Departments. Remember that all mapping *ToOne (@ManyToOne and @OneToOne) are EAGER by default.

Ovenbird

Which behaviour will be if we execute statement without fetch and get result. Then within session we will treat to department? – Cutlass

@gstackoverflow, yes – Ovenbird

I use native query with Lazy fetch in both sides of relationship but still loads hierarchy of child relations. – Dr

Worth mention that fetch has to be used if (using our example) you want to order by some Department attribute. Otherwise, (valid at least for PG) you might get ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list – Snake

@Ovenbird Is there a way to use this query but with nativeQuery=true? – Germanophobe

@Germanophobe If you use nativeQuery you are not anymore using the JPQL/HQL language to create queries; you are using plain SQL. So, the fetch is not available. – Ovenbird

This answer appears to be the accepted answer but has a glaring error in it. This sentence seems incomplete: "But, the difference is: in the first query you are returning only the Employes for the Hibernate." What is this sentence supposed to say? – Platon

@Platon the first query just return the Employe entities. Maybe the "for the Hibernate" could be removed. – Ovenbird

Answer 2

JOIN

When using JOIN against an entity associations, JPA will generate a JOIN between the parent entity and the child entity tables in the generated SQL statement.

So, taking your example, when executing this JPQL query:

FROM Employee emp
JOIN emp.department dep

Hibernate is going to generate the following SQL statement:

SELECT emp.*
FROM employee emp
JOIN department dep ON emp.department_id = dep.id

Note that the SQL SELECT clause contains only the employee table columns, and not the department ones. To fetch the department table columns, we need to use JOIN FETCH instead of JOIN.

JOIN FETCH

So, compared to JOIN, the JOIN FETCH allows you to project the joining table columns in the SELECT clause of the generated SQL statement.

So, in your example, when executing this JPQL query:

FROM Employee emp
JOIN FETCH emp.department dep

Hibernate is going to generate the following SQL statement:

SELECT emp.*, dept.*
FROM employee emp
JOIN department dep ON emp.department_id = dep.id

Note that, this time, the department table columns are selected as well, not just the ones associated with the entity listed in the FROM JPQL clause.

Also, JOIN FETCH is a great way to address the LazyInitializationException when using Hibernate as you can initialize entity associations using the FetchType.LAZY fetching strategy along with the main entity you are fetching.

Fokos

Is it possible to use multiple JOIN FETCH in same query? – Barbarbarbara

Yes, it's possible for any number of @ManyToOne and @OneToOne associations and at most one collection. – Fokos

Answer 3

in this link i mentioned before on the comment, read this part :

A "fetch" join allows associations or collections of values to be initialized along with their parent objects using a single select. This is particularly useful in the case of a collection. It effectively overrides the outer join and lazy declarations of the mapping file for associations and collections.

this "JOIN FETCH" will have it's effect if you have (fetch = FetchType.LAZY) property for a collection inside entity(example bellow).

And it is only effect the method of "when the query should happen". And you must also know this:

hibernate have two orthogonal notions : when is the association fetched and how is it fetched. It is important that you do not confuse them. We use fetch to tune performance. We can use lazy to define a contract for what data is always available in any detached instance of a particular class.

when is the association fetched --> your "FETCH" type

how is it fetched --> Join/select/Subselect/Batch

In your case, FETCH will only have it's effect if you have department as a set inside Employee, something like this in the entity:

@OneToMany(fetch = FetchType.LAZY)
private Set<Department> department;

when you use

FROM Employee emp
JOIN FETCH emp.department dep

you will get emp and emp.dep. when you didnt use fetch you can still get emp.dep but hibernate will process another select to the database to get that set of department.

so its just a matter of performance tuning, about you want to get all result(you need it or not) in a single query(eager fetching), or you want to query it latter when you need it(lazy fetching).

Use eager fetching when you need to get small data with one select(one big query). Or use lazy fetching to query what you need latter(many smaller query).

use fetch when :

• no large unneeded collection/set inside that entity you about to get

• communication from application server to database server too far and need long time

• you may need that collection latter when you don't have the access to it(outside of the transactional method/class)

Ragin

Could you explain it for the queries that I just wrote in the updated question. – Rheo

useful consideration : "no large unneeded collection/set inside that entity you about to get" – Pipes

Would the departments still be eagerly fetched if the departments inside the employee was a List instead of a Set ? – V2

Does using the FETCH keyword in an JPQL statement imply an eagerly retrieved property ? – V2

Answer 4

If you have @oneToOne mapping set to FetchType.LAZY and you use second query (because you need Department objects to be loaded as part of Employee objects) what Hibernate will do is, it will issue queries to fetch Department objects for every individual Employee object it fetches from DB.

Later, in the code you might access Department objects via Employee to Department single-valued association and Hibernate will not issue any query to fetch Department object for the given Employee.

Remember, Hibernate still issues queries equal to the number of Employees it has fetched. Hibernate will issue same number of queries in both above queries, if you wish to access Department objects of all Employee objects

Hawkshaw

Answer 5

Difference JOIN and JOIN FETCH in JPQL

TLDR: The FETCH keyword tells the entityManager to also fetch the associated entities which are joined eagerly (when this is not already the case).

---

Lets say we an user entity and userInfo entity. The user entity has a @OneToMany relationship with userInfo as follows:

import javax.persistence.*;

@Entity
@Table(name= "test_user")
public class User {

    // ... user properties id etc

    @OneToMany(mappedBy = "user" fetch = FetchType.LAZY)
    private List<UserInfo> infoList;
}

And suppose we have the following Queries (this Spring data JPA syntax, however does not matter how JPQL is constructed):

@Query("SELECT user FROM User user JOIN user.infoList info")
public List<User> getUsersJoin();

@Query("SELECT user FROM User user JOIN FETCH user.infoList info")
public List<User> getUsersJoinFetch();

---

The first Query with only the JOIN keyword will generate the following SQL:

select u.id, u.email, u.name from test_user u
inner join test_user_data on u.id=test_user_data.user_id;

As you can see it will only fetch data from the test_user table and not from the test_user_data table. This can also be seen in the debugger as follows:

As can be seen we do not have the List<userData> on our User object because this is not loaded by default.

---

Now lets check out the generated SQL for the Query with JOIN FETCH:

select test_user.id, data.id, test_user.email, test_user.name, 
data.data, data.user_id, data.user_id, data.id from test_user test_user 
inner join test_user_data data on test_user.id=data.user_id

As can be seen we are now fetching the data from both the test_user and the test_user_data table in the join. This can also be seen in the debugger as follows:

As can be observed we now have access to the List<userData> within the User Object.

Sniffle

From this it seems that join is plain pointless, which can't be true... right? What am I missing? – Cognize

you could still have a condition on the joined table like ... where test_user_data.some_field = some_value. This way you get the users, that fulfill the condition, but without the overhead of loading data, that is not needed later on. – Bonniebonns

Answer 6

Dherik : I'm not sure about what you say, when you don't use fetch the result will be of type : List<Object[ ]> which means a list of Object tables and not a list of Employee.

Object[0] refers an Employee entity 
Object[1] refers a Departement entity 

When you use fetch, there is just one select and the result is the list of Employee List<Employee> containing the list of departements. It overrides the lazy declaration of the entity.

Horsey

I don't know if I understand your concern. If you don't use fetch, your query will return only the Employees. If the Departments, even in this case, continue to be returned, is because your mapping between Employee and Department (a @OneToMany) are setted with FetchType.EAGER. In this case, any HQL (with fetch or not) query with FROM Employee will bring all Departments. – Ovenbird

Without using fetch (join term only), the result would be an array of collections, two rows, the first is a collection of Employees and the second is a collection of Departments. Using eager fetch or lazy fetch, departments will be fetched. – Horsey

Without fetch on HQL, this will happen only if your mapping between Employee and Department are EAGER (@OneToMany(fetch = FetchType.EAGER). If is not the case, the Departments will not be returned. – Ovenbird

@Ovenbird try it yourself, You'll get a ClassCastException. – Horsey

I figure out the problem. Is not a fetch problem, but how the select was made in the HQL. Try SELECT emp FROM Employee emp JOIN FETCH emp.department dep. JPA/Hibernate have this behaviour of return a List of Object[] when you ommit the SELECT part. – Ovenbird

Answer 7

JOIN FETCH => returns both tables(entities) that you indicated in the JPQL query

JOIN => returns only one table(entity) from JPQL query

Cattish