[SQL] 서브쿼리

지수·2023년 4월 2일

TIL sql

플레이데이터

목록 보기

13/50

[ 실습 내용 ]

5. 서브쿼리(subquery)

: 메인 쿼리(main query) 전에 실행되어 새로운 데이터 테이블을 하나 생성하는 부수적 쿼리, 서브쿼리의 결과는 메인 쿼리에서 사용됨
= SELECT문 안에 SELECT문이 하나 더 있는 것

서브쿼리는 괄호로 묶어서 표현
가독성을 위해 비교 조건 오른쪽에 서브쿼리 배치

위치에 따른 서브쿼리 종류

SELECT절에 있는 서브쿼리 : 스칼라 서브쿼리 (단일 행)
FROM절에 있는 서브쿼리 : 인라인 뷰 (SELECT문에서 내부 컬럼 활용 가능)
WHERE절에 있는 서브쿼리 : 서브쿼리

반환값에 따른 서브쿼리 종류

단일 행 서브쿼리(single-row subquery) : 서브쿼리의 결과가 1행
다중 행 서브쿼리(multiple-row subquery) : 서브쿼리의 결과가 여러 행
다중 컬럼 서브쿼리(multiple-column subquery) : 서브쿼리의 결과가 여러 컬럼

single-row subquery

: 서브쿼리를 통해 검색된 값이 단일한 값일 때
▶ 단일 행 연산자(=,>,<,<>...) 사용

multiple-row subquery

: 서브쿼리를 통해 검색된 값이 여러 개의 값일 때
▶ 다중 행 연산자(IN, ANY, ALL,...) 사용

EXISTS : 서브쿼리와 함께 쓰임, 서브쿼리 값이 하나라도 존재하면, 서브쿼리 값이 true면 true
NOT EXISTS : 서브쿼리와 함께 쓰임, 서브쿼리 결과값이 하나도 존재하지 않으면, 서브쿼리 값이 false면 true
⛔IN처럼 비교값을 하나하나 다 대응해서 확인해보지 않음⛔

IN : 다수의 비교값과 비교하여 비교값 중 하나라도 같은 값이 있다면 true (OR로 연결됨)
NOT IN : 다수의 비교값과 비교하여 비교값 전체에 하나도 같은 값이 없어야 true (AND로 연결됨)
⛔비교값이 NULL 포함하면 안됨⛔

ANY : 다수의 비교값 중 하나라도 만족하면 true (OR로 연결됨)
> ANY, < ANY, >= ANY, <= ANY, = ANY
= ANY는 IN과 같은 의미
ALL : 다수의 비교값 전체 모두 만족하면 true (AND로 연결됨)
> ALL, < ALL, >= ALL, <= ALL, != ALL
!= ALL는 NOT IN과 같은 의미

correlated subquery

: 서브쿼리가 메인 쿼리 값을 사용하여 검색하고, 그 값을 메인 쿼리가 받아서 검색하는 구조의 쿼리
서브쿼리 안에 메인 쿼리 값을 가져오는 부분이 포함되어 있음

SELECT m.empno, m.ename, m.sal, m.deptno, m.mgr
FROM emp m 
WHERE m.deptno IN (10, 20, 30)
AND m.sal >= (SELECT AVG(s.sal) 
	      FROM emp s  
	      WHERE m.deptno = s.deptno)
ORDER BY m.deptno;

coreelated subquery 사용 예시

[ 복습 문제 풀이 ]

Q1. EMP 테이블에 포함된 사원 정보 중, 급여를 많이 받는 사원 3명을 아래와 같이 출력하시오 (단, 동일한 급여를 받는 사원이 존재한다면 함께 출력하시오)⭐⭐

SELECT empno, ename, sal, deptno
FROM emp
WHERE sal IN (SELECT sal
              FROM (SELECT DISTINCT sal 
                    FROM emp ORDER BY sal DESC)
              WHERE ROWNUM <= 3);

sal을 중복없이 내림차순으로 정렬하는 서브쿼리 생성
위의 서브쿼리를 3번째 행까지만 출력하는 서브쿼리 생성
두 과정을 통해 급여 값 top3만 담은 서브쿼리가 만들어짐
전체 emp 테이블에서 sal 값이 위의 서브쿼리 안에 있는 경우 검색

Q2. DEPT, EMP 테이블을 사용하여 각 부서의 소속 사원 유무를 확인하는 검색 결과를 만드시오. EMP 컬럼은 소속 사원이 존재할 때 'YES', 아니면 'NO'를 검색합니다.⭐⭐

SELECT d.deptno, d.dname, d.loc, NVL2(e.cnt, 'YES', 'NO') AS emp
FROM dept d, (SELECT deptno, COUNT(empno) AS cnt
              FROM emp
              GROUP BY deptno) e
