Difference between join and path navigation in jpa query

Question

I have the following 2 entities:

class User {
private String name;
private UserType userType;
}

class UserType {
private String name;
}

I want to fetch all Users with userType name equal to 'admin'. I can write the following 2 queries that return the same result.

select u from User u where u.userType.name = 'admin';

and

select u from User u join u.userType ut where ut.name = 'admin';

Just wanted to understand which approach is preferable and what is the difference. If I can always get the result using navigation between entities when will I want to follow the join approach?

Answer 1

Technically, they are equivalent but the second one is much more flexible.

With the explicit join syntax, you can change the JOIN to LEFT JOIN with an ON criteria:

select u 
from User u 
left join u.userType ut on ut.name = 'admin'

This query will always return a User even if it doesn't have a user type, so sometimes that's desirable for some particular use cases.

Answer 2

On analysing the SQL generated by the two queries (jpa + Spring Data) it was observed that navigation leads to a cross join whereas mentioning only 'join' leads to an inner join by default.

Hence Navigation is not as efficient as explicitly joining on the required columns.