[DB] SubQuery

부속 질의 (subquery)

 

SELECT sum(saleprice)
FROM orderTable
WHERE custid=(SELECT custid
		FROM customerTable
		WHERE name = '박지성')

부속질의란

 

하나의 SQL 문 안에 다른 SQL 문이 중첩된 질의를 말함.

 

다른 테이블에서 가져온 데이터로 현재 테이블에 있는 정보를 찾거나 가공할 때 사용함.

 

보통 데이터가 대량일 때 데이터를 모두 합쳐서 연산하는 조인보다 데이터만 찾아서 공급해주는 부속질의가 성능이 더 좋음.

 

Join과 Subquery의 성능 차이는 case by case다.

 

데이터베이스 최적화, 인덱스 사용 여부, 쿼리 작성 방법 등 주어진 상황에 따라 다르므로 특정 상황에서는 JOIN이 더 효율적일 수 있고, 다른 상황에서는 서브쿼리가 더 효율적일 수 있다.

 

그래도 SCALAR Subquery는 사용하지 말자.

 

쿼리문을 느리게 만드는 주범이다.

 

그 이유는 스칼라 서브쿼리가 메인 쿼리의 각 행에 대해 실행되어야 하기 때문에, 데이터가 많을 경우 처리 시간이 길어질 수 있다.

 

만약 가능하다면 OVER함수 사용하자.

 

OVER 함수는 윈도우 함수의 일종으로, 각 행에 대해 집계 연산 없이 그룹 내에서 계산을 수행할 수 있다.

 

이를 통해 스칼라 서브쿼리의 사용을 피하고 성능을 향상시킬 수 있다.

 

SQL OVER 절

 

 

명칭	위치	영문과 동의어
스칼라 부속질의	SELECT 절	scalar subquery
인라인 뷰	FROM 절	inline view, table subquery
중첩질의	WHERE 절	nested subquery,predicate subquery

 

---

스칼라 부속질의

 

SELECT custid, (SELECT name
		FROM customerTable cs
		WHERE cs.custid = od.custid), SUM(saleprice)
FROM orderTable od
GROUP BY custid;

 

FROM orderTable od : 별칭, 줄임말 정도로 이해하자. orderTable을 od라고 줄여 부르겠다는 의미.

 

WHERE cs.custid = od.custid : 이 구문을 통해서 실제로 컴퓨터가 가장 먼저 읽고 처리하는 것은 FROM에서 테이블을 가져오는 것이란 걸 알 수 있다.

 

---

인라인 뷰

 

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

 

SELECT cs.name, SUM(od.saleprice) "total"
FROM (SELECT custid, name FROM customerTable WHERE custid <= 2) cs, orderTable od
WHERE  cs.custid=od.custid
GROUP BY cs.name;

고객번호가 2 이하인 고객의 판매액을 보이시오.(결과는 고객 이름과 고객별 판매액 출력)

 

최종 출력 값이 고객이름(cs.name)과 고객별 판매액(SUM(od.saleprice))이다.

 

FROM 절은 결국 cs 테이블에 연결되어 있다.

 

원하는 값을 출력하기 위해 cs 테이블에서 가져와야 할 값은 name과 customerTable.custid 다.

(WHERE cs.custid=od.custid 를 수행하기 위해서 customerTable.custid 필요)

 

---

중첩질의

 

WHERE 절은 보통 데이터를 선택하는 조건 혹은 술어(predicate)와 같이 사용됨.

 

그래서 중첩질의를 (서)술어 부속질의(predicate subquery)라고도 함.

 

술어	연산자	반환 행	반환 열	상관
비교	=, >, <, >=, <=, <, >	단일	단일	가능
집합	IN, NOT IN	다중	단일	가능
한정	ALL, SOME(ANY)	다중	단일	가능
존재	EXISTS, NOT EXISTS	다중	다중	필수

 

• 상관 서브쿼리(Correlated Subquery)
  : 메인 쿼리에 의존적인 서브쿼리로서, 서브쿼리 내부에서 메인 쿼리의 특정 열을 참조합니다.
  : 이 참조에 의해 서브쿼리의 실행이 메인 쿼리의 각 행에 대해 반복적으로 수행되게 됩니다.
  : 따라서 상관 서브쿼리는 실행 횟수가 많아 지는 반면, 결과의 정확성을 보장할 수 있어 복잡한 질의에 사용됩니다.

 

SELECT orderid, saleprice
FROM orderTable
WHERE saleprice <= (SELECT AVG(saleprice) FROM orderTable);

평균 주문금액 이하의 주문에 대해서 주문번호와 금액을 보이시오.

 

SELECT SUM(saleprice) "total"
FROM orderTable
WHERE custid IN (SELECT custid FROM customerTable WHERE address LIKE '%대한민국%');

대한민국에 거주하는 고객에게 판매한 도서의 판매 총액을 구하시오.

 

SELECT orderid, saleprice
FROM orderTable
WHERE saleprice > ALL (SELECT saleprice FROM orderTable WHERE custid='3');

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

ALL : 모두

SOME(ANY) : 최소한 하나라도

 

SELECT SUM(saleprice) “total”
FROM orderTable od
WHERE EXISTS (SELECT * FROM customerTable cs 
		WHERE address LIKE '%대한민국%' AND cs.custid=od.custid);

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

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

 

-- IN과 EXISTS의 차이점

SELECT SUM(saleprice) "total"
FROM orderTable od
WHERE EXISTS (SELECT * FROM customerTable cs 
		WHERE address LIKE '%대한민국%' AND cs.custid=od.custid);

SELECT SUM(saleprice) "total"
FROM orderTable od
WHERE custid IN (SELECT custid FROM customerTable cs 
		WHERE address LIKE '%대한민국%' AND cs.custid=od.custid);

SELECT SUM(saleprice) "total"
FROM orderTable od
WHERE custid IN (SELECT * FROM customerTable cs 
		WHERE address LIKE '%대한민국%' AND cs.custid=od.custid);
-- 오류
-- 하위 쿼리를 EXISTS로 정의하지 않은 경우에는 SELECT 목록에서 식을 하나만 지정할 수 있습니다.
-- IN은 부속질의로 SELECT * (복수의 데이터 추력)를 사용할 수 없다.