11장 조인 - 서브 쿼리

1) 서브 쿼리 eq 사용

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

2) 서브 쿼리 goe 사용

/**
 * 나이가 평균 나이 이상인 회원
 */
@Test
public void subQueryGoe() throws Exception {
 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);
}

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

/**
 * 서브쿼리 여러 건 처리, in 사용
 */
@Test
public void subQueryIn() throws Exception {
 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);
}

4) select 절에 subquery

List<Tuple> fetch = queryFactory
 .select(member.username,
 JPAExpressions
 .select(memberSub.age.avg())
 .from(memberSub)
 ).from(member)
 .fetch();
for (Tuple tuple : fetch) {
 System.out.println("username = " + tuple.get(member.username));
 System.out.println("age = " +
tuple.get(JPAExpressions.select(memberSub.age.avg())
 .from(memberSub)));
}

5) 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 절의 서브쿼리 해결방안

1. 서브쿼리를 join으로 변경한다. (가능한 상황도 있고, 불가능한 상황도 있다.)

2. 애플리케이션에서 쿼리를 2번 분리해서 실행한다.

3. nativeSQL을 사용한다.