17. 서브 쿼리

그 왜 SQL에서 막 쓰다가 () 괄호 하고 새 쿼리 쓰는 그걸 서브 쿼리라고 함.

일단 서브쿼리를 사용할 때는 JPAExpressions, 직역은 JPA 표현식을 이용

//    나이가 가장 많은 회원 조회
@Test
public void subQuery(){
    QMember memberSub = new QMember("memberSub");
    Member member = query.selectFrom(m).orderBy(m.age.desc()).limit(1).fetchOne();
    System.out.println("member = " + member.getAge());

    List<Member> result = query.selectFrom(m).
            where(
                    m.age.eq(
                            JPAExpressions.
                                    select(memberSub.age.max()).from(memberSub)
                    )
            ).fetch();

    assertThat(result.get(0).getAge()).isEqualTo(40);
}

사실 저 member 처럼 그냥 저렇게 해도 되긴 하는데, 서브쿼리 연습용이라,

보면 다 똑같은데, 서브쿼리 하고 싶은 부분만 JPAExpressions.하면서 시작. 똑같음. 저것도 쿼리 팩토리라고 생각하면 됨.

 

일단 멤버를 조회해 오는데, 멤버의 나이가 서브쿼리해서 얻은 멤버의 최고 나이랑 같은거.

 

지금 멤버를 @BeforeEach로 10,20,30,40 해서 넣어둠.

 

지금 보면 서브쿼리용 별칭을 따로 생성해서 사용함. 사실 그냥 m 써도 결과는 같음.

근데 이렇게 하는 이유는, 지금 쿼리메소드들 같은 경우에는 충돌 날 여지가 없지만 혹시라도 충돌이 날 수 있고, 

또 서브쿼리는 이렇게 명확하게 독립적으로 별칭을 사용해서 메인 쿼리와 나누는 게 유지보수 면에서도 명확하게 알 수 있음.

 

여튼 저렇게 서브쿼리를 날리면, max() 함수를 DB에 요청해서 member중 제일 나이가 많은 숫자를 가지고 오게 될거임.

그거를 m.age = 

where m.age = (서브쿼리)

 

나가는 jpql을 예상해 보자면

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

이럴 까 함.

 

가장 많은 게 40이라 테스트는 통과.

/* select
    member1 
from
    Member member1 
where
    member1.age = (
        select
            max(memberSub.age) 
        from
            Member memberSub
    ) */ select
        m1_0.member_id,
        m1_0.age,
        m1_0.team_id,
        m1_0.username 
    from
        member m1_0 
    where
        m1_0.age=(
            select
                max(m2_0.age) 
            from
                member m2_0
        )

같음.

 

@Test
public void subQueryGoe(){
    QMember memberSub = new QMember("memberSub");
    List<Member> result = query.selectFrom(m).where(m.age.goe(JPAExpressions.select(memberSub.age.avg()).from(memberSub))).fetch();
    assertThat(result).extracting("age").containsExactly(30,40);
}

나이 평균 이상만.

위랑 비슷한데 조건만 약간 달라짐.

member를 얻어오는데 where하고 조건이 멤버의 나이가 크거나 같은 거

멤버의 평균보다. 

select m from Member m where m.age >= (select avg(memberSub.age) from Member memberSub )

/* select
    member1 
from
    Member member1 
where
    member1.age >= (
        select
            avg(memberSub.age) 
        from
            Member memberSub
    ) */ select
        m1_0.member_id,
        m1_0.age,
        m1_0.team_id,
        m1_0.username 
    from
        member m1_0 
    where
        m1_0.age>=(
            select
                avg(cast(m2_0.age as float(53))) 
            from
                member m2_0
        )

같음.

 

이제 약간 다름.

//   멤버 중 10살 초과인 멤버들만 출력
@Test
public void subQueryIn(){
    QMember memberSub = new QMember("memberSub");

    List<Member> result = query.
            selectFrom(m).where(
                    m.age.in(
                            JPAExpressions.
                                    select(memberSub.age).from(memberSub).where(memberSub.age.gt(10))
                    )
            ).fetch();
    assertThat(result).extracting("age").containsExactly(20,30,40);
}

이번엔 서브쿼리로 여러개의 row를 가져올 것이고, 그걸 in의 조건으로 만듦.

이것도 사실 select m from Member m where m.age > 10 하면 되긴 하는데..

 

일단 쿼리메소드 분석을 해 보자면

멤버에서 멤버들을 가져오는데,

조건이

멤버의 나이가

in

멤버의 나이들 중 10 초과인 멤버들의 나이를 row들로 여러 개 가져옴. 

 

jpql로 바꿔보면

select m from Member m where m.age in (select memberSub.age from Member memberSub where memberSub.age > 10)

/* select
    member1 
from
    Member member1 
where
    member1.age in (select
        memberSub.age 
    from
        Member memberSub 
    where
        memberSub.age > ?1) */ select
        m1_0.member_id,
        m1_0.age,
        m1_0.team_id,
        m1_0.username 
    from
        member m1_0 
    where
        m1_0.age in (select
            m2_0.age 
        from
            member m2_0 
        where
            m2_0.age>?)

같음.

 

@Test
public void selectSubQuery(){
    QMember memberSub = new QMember("memberSub");

    List<Tuple> result = query.select(m.username,
            JPAExpressions.select(memberSub.age.avg()).from(memberSub)
    ).from(m).fetch();

    for (Tuple tuple : result) {
        System.out.println("tuple = " + tuple);
    }
}

이거는 select 절에서 서브쿼리로 평균과 같이 가져오는 거.

 

멤버의 이름과, 서브쿼리로 멤버들 나이의 평균을 같이 가져옴.

 

jpql

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

/* select
    member1.username,
    (select
        avg(memberSub.age) 
    from
        Member memberSub) 
from
    Member member1 */ select
        m1_0.username,
        (select
            avg(cast(m2_0.age as float(53))) 
        from
            member m2_0) 
    from
        member m1_0

같음.

 

출력 결과는

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

의도했던 대로 멤버의 이름과, 나이 평균이 나옴.

 

참고로, JPAExpressions는 정적 import가 가능함.

 

 

지금까지 보면, where 절과 select 절에만 사용을 했음.

where절이 가능하다면 비슷하게 on절도 아마 가능할꺼임.

 

근데 아직 from 절에는 '공식적인 지원'은 안함.

6.1부터 추가되기는 했으나, 아직 이슈가 있어 공식적인 지원은 안함.

 

jpql 자체에서 from절에 서브쿼리를 지원하지 않는다.

 

원래 JPA에서는 select 절 서브쿼리도 안된다고 한다. 그러니까, 표준 스펙은 아니라고 한다.

그런데 우리는 그 JPA 인터페이스를 구현한 구현체인 하이버네이트를 사용하고 있는데, 하이버네이트에서는 select절의 서브쿼리를 지원한다. QueryDSL도 하이버네이트를 사용.

 

 

그래서, 여튼 공식적으로는 from절에 서브쿼리를 지원하지 않기 때문에, 해결방법은

서브쿼리는 보통 join으로 변경이 왠만하면 가능하다. (불가능한 상황도 있긴 하다.)

그 다음, 위에 보다는 비효율적이지만, 쿼리를 2번 나눠서 실행.

그래도 안된다면 결국 네이티브 쿼리