[SQL] 서브쿼리(Subqueries)

Hyunjun Kim·2024년 9월 30일

SQL

목록 보기

5/44

서브쿼리

서브쿼리란?

서브쿼리는 SQL 쿼리 내에 포함된 또 다른 쿼리를 의미한다. 메인 쿼리(외부 쿼리) 내에서 데이터를 필터링하거나 계산하기 위해 사용된다. 서브쿼리는 SELECT, INSERT, UPDATE, DELETE 등 다양한 SQL 문 안에서 사용될 수 있다.

서브쿼리의 장점:

복잡한 로직 처리: 단일 쿼리로 처리하기 어려운 복잡한 데이터 조회를 단순화할 수 있다.
재사용성: 특정 조건을 여러 번 사용할 때 중복을 줄일 수 있다.
가독성 향상: 논리적 단계를 분리하여 쿼리의 가독성을 높일 수 있다.

서브쿼리 종류

서브쿼리의 가장 일반적인 분류는 비상관 서브쿼리(Non-correlated Subquery)와 상관 서브쿼리(Correlated Subquery)로 분류 가능.

비상관 서브쿼리 (Non-correlated Subquery)

특징: 서브쿼리가 메인 쿼리와 독립적으로 실행된다. 즉, 서브쿼리는 메인 쿼리의 각 행과 관계없이 한 번만 실행됩니다.
사용 예: 특정 조건에 맞는 값을 미리 계산하여 메인 쿼리에서 사용하는 경우.

employees 테이블

employee_id	name	department_id	salary
1	Alice	10	70000
2	Bob	20	80000
3	Charlie	10	75000
4	David	30	60000
5	Eva	20	90000

목표: 평균 급여보다 높은 급여를 받는 직원들을 조회.

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

서브쿼리 (SELECT AVG(salary) FROM employees)는 모든 직원의 평균 급여를 계산하고
메인 쿼리는 이 평균 급여보다 높은 급여를 받는 직원들을 조회

상관 서브쿼리 (Correlated Subquery)

특징: 서브쿼리가 메인 쿼리의 각 행에 의존적으로 실행된다. 서브쿼리는 메인 쿼리의 현재 행을 참조한다.
사용 예: 메인 쿼리의 각 행에 대해 조건을 다르게 적용해야 하는 경우.

예제

목표: 각 부서에서 최고 급여를 받는 직원들을 조회.

SELECT e1.name, e1.salary, e1.department_id
FROM employees e1
WHERE e1.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);
메인 쿼리의 각 직원 e1에 대해, 서브쿼리는 같은 부서(department_id)에서의 최고 급여를 찾는다.
메인 쿼리는 각 부서에서 최고 급여를 받는 직원들을 조회.

서브쿼리의 사용 위치

SELECT 절 내: 특정 열의 값을 계산하거나 변환할 때.
WHERE 절 내: 조건을 추가할 때.
FROM 절 내: 서브쿼리를 테이블처럼 사용하여 조인할 때.
HAVING 절 내: 그룹화된 데이터에 조건을 추가할 때.

SELECT 내 서브 쿼리

목표: 각 직원의 부서 내 평균 급여를 함께 조회.

SELECT 
    name, 
    salary, 
    (SELECT AVG(salary) FROM employees e2 
    WHERE e2.department_id = e1.department_id) 
    AS avg_department_salary
FROM employees e1;

직원의 부서(department_id) 내 평균 급여를 서브쿼리를 통해 계산하고 함께 조회

FROM 내 서브 쿼리

부서별 직원 수와 평균 급여를 조회.

SELECT 
    sub.department_id, 
    sub.num_employees, 
    sub.avg_salary
FROM 
    (SELECT 
         department_id, 
         COUNT(*) AS num_employees, 
         AVG(salary) AS avg_salary
     FROM employees
     GROUP BY department_id) AS sub
WHERE sub.num_employees > 2;

서브쿼리 sub는 각 부서의 직원 수와 평균 급여를 계산
메인 쿼리는 직원 수가 2명 이상인 부서만 조회

서브쿼리 사용 예제

예제 1

sales 테이블에서 전체 판매액의 평균을 초과하는 판매 기록을 조회.

sales 테이블:

sale_id	product_id	amount
1	101	500
2	102	1500
3	101	700
4	103	2000
5	102	1200

SELECT sale_id, product_id, amount
FROM sales
WHERE amount > (SELECT AVG(amount) FROM sales);

결과:

