where절 서브쿼리

서브쿼리

란 SQL 안에 포함되는 SELECT 문으로

 

검색문의 where절, select절 (scalar subquery), from절(inline view), having절, order by절 에서 사용 가능하고

갱신문의 insert문의 values절, update문의 set절 등에서 사용 가능하다.

 

경험상 where절, select절의 스칼라 서브쿼리, from 등에 많이 사용했던 것 같다.

 

주의사항

 

1. 컬럼 참조의 제한

- 일반적으로 서브쿼리는 메인 쿼리 컬럼을 참조 가능하지만 메인쿼리는 서브쿼리의 컬럼을 참조할 수 없다.

- from절 서브쿼리(inline view)의 경우 당연하게도 메인쿼리에서 서브쿼리를 자유롭게 참조할 수 있다.

 

2. order by절 사용의 제한

- where절 서브쿼리에서는 order by 절을 사용하지 못한다.

- inline view는 가능하다.

 

참고로 쿼리 실행순서는 다음과 같다.

from - where - group by - having - select - order by

 

WHERE절 서브쿼리

 

반환되는 결과에 따른 분류가 있을 수 있다.

 

연산자에 따른 분류

1. 단일값 서브쿼리(scalar subquery)

- 결과가 1개 이하의 단일값으로 단일값 비교연산자를 사용한다(=, <>, <, <=, >, >=)

SELECT
FROM
WHERE expr 비교연산자 (subquery)

2. 다중값 서브쿼리(multy-value subquery)

- 결과가 단일값의 집합(1개의 튜플)으로  단일값 비교연산자에 한정자(ANY, SOME, ALL)를 사용한다

SELECT
FROM
WHERE expr 비교연산자 {ANY|SOME|ALL} (subquery)
// ANY와 SOME은 같다고 보면 된다. 서브쿼리 결과에 존재하는 어느 한 값의 만족 여부를 확인
// ALL은 서브쿼리 결과에 존재하는 모든 값의 만족 여부를 확인

 

3. 다중행 서브쿼리(table subquery)

- 서브쿼리 결과가 n-tuple의 집합으로 IN(멤버십 연산자)이나 EXISTS(존재 정량자)를 사용한다.

SELECT
FROM
WHERE expr [NOT] {IN|EXISTS} (subquery)
// IN: 서브쿼리 결과에 존재하는 투플의 존재 여부를 확인(multiple or)
// EXISTS: 서브쿼리의 결과를 만족하는 투플 존재 여부, 1개 찾으면 더이상 검색 X

 

동작하는 방식에 따른 분류

1. 비연관 서브쿼리

- 서브쿼리가 메인쿼리 컬럼을 가지고 있지 않은 형태로 서브쿼리는 한번 실행된다.

2. 연관 서브쿼리

-  서브쿼리가 메인쿼리 컬럼을 가지고 있는 형태로 튜플마다 서브쿼리가 반복 실행된다.(조인과 유사)

메인쿼리 테이블을 필터링 하기 위해 사용한다.

 

결론적으로 6개 종류의 서브쿼리가 나올 수 있다.

1. 비연관, 단일값 서브쿼리

2. 비연관, 다중값 서브쿼리

3. 비연관, 다중행 서브쿼리

4. 연관, 단일값 서브쿼리

5. 연관, 다중값 서브쿼리

6. 연관, 다중행 서브쿼리

 

비연관 서브쿼리

 

1. 비연관, 단일값 서브쿼리

select name, MSRP
  from products
 where MSRT >= (
 		select AVG(MSRP) * 2
                  from products
                )
 order by MSRP;

MSRP(권장소비자 가격)가 MSRP 평균의 2개 이상인 상품의 이름, MSRP를 검색

굳이 서브쿼리를 안쓰자면

with temp as(
	select avg(MSRP) * 2 as AVGMSRP
	from products
)
select name, MSRP
  from products, temp
 where MSRP >= temp.AVGMSRP
 order by MSRP;

이렇게도 사용할 수 있겠다.

 

2. 비연관, 다중값 서브쿼리

select officeCode, city
from offices
where officeCode = (
                select officeCode 
                from employees
                where lastName = 'Patterson'
                )
order by officeCode;

성이 Patterson인 직원이 근무하는 지점을 검색하고자 한다. 하지만 얘는 메롱이다. 서브쿼리가 다중값 서브쿼리인데 이퀄 비교를 했으니 에러가 뜬다.

select officeCode, city
  from offices
 where officeCode = ANY (
            select officeCode 
            from employees
            where lastName = 'Patterson'
            )
 order by officeCode;

요로코롬 적어주면 다중값을 비교해줄 수 있다.

여기서 약간의 발상의 전환을 해보면 다중행 서브쿼리를 이해할 수 있다.

select officeCode, city
  from offices
 where officeCode IN (
            select officeCode 
            from employees
            where lastName = 'Patterson'
            )
 order by officeCode;

where문의 officeCode를 하나의 투플로 생각하고 서브쿼리가 scalar의 집합 즉 1 투플이면 IN으로 대체할 수 있다.

 

3. 비연관, 다중행 서브쿼리

각 상품라인에서 권장 소비자가격이 가장 저렴한 상품을 검색할 때

