[JPA] 서브 쿼리

※ 서브쿼리 예제 데이터

---

JPQL 서브쿼리

JPQL도 SQL처럼 서브쿼리를 지원하지만 모든 서브쿼리를 지원하는 것은 아니다.

SELECT절 → Hibernate의 HQL에서는 허용 O / JPA 자체에서는 허용 X
FROM절 → 허용 X
WHERE절 → 허용 O
HAVING절 → 허용 O

 

from절에서의 서브쿼리는 JPA, Hibernate에서는 허용을 하지 않지만, JPA의 다른 구현체에서는 허용한다

 

1. SELECT절 서브쿼리 (Hibernate의 HQL)

(1) Member별로 주문을 한 횟수

List<Object[]> resultList = em.createQuery(
        "SELECT m, (SELECT COUNT(*) FROM Order o WHERE o.member.id = m.id)" +
                " FROM Member m"
).getResultList();

for (Object[] objects : resultList) {
    Member member = (Member) objects[0];
    Long count = (Long) objects[1];
    System.out.println("Member = " + member + " -> OrderCount = " + count);
}

 

(2) 팀에 소속된 멤버 수

List<Object[]> resultList = em.createQuery(
        "SELECT t, (SELECT COUNT(*) FROM Member m WHERE m.team.id = t.id)" +
                " FROM Team t"
).getResultList();

for (Object[] objects : resultList) {
    Team team = (Team) objects[0];
    Long count = (Long) objects[1];
    System.out.println("Team = " + team + " -> MemberCount = " + count);
}

 

2. WHERE절 서브쿼리

(1) Member DB에서 나이가 평균보다 많은 회원들

• 멤버 평균 나이 : 25.8살

List<Member> members = em.createQuery(
        "SELECT m" +
                " FROM Member m" +
                " WHERE m.age > (SELECT AVG(m.age) FROM Member m)" +
                " ORDER BY m.age",
        Member.class
).getResultList();

 

(2) 멤버가 2명 이상 소속된 팀

List<Team> resultList = em.createQuery(
        "SELECT t" +
                " FROM Team t" +
                " WHERE (SELECT COUNT(m) FROM Member m WHERE m.team.id = t.id) >= 2",
        Team.class
).getResultList();

 

이렇게 WHERE절에 team_id에 해당되는 멤버의 수를 직접 넣을수도 있지만 "Team쪽에 Member의 컬렉션"을 보유한다면 더 간단하게 조회할 수 있다

@Entity
@Table(name = "team")
public class Team {
    ...
    ...

    @OneToMany(mappedBy = "team")
    private List<Member> memberList = new ArrayList<>();
}

List<Team> resultList = em.createQuery(
        "SELECT t" +
                " FROM Team t" +
                " WHERE t.memberList.size >= 2",
        Team.class
).getResultList();

컬렉션에 대한 size를 where절에서 검증을 해주니까 JPQL을 해석함에 따라서 알아서 WHERE절에 멤버 수를 조회하는 서브쿼리가 적용됨을 확인할 수 있다

 

 

서브쿼리 함수

1. [NOT] EXISTS

EXISTS = 서브쿼리 결과에 존재하는지

팀B 소속인 멤버

List<Member> resultList = em.createQuery(
        "SELECT m" +
                " FROM Member m" +
                " WHERE EXISTS (SELECT t FROM m.team t WHERE t.name = 'Team-B')",
        Member.class
).getResultList();

 

NOT EXISTS = 서브쿼리에 결과에 존재하지 않는지

팀A 소속이 아닌 멤버 (팀에는 소속되어 있지만, 팀A에 소속된 멤버 제외)

List<Member> resultList = em.createQuery(
        "SELECT m" +
                " FROM Member m" +
                " WHERE NOT EXISTS (SELECT t FROM m.team t WHERE t.name = 'Team-A')" +
                "       AND m.team IS NOT NULL",
        Member.class
).getResultList();

 

2. {ALL | ANY | SOME}

>> 이 서브쿼리 함수들은 "비교 연산자"와 같이 사용해야 한다

 

ALL = 서브쿼리 결과 모두 만족

완판되지 못한 상품들 (Product.stockAmount > Order.orderAmount)
→ 완판된 상품 = Product-B

List<Product> resultList = em.createQuery(
        "SELECT p" +
                " FROM Product p" +
                " WHERE p.stockAmount > ALL(SELECT o.orderAmount FROM Order o)",
        Product.class
).getResultList();

 

 

ANY/SOME = 서브쿼리 결과 중 하나라도 만족

어떤팀이든 상관없이 팀에 속한 멤버
→ 팀이 없는 멤버 = [Avenus3, Avenus10]

// ANY
List<Member> resultList = em.createQuery(
        "SELECT m" +
                " FROM Member m" +
                " WHERE m.team = ANY(SELECT t FROM Team t)" +
                " ORDER BY m.id",
        Member.class
).getResultList();

// SOME
List<Member> resultList = em.createQuery(
        "SELECT m" +
                " FROM Member m" +
                " WHERE m.team = SOME(SELECT t FROM Team t)" +
                " ORDER BY m.id",
        Member.class
).getResultList();

 

3. [NOT] IN

IN = 서브쿼리 결과 안에 같은것이 있는지

30세 이상 멤버를 보유한 팀 목록
→ Team-B[Avenus4, Avenus5] / Team-C[Avenus9]

List<Team> resultList = em.createQuery(
        "SELECT t" +
                " FROM Team t" +
                " WHERE t IN(SELECT t2 FROM Team t2 INNER JOIN t2.memberList ml WHERE ml.age >= 30)",
        Team.class
).getResultList();

 

NOT IN = 서브쿼리 결과 안에 같은것이 없는지

팀에 소속되지 않은 멤버들

List<Member> resultList = em.createQuery(
        "SELECT m" +
                " FROM Member m" +
                " WHERE m NOT IN(SELECT ml FROM Team t INNER JOIN t.memberList ml)",
        Member.class
).getResultList();