Home > Software design >  can this left join be safely removed?
can this left join be safely removed?

Time:09-02

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;

CodePudding user response:

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.

  • Related