Search code examples
jpql

Entities not referenced by another entity

In the following relationship, I would like to find instances of entity A not referenced by B.

@Entity
public class A {
}

@Entity
public class B {
    @OneToOne(fetch = FetchType.LAZY)
    @NotFound(action = NotFoundAction.IGNORE)
    @JoinColumn(name = "a_id")
    private A a;
}

How can I write a JPQL query for this?

Solution

  • You can do the left join with A and check if b.id is null to find all rows of A which doesn't have reference in B.

    JPQL:

    SELECT a FROM A a LEFT JOIN B b on b.a = a where b.id IS NULL

    Mysql:

    select a.id from A a left join B b on b.a_id = a.id where b.id is NULL;