sale_id	product_id	amount
2	102	1500
4	103	2000
5	102	1200

예제 2

상관 서브쿼리를 이용한 부서별 최고 급여 직원 조회

employees 테이블:

employee_id	name	department_id	salary
1	Alice	10	70000
2	Bob	20	80000
3	Charlie	10	75000
4	David	30	60000
5	Eva	20	90000

SELECT e1.name, e1.salary, e1.department_id
FROM employees e1
WHERE e1.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

결과:

name	salary	department_id
Charlie	75000	10
Eva	90000	20
David	60000	30

연습 문제

문제 1

목표: 각 부서의 최소 급여 이상을 받는 직원들의 이름과 급여를 조회하세요.

SELECT e1.name, e1.salary, e1.department_id
FROM employees e1
WHERE e1.salary >= (
    SELECT MIN(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

서브쿼리 (SELECT MIN(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id)는 메인 쿼리의 각 부서별 최소 급여를 계산
메인 쿼리는 이 최소 급여 이상을 받는 직원들을 조회

문제 2

목표: sales 테이블에서 가장 많이 판매된 product_id를 조회하세요.

SELECT product_id
FROM sales
GROUP BY product_id
HAVING COUNT(*) = (
    SELECT MAX(sale_count)
    FROM (
        SELECT product_id, COUNT(*) AS sale_count
        FROM sales
        GROUP BY product_id
    ) AS sub
);

내부 서브쿼리 (SELECT product_id, COUNT(*) AS sale_count FROM sales GROUP BY product_id)는 각 제품의 판매 수를 계산
또 다른 서브쿼리 (SELECT MAX(sale_count) FROM (...))는 가장 많이 판매된 수 찾기
메인 쿼리는 판매 수가 최대 판매 수와 같은 product_id를 조회

LIMIT을 사용하여 간단히 해결할 수 있지만, 이 예제는 다양한 데이터베이스에 적용 가능하도록 작성되었습니다

문제 3

목표: employees 테이블에서 각 부서별로 최고 급여를 받는 직원의 이름과 급여를 조회

SELECT e1.name, e1.salary, e1.department_id
FROM employees e1
WHERE e1.salary = (
    SELECT MAX(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

서브쿼리 (SELECT MAX(e2.salary) FROM employees e2 WHERE e2.department_id = e1.department_id)는 메인 쿼리의 각 부서에서 최고 급여를 계산
메인 쿼리는 이 최고 급여를 받는 직원들을 조회

문제 4

목표: employees 테이블에서 모든 부서에 속한 직원 중, 각 부서에서 상위 50%의 급여를 받는 직원의 이름과 급여를 조회

이 문제는 SQL에서 순위를 매기는 함수(ROW_NUMBER, RANK, DENSE_RANK)를 사용해야 효율적으로 해결할 수 있습니다. 하지만 순위 함수를 사용하지 않고 서브쿼리를 활용하여 해결하는 방법을 소개하겠습니다.

SELECT e1.name, e1.salary, e1.department_id
FROM employees e1
WHERE e1.salary >= (
    SELECT PERCENTILE_CONT(0.5) 
    WITHIN GROUP (ORDER BY e2.salary) 
    FROM employees e2
    WHERE e2.department_id = e1.department_id
);

PERCENTILE_CONT(0.5)는 각 부서의 급여 중앙값을 계산
메인 쿼리는 각 부서에서 중앙값 이상을 받는 직원들을 조회

문제 5

목표: orders 테이블에서 각 고객의 총 주문 금액이 해당 고객의 평균 주문 금액보다 높은 고객을 조회

orders 테이블:

order_id	customer_id	order_amount
1	101	250
2	102	150
3	101	300
4	103	400
5	102	200

SELECT customer_id, SUM(order_amount) AS total_amount
FROM orders
GROUP BY customer_id
HAVING SUM(order_amount) > (
    SELECT AVG(customer_total)
    FROM (
        SELECT SUM(order_amount) AS customer_total
        FROM orders
        GROUP BY customer_id
    ) AS customer_totals
);

서브쿼리 customer_totals는 각 고객의 총 주문 금액을 계산
또 다른 서브쿼리 (SELECT AVG(customer_total) FROM (...))는 모든 고객의 평균 총 주문 금액을 계산
메인 쿼리는 각 고객의 총 주문 금액이 이 평균을 초과하는 고객들을 조회

» = cmd + shift + \

Hyunjun Kim

이전 포스트

[SQL] UNION & JOIN

다음 포스트