I have two tables, tableA and tableB. The columns in tableA are a, b, and c, while the columns in tableB are a, d, and e. Here, a is the primary key of tableA and a foreign key in tableB. Additionally, d is the primary key of tableB, and there is a one-to-one mapping between tableA and tableB.

I want to execute the following query using Spring Data JPA:

select ta.a, ta.b, tb.e from tableA ta 
left join tableB tb on ta.a = tb.a 
where ta.b = :b and ta.c = :c

In my repository interface, I have defined a method findAllByBAndC(String b, String c) without using the @Query annotation to run the above query. However, when I call my API and check the debug logs, I see that JPA is executing a single query multiple times. The query being run is:

select ta.a, ta.b, tb.e from tableA ta 
left join tableB tb on ta.a = tb.a 
where tb.a = :a

I could use the @Query annotation to write the custom query mentioned above, but I have two questions:

1. Why is this behavior occurring? Shouldn’t JPA interpret my method definition findAllByBAndC(String b, String c) correctly and run the appropriate query to find all rows by b and c (it should return all columns but thats okay)?
2. Is there a way to resolve this issue without using the @Query annotation or writing extra boilerplate code, and just use Spring Data JPA and repository methods?

Below is the code for the scenario described:

TableA.java:

@Entity
@Table(name = "tableA")
@Getter
@NoArgsConstructor
@Setter
public class TableA {
    @Id
    private Long a;
    private String b;
    private String c;

    @OneToOne(mappedBy = "tableA", fetch = FetchType.LAZY)
    private TableB tableB;
}

TableB.java:

@Entity
@Table(name = "tableB")
@Getter
@NoArgsConstructor
@Setter
public class TableB {
    @Id
    private Long d;
    private String e;

    @OneToOne
    @JoinColumn(name = "a")
    private TableA tableA;
}

Repository Layer:

@Repository
public interface TableARepository extends JpaRepository<TableA, Long> {
    List<TableA> findAllByBAndC(String b, String c);
}

Service Layer:

@Service
public class TableAService {
    @Autowired
    private TableARepository tableARepository;

    public List<TableA> findAllByBAndCAndFilterByD(String b, String c, Long d) {
        return tableARepository.findAllByBAndC(b, c).stream()
                .filter(data -> data.getTableB() != null && data.getTableB().getE().equals(e))
                .collect(Collectors.toList());
    }
}

I have tried using the @Query annotation with the custom query, and I know this approach works. However, my question is to understand why this behavior is occurring and if we can achieve the behaviour i want without using @Query.