WHERE d.deptno = e.deptno(+)
ORDER BY d.deptno;

부서별 소속 사원 수를 파악하는 서브쿼리 생성
(조인 조건이 될 deptno와 사원 수 반환)
해당 서브쿼리와 부서 정보를 가지고 있는 dept 테이블 조인
소속 사원이 없는 부서도 출력해야하기 때문에 LEFT OUTER JOIN 실행
숫자값과 null이 함께 출력된 cnt를 NVL2() 함수로 변환
NVL2(기존 값, null이 아니면 출력할 값, null이면 출력할 값)

Q3. EMPLOYEES 테이블을 사용하여, 각 부서에서 최고 급여를 받는 사원과 동일한 급여를 받는 사원의 사원 번호, 이름, 부서 번호, 급여를 아래와 같이 조회하시오 (단, 부서 번호가 NULL인 사원은 제외)⭐

SELECT employee_id, first_name, department_id, salary
FROM employees
WHERE salary IN (SELECT MAX(salary)
                 FROM employees
                 GROUP BY department_id)
AND department_id IS NOT NULL
ORDER BY department_id;

부서별 최고 급여를 구하는 서브쿼리 생성
(IN 연산자를 통해 salary 값과 비교할 것이기 때문에 department_id는 SELECT 하지 않음 => multiple-row subquery)
전체 사원 중 급여가 위의 서브쿼리 안에 있는 값과 동일한 사람 검색
부서 번호가 null인 사원 제외

Q4. EMPLOYEES 테이블을 사용하여, 부서별 최고 급여를 받는 사원의 사원 번호, 이름, 부서 번호, 급여를 아래와 같이 조회하시오⭐

SELECT employee_id, first_name, department_id, salary
FROM employees
WHERE (department_id, salary) IN (SELECT department_id, MAX(salary)
                                  FROM employees
                                  GROUP BY department_id)
AND department_id IS NOT NULL
ORDER BY department_id;

부서별 최고 급여를 구하는 서브쿼리 생성
(department_id, salary를 모두 비교해야하므로, 두 컬럼 반환 => multi column subquery)
동일 부서 사원 중 급여가 위의 서브쿼리 안에 있는 값과 동일한 사람 검색
부서 번호가 null인 사원 제외

ADSQL 1. 고객 테이블(custs)을 이용하여 다음 조건에 맞는 고객을 검색하세요

가) 검색 컬럼
고객번호, 이름, 전화번호, 신용한도, 성별
나) 조건
① 신용한도가 5000이상 9000 이하인 고객
② 성별 컬럼이 여성(F)이거나 NULL인 고객
③ 위 두 조건을 모두 만족해야 합니다.

SELECT cust_id, fname, phone, credit_limit, gender
FROM custs
WHERE credit_limit BETWEEN 5000 AND 9000
AND (gender = 'F' OR gender IS NULL);

ADSQL 2. 고객 테이블(custs)과 주문 테이블(orders)을 이용하여 다음 조건의 결과를 검색하세요

가) 검색 컬럼
고객이름(lname), 주문번호, 주문일자, 전화번호, 주문 금액
나) 조건
① online으로 주문한 고객

SELECT c.lname, o.order_id, o.order_date, o.order_total
FROM custs c, orders o
WHERE c.cust_id = o.cust_id
AND order_mode = 'online';

ADSQL 3. 주문 테이블(orders)을 활용하여 아래의 조건에 맞는 결과를 검색하세요

가) 검색 컬럼
판매사원번호, 고객번호, 주문 금액
나) 조건
① direct로 판매된 제품의 주문 금액의 평균 값 보다 주문 금액이 적은 경우
② online으로 주문된 경우는 제외
③ 위 두 조건을 모두 만족하는 조건 검색
다) 정렬
① 주문 금액을 내림차순 정렬

SELECT sales_rep_id, cust_id, order_total
FROM orders
WHERE order_total < (SELECT AVG(order_total)
                     FROM orders 
                     WHERE order_mode = 'direct'
                     GROUP BY order_mode)
AND order_mode <> 'online';

direct로 주문된 경우 주문 금액의 평균 값을 반환하는 서브쿼리 생성
=> single-row subquery
주문 금액이 위의 서브쿼리 값보다 적은 경우 검색
주문 방식이 online인 경우 제외

ADSQL 4. 상품 테이블(prods) 주문 테이블(orders) 주문 상세 테이블(order_items), 고객 테이블(custs)을 사용하여 상품을 구매했다가 취소한 고객의 번호와 이름, 상품번호, 주문일자, 주문했던 수량, 주문 단가 및 금액을 검색하세요⭐⭐⭐⭐

