Querydls - 서브 쿼리

서브 쿼리

com.querydsl.jpa.JPAExpressions를 사용한다.

서브 쿼리 eq 사용

/*
* 나이가 가장 많은 회원 조회
* */
@Test
void subQuery() {
    QMember memberSub = new QMember("memberSub")
    List<Member> result = queryFactory
            .selectFrom(member) // member 조회
            .where(member.age.eq( // member.age가 memberSub(member)중 max()값이랑 같은 조건
                    JPAExpressions.select(memberSub.age.max())
                            .from(memberSub)
            ))
            .fetch();
    assertThat(result).extracting("age").containsExactly(40);
}

select
        member1
from
    Member member1
where
    member1.age = (
        select
            max(memberSub.age)
        from
            Member memberSub
    )


select
    member0_.member_id as member_i1_1_,
    member0_.age as age2_1_,
    member0_.team_id as team_id4_1_,
    member0_.username as username3_1_
from
    member member0_
where
    member0_.age=(
        select
            max(member1_.age)
        from
            member member1_
    )

서브 쿼리 goe 사용

/*
* 나이가 평균 이상인 회원
* */
@Test
void subQueryGoe() {
    QMember memberSub = new QMember("memberSub")
    List<Member> result = queryFactory
            .selectFrom(member)
            .where(member.age.goe(
                    JPAExpressions.select(memberSub.age.avg())
                            .from(memberSub)
            ))
            .fetch();
    assertThat(result).extracting("age").containsExactly(30, 40);
}

select
        member1
from
    Member member1
where
    member1.age >= (
        select
            avg(memberSub.age)
        from
            Member memberSub
    ) */


select
    member0_.member_id as member_i1_1_,
    member0_.age as age2_1_,
    member0_.team_id as team_id4_1_,
    member0_.username as username3_1_
from
    member member0_
where
    member0_.age>=(
        select
            avg(cast(member1_.age as double))
        from
            member member1_
    )

서브쿼리 여러 건 처리 in 사용

/*
 * 서브쿼리 여러 건 처리, in 사용
 * */
@Test
void subQueryIn() {
    QMember memberSub = new QMember("memberSub")
    List<Member> result = queryFactory
            .selectFrom(member)
            .where(member.age.in(
                    JPAExpressions
                            .select(memberSub.age)
                            .from(memberSub)
                            .where(memberSub.age.gt(10))
            ))
            .fetch();
    assertThat(result).extracting("age").containsExactly(20, 30, 40);
}

select
        member1
from
    Member member1
where
    member1.age in (
        select
            memberSub.age
        from
            Member memberSub
        where
            memberSub.age > ?1
    )


select
    member0_.member_id as member_i1_1_,
    member0_.age as age2_1_,
    member0_.team_id as team_id4_1_,
    member0_.username as username3_1_
from
    member member0_
where
    member0_.age in (
        select
            member1_.age
        from
            member member1_
        where
            member1_.age>?
    )

select 절에 subquery

@Test
void selectSubQuery() {
    QMember memberSub = new QMember("memberSub")
    List<Tuple> result = queryFactory
            .select(member.username,
                    JPAExpressions
                            .select(memberSub.age.avg())
                            .from(memberSub)
            )
            .from(member)
            .fetch()
    for (Tuple tuple : result) {
        System.out.println("tuple = " + tuple);
    }
}

결과

tuple = [member1, 25.0]
tuple = [member2, 25.0]
tuple = [member3, 25.0]
tuple = [member4, 25.0]

select
        member1.username,
        (select
            avg(memberSub.age)
        from
            Member memberSub)
    from
        Member member1


select
    member0_.username as col_0_0_,
    (
      select
        avg(cast(member1_.age as double))
      from
        member member1_) as col_1_0_
from
    member member0_

static import 활용

import static com.querydsl.jpa.JPAExpressions.select;

List<Member> result = queryFactory
        .selectFrom(member)
        .where(member.age.eq(
                select(memberSub.age.max())
                        .from(memberSub)
         ))
         .fetch();

from 절의 서브쿼리 한계

JPA JPQL 서브쿼리의 한계점으로 from 절의 서브쿼리(인라인 뷰)는 지원하지 않는다.
당연히 Querydsl도 지원하지 않는다.(Querydsl은 JPQL의 빌더 역할을 하기 때문)
하이버네이트 구현체를 사용하면 select 절의 서브쿼리는 지원한다.
Querydsl도 하이버네이트 구현체를 사용하면 select 절의 서브쿼리를 지원한다.

from 절의 서브쿼리 해결방안

1. 서브쿼리를 join으로 변경한다.(가능한 상황도 있고, 불가능한 상황도 있다.)
2. 애플리케이션에서 쿼리를 2번 분리해서 실행한다.
3. nativeSQL을 사용한다.

from절 서브쿼리를 이용해서 View에 맞게 데이터를 가공하는 방법도 있지만, DB는 데이터를 가져오는 용도로 사용해야지 View에 맞게 데이터를 가공하는 것은 DB에서 하지 말고 애플리케이션 내부에서 하는 것이 좋다.

인프런 김영한 지식공유자님 강의 : 실전! Querydsl