select productLine, name, MSRP
  from products
 where (productLine, MSRP) in (
                    select productLine, MIN(MSRP)
                    from products
                    group by productLine 
                )
 order by productLine, name;

요래 적어주면 다중행 서브쿼리를 비교할 수 있다.

쿼리를 분리해서 실행결과를 보면

select productLine, MIN(MSRP)
  from products
 group by productLine;

위 서브쿼리의 결과는 다중행 서브쿼리로 아래와 같이 나오고

이 쿼리로 전체 쿼리와 IN 비교하면

다음과 같은 최종결과가 나온다.

 

물론 위의 쿼리도 조인으로 변환 가능하다.

with temp as
(
	select productLine, min(MSRP) as MIN_MSRP
	  from products
	 group by productLine
)
select p.productLine, p.name, p.MSRP
  from products p 
       join temp t on (p.productLine = t.productLine)
       and p.MSRP = t.MIN_MSRP 
 order by p.productLine, p.name;

하지만 속도를 생각해보면 서브쿼리를 사용하는게 훨씬 빠르다.

비연관 서브쿼리이기 때문에 서브쿼리가 한번만 실행되고 

서브쿼리 실행의 결과로 나온 튜플의 집합을 IN 연산자를 이용해 비교하면 내부 알고리즘을 이용하지만

두 테이블을 조인하기 위해서는 이중 for문을 이용하기 때문에 효율이 떨어진다.

 

 

연관 서브쿼리

이제 WHERE절 서브쿼리에서 연관 서브쿼리에 대해 이야기해보자면

서브쿼리에 메인쿼리 테이블의 컬럼을 사용해서 조인조건을 서브쿼리의 WHERE절에 사용한다.

주로 메인쿼리 테이블의 투플을 필터링할때 사용한다.(조인과 유사하나 결과는 메인쿼리 테이블의 부분집합이다)

 

메인쿼리 테이블의 투플마다 서브쿼리를 실행한다.

 

4. 연관, 단일값 서브쿼리

select productLine, name, MSRP
  from products X
 where MSRP = (
            select MIN(MSRP)
              from products Y
             where Y.productLine = X.productLine
            )
 order by productLine, name;

각 상품라인에서 권장 소비자 가격이 가장 저렴한 상품을 선택하는 쿼리다.

다시한번 상기해보자면 연관 서브쿼리는 서브쿼리가 메인쿼리 컬럼을 가지고 있는 형태로

1. 메인 쿼리의 products에서 productLine 투플을 하나 골라서

2. 서브쿼리의 비교문으로 가져와서 X.productLine, Y.productLine을 비교한다.

3. 충족되는 값을 가지고 와서 또 다시 메인쿼리의 where문 비교를 하는 nested loop 연산을 하게 된다.

 

뭔가 비슷한 쿼리를 쓴 기억이 있다. 연관서브쿼리는 비연관 서브쿼리로 변환이 가능하다. 서브쿼리가 한번만 실행되므로 아래 쿼리가 훨씬 효율적일 것이다.

select productLine, name, MSRP
  from products
 where (productLine, MSRP) in (
                    select productLine, MIN(MSRP)
                    from products
                    group by productLine 
                )
 order by productLine, name;

이 친구는 연관 서브쿼리다. 어떻게 비연관으로 연결할 수 있을까?

select productLine, name, MSRP
  from products x
 where MSRP < (
            select AVG(MSRP)
              from products Y
             where Y.productLine = X.productLine
            )
 order by productLine, MSRP desc;

 

당연히 이렇게 하면 여러 튜플을 대입했기 때문에 에러가 뜬다.

 

select productLine, name, MSRP
  from products x
 where MSRP < (
            select AVG(MSRP)
              from products y
             group by productLine
            )
order by productLine, MSRP desc;

 

from절 서브쿼리로 해결할 수 있을 것 같은데 잠시 후 알아보도록 하자.

 

5. 연관, 다중값 서브쿼리

 

상태가 'Cancelled' 혹은 'On Hole'인 주문을 한 고객

select name
  from customers c 
 where customerId = ANY(
                    select customerId 
                      from orders o 
                     where o.customerId = c.customerId
                       and o.status in ('Cancelled', 'On Hold')
                    );

각각의 customer의 n개의 투플에 대해 모든 orders의 m개의 n * m 만큼 실행되고 ANY에 걸리면 return 된다.

 

6. 연관, 다중행 서브쿼리

select name
  from customers c 
 where exists (
            select *
              from orders o
             where o.customerId = c.customerId 
               and year(orderDate) = 2003
               and month(orderDate) = 1
            );

exists는 항상 연관 서브쿼리로 사용되며 만약 조건을 만족하는 1개의 투플만 찾으면 추가적인 검색을 하지 않는다.

 

 

연관 서브쿼리와 조인의 차이

 

조인

- 조인은 두 테이블의 카티션 프로덕트의 부분집합을 리턴한다. 즉, 결과의 갯수는 최대 n * m이 된다.

- 질의결과에 두 테이블의 컬럼을 모두 포함해야 한다면 조인을 사용해야 한다.

 

- 연관 서브쿼리는 메인쿼리 테이블의 부분집합을 리턴한다.

- 메인쿼리의 투플 수가 n이면 생성되는 최대 수는 n개가 된다.