Featured image for [DB] SubQuery

[DB] SubQuery

작성자:

부속 질의 (subquery)

SELECT sum(saleprice)
FROM orderTable
WHERE custid=(SELECT custid
		FROM customerTable
		WHERE name = '박지성')
Code language: JavaScript (javascript)

부속질의란

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

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

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

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

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

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

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

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

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

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

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

SQL OVER 절

SQL OVER 절

드디어 over, partition by절 이해하고 적용해본 기념으로 쓰는 글! 실제로 내용을 이해했던 흐름으로 작성해보자!물품 A의 재고관리를 위해 다음과 같은 테이블 “창고”가 있다고 가정해 보자.위 테이블은 창고에 A가 언제, 얼마나 입/출고 되었는지 나타낸다.

명칭위치영문과 동의어
스칼라 부속질의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;
Code language: HTML, XML (xml)

고객번호가 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 '%대한민국%');
Code language: JavaScript (javascript)

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

SELECT orderid, saleprice
FROM orderTable
WHERE saleprice > ALL (SELECT saleprice FROM orderTable WHERE custid='3');
Code language: JavaScript (javascript)

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);
Code language: JavaScript (javascript)

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 * (복수의 데이터 추력)를 사용할 수 없다.
Code language: JavaScript (javascript)

댓글 남기기