[FIXED] can this left join be safely removed?

Issue

I have the following JPA query method in a Spring Data repository

public interface UserRoleRepository extends CrudRepository<UserRole, UUID> {

    @Query("""
        from UserRole ur
        join ur.role
        join ur.user
        left join ur.businessUnit
        where ur.user.id = :userId""")
    List<UserRole> getUserRoles(UUID userId);
}

Given that no predicates are applied to ur.businessUnit, can this query be simplified to

from UserRole ur
join ur.role
join ur.user
where ur.user.id = :userId

Each user role either has 0 or 1 associated business units. In JPA terms, this is defined in the UserRole class as:

@ManyToOne
@JoinColumn(name = "business_unit_id")
private BusinessUnit businessUnit;

Solution

A left join is cardinality preserving, so as long as you don't refer to the join alias (which doesn't even exist in your case), it is safe to remove the join.

Answered By - Christian Beikov
Answer Checked By - David Marino (JavaFixing Volunteer)