가) 검색 컬럼
고객번호, 이름, 상품번호, 상품이름, 주문일자, 주문 수량, 주문 단가, 주문 금액 (quantity*unit_price)
나) 조건
① 주문 취소 내역이 있는 정보 검색
다) 정렬
① 고객번호, 주문일자, 상품번호 컬럼으로 오름차순 정렬

-- 내가 푼 방법
SELECT c.cust_id, c.lname, 
    p.prod_id, p.prod_name,
    k.order_date, k.quantity, k.unit_price,
    (k.quantity * k.unit_price) AS amt
FROM custs c, prods p, (SELECT o.order_date, i.quantity, i.unit_price, o.cust_id, i.prod_id
                        FROM orders o, order_items i, order_cancel c
                        WHERE o.order_id = c.order_id
                        AND i.order_id = c.order_id
                        AND i.prod_id = c.prod_id) k
WHERE c.cust_id = k.cust_id
AND p.prod_id = k.prod_id
ORDER BY c.cust_id, o.order_date, p.prod_id;

-- EXISTS, Correlatede subquery를 활용해서 개선한 방법
SELECT c.cust_id, c.lname,
    p.prod_id, p.prod_name,
    o.order_date, oi.quantity, oi.unit_price,
    (oi.quantity * oi.unit_price) AS amt
FROM custs c, prods p, orders o, order_items oi
WHERE oi.prod_id = p.prod_id
AND o.order_id = oi.order_id
AND o.cust_id = c.cust_id

AND EXISTS (SELECT order_id, prod_id
            FROM order_cancel
            WHERE order_id = oi.order_id
            AND prod_id = oi.prod_id)
ORDER BY c.cust_id, o.order_date, p.prod_id;

📍 EXISTS, Correlatede subquery 활용 풀이 방법

주문 취소 여부는 메인 쿼리에서 사용되지 않고, 그 여부만 확인하면 됨
=> 따로 조인하기보다 EXISTS를 사용하여 존재 여부만 확인하는 것이 바람직

EXISTS 비교값에 주문 취소 내역을 반환하는 서브쿼리 생성

해당 비교값 안의 order_id, prod_id가 메인 쿼리의 oi.order_id, oi.prod_id와 동일한 지 확인(취소된 주문인지 확인)
=> 서브쿼리에서 메인 쿼리의 값을 가져와 비교하므로 correlated subquery

ADSQL 5. 상품 테이블(prods)과 주문 상세 테이블(order_items), 주문 테이블(orders), 고객 테이블(custs)을 이용하 여 미국에서 거주하는 고객이 주문한 상품의 번호와 이름, 주문 수량, 주문 단가, 주문 금액을 조회하세요⭐⭐

가) 검색 컬럼
주문번호, 상품번호, 상품이름, 주문수량, 주문단가, 주문금액 (quantity * unit_price)
나) 조건
① 미국에 거주중인 고객이 주문한 정보를 검색
다) 정렬
① 주문금액 컬럼을 내림차순으로 정렬

-- 처음에 푼 방법
SELECT o.order_id, p.prod_id,
    i.quantity, i.unit_price,
    (i.quantity * i.unit_price) AS amt
FROM orders o, prods p, order_items i
WHERE o.cust_id IN (SELECT cust_id 
		    FROM custs 
		    WHERE country = 'USA')
AND o.order_id = i.order_id
AND i.prod_id = p.prod_id
ORDER BY amt DESC;

-- EXISTS, Correlatede subquery를 활용해서 개선한 방법
SELECT i.order_id, p.prod_id, p.prod_name,
    i.quantity, i.unit_price,
    (i.quantity * i.unit_price) AS amt
FROM prods p, order_items i
WHERE p.prod_id = i.prod_id
AND EXISTS (SELECT *
            FROM custs c, orders o
            WHERE c.cust_id = o.cust_id
            AND o.order_id = i.order_id
            AND country = 'USA')
ORDER BY amt DESC;

📍 EXISTS, Correlatede subquery 활용 풀이 방법

고객 주소지가 미국인지 아닌지는 메인 쿼리에서 사용되지 않고, 그 여부만 확인하면 됨
=> 따로 조인하기보다 EXISTS를 사용하여 존재 여부만 확인하는 것이 바람직

고객-주문내역-주문상품을 연결하면서 고객정보에서 거주지가 USA인 결과를 출력하는 서브쿼리 생성

서브쿼리 안에서 메인 쿼리의 i.order_id을 가지고와 값 비교 => correlated subquery

해당 값이 존재하는지 EXISTS로 확인

그 외 SELECT문에서 필요한 prods, order_items 테이블 조인

지수

사부작 사부작

이전 포스트

[SQL] WHERE절과 정렬, 단일 행 함수 사용

다음 포스트