서브쿼리

 

 서브쿼리란, SQL 쿼리 안에 있는 또다른 SQL 쿼리를 뜻한다. 또한 검색할 데이터를 추가로 제한하는 조건으로 main 쿼리에서 사용할 데이터를 반환할 때 사용한다. 서브쿼리는 SELECT, INSERT, UPDATE, DELETE문을 사용할 수 있다. 또한 대표적으로 SELECT의 경우, SELECT, WHERE, FROM 3군데에서 사용한다.

 

*서브쿼리 특징

- 만약 main 쿼리에서 여러개의 칼럼이 필요하지 않다면, SELECT 에서는 1개의 칼럼값만 가질 수 있다. 

- main 쿼리에서 ORDER BY를 사용할 수 있더라도, 서브쿼리에서는 ORDER BY를 사용할 수 없다.

- main 쿼리에서 GROUP BY를 사용하듯이, 서브쿼리에서 GROUP BY를 사용할 수 있다.

- 1개 이상의 데이터를 반환하는 서브쿼리는 IN 연산자와 사용할 수 있다.

- 서브쿼리 안에서는 BETWEEN을 사용할 수 있지만, BETWEEN 연산자 안에 서브쿼리를 사용할 수 없다.

 

 

나이가 평균보다 많은 회원

 

SELECT m FROM Member m WHERE m.age > (SELECT AVG(m2.age) FROM Member m2);

 

 

한건이라도 주문한 고객

 

SELECT m FROM Member m WHERE (SELECT COUNT(o) From Order o WHERE m = o.member) > 0

 

=> JPA에서는 객체로 비교가 가능하기 때문에 WHERE m = o.member 로 사용이 가능하다.

 

 

서브쿼리 지원 함수

EXISTS - 1개라도 존재한다면

ALL - 모든 경우

ANY - 1개 이상의 경우

SOME

IN - 여러개 중에서

 

 

* 팀A 소속인 회원

 

SELECT m FROM Member m WHERE exists (SELECT t FROM m.team t WHERE t.name = '팀A')

 

 

* 전체 상품 각각의 재고보다 주문량이 많은 주문들

 

SELECT o FROM Order o WHERE o.orderAccount > ALL(SELECT p.stockAccount FROM Product p)

 

 

*어떤 팀이든 팀에 소속된 회원

 

SELECT m FROM Member m WHERE m.team = ANY(SELECT t FROM Team t)

 

 

* JPA에서 서브쿼리의 한계

 

JPA는 SELECT(HIBERNATE 지원), WHERE, HAVING 절에서만 서브 쿼리 사용이 가능하다.

JPA 서브 쿼리 한계는 FROM 절의 서브쿼리는 JPQL에서 불가능하다

 

 

*한계를 극복하는 방법

 

1. 조인으로 해결한다.

1. 어플리케이션으로 모든 결과 조회한 후 가져와서 튜닝한다.

2. 쿼리를 2번 날려서 튜닝한다.

 

 

*JPQL 타입 표현

 

문자 : 'HELLO', 'Shes''s'

 

숫자 : 10L(Long), 10D(Doube), 10F(Float)

 

Boolean : TRUE, FALSE

 

ENUM : jpabook.MemberType.Admin(패키지명 추가)

 

엔티티타입 : TYPE(m) = Member(상속관계에서 사용)

 

List<Object[]> resultList = em.createQuery("select m.name, 'HELLO', TRUE from Member m")
.getResultList();

Object[] result = resultList.get(0);
System.out.println(result[0]);
System.out.println(result[1]);
System.out.println(result[2]);

 

 

*ENUM

 

List<Object[]> resultList = 
em.createQuery("select m.name, 'HELLO', TRUE from Member m where m.type = jpql.MemberType.USER")
.getResultList();

Object[] result = resultList.get(0);
System.out.println(result[0]);
System.out.println(result[1]);
System.out.println(result[2]);

 

WHERE절에서 ENUM타입은 해당 경로의 패키지명까지 입력을 해야 한다.

 

List<Object[]> resultList = 
em.createQuery("select m.name, 'HELLO', TRUE from Member m where m.type := userType")
.setParameter("userType", MemberType.USER)
.getResultList();

Object[] result = resultList.get(0);
System.out.println(result[0]);
System.out.println(result[1]);
System.out.println(result[2]);

 

따라서, 대입하여 사용할 수 있는 형태로 만들어 setParameter로 설정한다.

 

 

*상속 

 

List<Item> resultList = em.createQuery("select i FROM Item i WHERE type(i)=Book", Item.class)
.getResultList();

 

@DiscriminatorColumn과 @DiscriminatorValue를 적절하게 조합해서 상속관계에 JPQL을 사용한다. type(i) = Book에서 @DiscrminatorValue값을 넣는 것이 아니라 클래스명 입력을 유의한다.

 

 

*출처

https://www.tutorialspoint.com/sql/sql-sub-queries.htm