SQL Subquery(부속질의) 정리

정말 중요하고 다분하게 사용되는 부속질의(subquery)에 대해서 정리하는 시간을 가져보겠습니다.

 

 

부속질의(subquery)란  

• 하나의 SQL문 안에 다른 SQL문이 중첩된 질의 
• 다른 테이블에서 가져온 데이터로 현재 테이블에 있는 정보를 찾거나 가공할 때 사용합니다.
• 데이터가 대량일 때 데이터를 모두 합쳐서 연산하는 조인보다 필요한 데이터만 찾아서 공급해주는 부속질의가 성능이 더 좋습니다
•  주질의 (main query, 외부질의)와 부속 질의 (subquery , 내부질의)로 구성됩니다.

 

부속질의(subquery)의 종류

 

스칼라 부속질의( scalar subquery)

• select절 안에서 사용되는 부속질의로, 부속질의의 결과 값을 단일 행, 단일 열의 스칼라 값으로 반환합니다. 
• 원칙적으로 스칼라 값이 들어갈 수 있는 모든 곳에 사용 가능하며, 일반적으로 SELECT 문과 UPDATE SET 절에 사용됩니다.
• 주질의와 부속질의와의 관계는 상관/비상관 모두 가능합니다. 

select id, 
( select name from customer where price > 10000 )
from order
group by id ;

 

상관 부속질의 (correlated subquery) 

• 투플을 이용하여 하위 부속질의를 계산합니다.
• 즉 상위 주질의와 하위 부속질의가 독립적 이지 않고 서로 관련을 맺고 있다고 할 수 있습니다.

select bookname 
from book b_1
where price > (select avg(price) 
				from book b_2
                where b_2.publisher = b_1.publisher) ;

위에 쿼리를 예를 들면 book테이블에 같은 출판사의 책들이 있는데 서로 가격이 다를 경우 상관 부속질의를 사용하여 같은 출판사의 책들 끼리의 평균 값을 구할 수 있습니다. 

 

 

인라인뷰(inline view) 

• FROM절에서 사용되는 부속질의
• 테이블 이름 대신 인라인 뷰 부속질의를 사용하면 보통의 테이블과 같은 형태로 사용할 수 있습니다.
• 부족질의 결과 반환되는 데이터는 다중 행, 다중 열이어도 상관없습니다.
• 다만 가상의 테이블인 뷰 형태로 제공되어 상관 부속질의로 사용될 수는 없습니다.

# 고객번호가 2 이하인 고객의 판매액을 구하라 

select a.name, sum(b.saleprice) as tot
from (select cid, name
		from customer 
        where cid <= 2) b 
where a.cid = b.cid
group by a.name ;

 

중첩질의

• WHERE 절에서 사용되는 부속질의
• WHERE 절은 보통 데이터를 선택하는 저건 혹은 술어와 같이 사용이 됩니다.
• 중첩질의를 또 다른 말로 술어 부속질의 라고도 합니다.

# 비교 연산자 

# 평균 주문금액 이하의 주문에 대해서 주문번호와 금액을 구하라 

select id , price 
from orders 
where price <= (select avg(price) as avg_price 
				from orders) ;

# IN, NOT IN 

# 대한민국에 거주하는 고객에게 판매한 도서의 총 판매액 

select sum(price) as tot 
from orders 
where cid in (select cid 	
			 from customer
             where address like % 'korea') ;

# ALL , SOME(ANY)
# ALL은 모두 , SOME(ANY)는 어떠한(최소한 하나라도) 의미

# 3번 고객이 주문한 도서의 최고 금액보다 더 비싼 도서를 구입한 주문의 주문번호와 금액

select oid, price 
from orders
where price > all(select price 
				  from orders 
                  where cid = 3) ;

# EXISTS, NOT EXISTS
# 데이터의 존재유무를 확인하는 연산자 

# EXISTS 연산자로 대한민국에 거주하는 고객에게 판매한 도서의 총 판매액을 구하라 

select sum(price) as tot
from orders a 
where exists (select * 	
			  from customer b 
              where address like '%korea%' and b.cid = a.cid) ;