잠깐 짚고 가기: 나는 문제를 보면 서브쿼리로 풀 생각을 못할까

[SQL] 복잡한 쿼리를 다루는 방법 — 서브쿼리와 CTE

[SQL] 복잡한 쿼리를 다루는 방법 — 서브쿼리와 CTE

과 내용 합침

 

문제를 보다보면 서브쿼리를 사용해아할 문제들이 꽤있다.

하지만 풀다보면 잘 안되기도 하고, 머리가 멈춰서 시간이 오래걸릴때가 많다.

 

나는 지금 서브쿼리에 대해 무엇을 잘못 생각하고 있는걸까?

개념을 다시 짚고 넘어가자

---

서브쿼리란...

쿼리 안에서 실행되는 또 다른 쿼리라고 정의할수 있다.

 

⭐중요 포인트⭐

대부분의 상황에선 서브쿼리가 먼저 작동한다.

SELECT
	컬럼1,
	(SELECT 집계함수(컬럼)
	FROM 테이블
	WHERE 조건) AS 기준값
FROM 테이블;

SELECT 컬럼1
FROM 테이블;
WHERE 연산자 (SELECT 집계함수(컬럼)
		FROM 테이블
		WHERE 조건) AS 기준값

와 비슷한 형태를 지닌다.

또

사용되는 위치에 따라서 역할이 달라지기도 한다.

SELECT 절 : 기준값을 출력에 같이 보여줄 때 WHERE 절  : 조건 비교를 위한 값이나 목록이 필요할 때 FROM 절   : 중간 결과를 테이블처럼 사용할 때 EXISTS절: 존재 여부를 판정할때

---

SELECT 절에서 사용될 경우...

각 행 옆에 기준이 되는 값(평균, 합계 등) 을 같이 보여주고 싶을 때 사용한다.

“기준값을 계산해서 같이 보여주고 싶을 때”

형태

SELECT
    컬럼1,
    (SELECT 집계함수(컬럼)
     FROM 테이블
     WHERE 조건) AS 기준값
FROM 테이블;

 

• 결과는 항상 한 값
• 각 행 옆에 비교 기준, 평균, 합계 등을 붙일 때

 

 

예시문제 1. 직원의 이름과 급여, 비교를 위해 평균 급여를 출력하라.

수도코드로 보기

전체 직원의 평균 급여를 구한다. 각 직원의 이름, 급여를 조사한다. 조회할 때 평균 급여를 같이 보여준다.

 

SELECT
    name,
    salary,
    (SELECT AVG(salary) FROM employees) AS avg_salary
FROM employees;

 

실행 순서 (Line by Line)

1. 서브쿼리 실행  → 평균 급여 1개 값 생성 2. employees 테이블 한 행씩 출력 3. 각 행마다 평균급여 값을 그대로 붙임

---

 

WHERE 절에서 사용될 경우...

조건에 쓸 기준값이 필요할 때, 조건을 비교해야 할 때 사용한다.

# 값이 1개일 때
WHERE 컬럼 = (SELECT ...)

# 값이 여러개일 때
WHERE 컬럼 IN (SELECT ...)

단일값 예시문제 1. 급여가 평균보다 큰 사람을 출력하라

수도코드로 보기

전체 직원의 평균 급여를 계산한다. 그 결과를 하나의 테이블로 구성한다. 평균급여보다 급여가 많은 사람을 구한다.

 

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

 

실행 순서 (Line by Line)

1. 서브쿼리 실행  → 평균 급여 1개 값 생성 2. employees 테이블 첫 번째 행 조회 3. salary > 평균 급여 비교 4. 조건이 참이면 출력, 거짓이면 제외 5. 모든 행에 대해 반복

 

여러 값 예시문제 2. 서울 소재 부서 소속 직원 조회 문제

수도코드로 보기

서울에 있는 부서의 id 목록을 구한다 그 부서에 속한 직원만 조회한다

 

SELECT *
FROM employees
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE location = '서울'
);

 

실행 순서 (Line by Line)

1. 서브쿼리 실행 → 서울에 위치한 부서의 department_id 목록 생성 2. employees 테이블에서 행 하나씩 조회 3. department_id가 목록 안에 있는지 확인 4. 있으면 출력, 없으면 제외

 

---

FROM 절에서 사용될 경우...

중간 결과를 테이블처럼 사용하고 싶을 때 사용한다.

SELECT 컬럼 1, 컬럼2 
FROM ( 
	SELECT 컬럼 1, 컬럼 2
    	FROM 테이블
        GROUP BY 묶을 컬럼) AS 서브쿼리명;

예시문제 1. 부서별 평균 급여 기준 필터링 문제

 

수도코드로 보기

부서별 평균 급여를 계산한다 이 결과를 하나의 임시 테이블로 만든다 평균 급여가 3000 이상인 부서만 조회한다

 

SELECT department, avg_salary
FROM (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
) t
WHERE avg_salary >= 3000;

 

실행 순서 (Line by Line)

1. FROM 절 안의 서브쿼리 실행→ 부서별 평균 급여 테이블 생성 2. 생성된 임시 테이블을 t로 이름 지정 3. t 테이블에 대해 WHERE 조건 적용 4. 조건을 만족하는 행 출력

---

EXISTS 서브쿼리문

WHERE EXISTS ( #not exisits는 없는것을 판별
    SELECT 1
    FROM 테이블
    WHERE 조건
)

 

예시문제 1. 주문 이력이 있는 고객 조회 문제

수도코드로 보기

이 고객에 대한 주문이 존재하는지 확인한다 주문이 하나라도 있으면 고객을 출력한다

 

SELECT *
FROM customers c
WHERE EXISTS (
    SELECT 1
    FROM orders o
    WHERE o.customer_id = c.id
);

 

실행 순서 (Line by Line)

customers 테이블의 각 행을 하나씩 확인 해당 customer_id로 orders에 행이 있는지 검사 하나라도 있으면 TRUE → 고객 출력

---

내용정리

SELECT 절 : 기준값을 출력에 같이 보여줄 때
WHERE 절  : 조건 비교를 위한 값이나 목록이 필요할 때
FROM 절   : 중간 결과를 테이블처럼 사용할 때

EXISTS절: 존재 여부를 판정할때

 

 

---

상관 서브쿼리 (Correlated Subquery)

지금까지 봤던 서브쿼리는 대부분 이러했다.

• 서브쿼리 혼자 실행 가능

• 한 번 실행 → 결과를 바깥 쿼리가 사용

허나, 상관 서브쿼리는 다르다.

 

상관 서브쿼리는 바깥 쿼리의 한 행마다 서브쿼리가 다시 실행된다. 

 

그렇다면 언제 사용하나?

“각 행마다 기준이 달라질 때”

 

• 각 부서에서 평균보다 급여가 높은 직원
• 각 고객의 주문 평균 금액보다 큰 주문
• 각 상품의 카테고리 평균보다 비싼 상품

예제 문제: 각 부서에서 자기 부서 평균 급여보다 많이 받는 직원 조회

수도코드로 보기

직원 한 명을 선택한다 이 직원이 속한 부서의 평균 급여를 계산한다 직원 급여가 그 평균보다 큰지 비교한다 크면 출력한다 다음 직원으로 이동한다

 

SELECT name, salary, department
FROM employees e
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department = e.department
);

 

실행 순서 (Line by Line)

1. employees 테이블에서 첫 번째 직원 e 선택 2. 서브쿼리 실행 → 이 직원의 부서 평균 급여 계산 3. e.salary > 부서 평균 급여 비교 4. 조건이 참이면 출력 5. 다음 직원으로 이동하여 반복

 

---

CTE (WITH 절)

 

CTE는 서브쿼리와 목적은 같으며 차이점은...

서브쿼리를 위에서 이름 붙여서 미리 정의한다. 때문에 읽기 쉽고, 재사용도 가능하다.

그렇다면 언제 사용하나?

 

• FROM 절 서브쿼리가 길어질 때
• 같은 서브쿼리를 여러 번 써야 할 때
• 실행 순서를 명확히 보여주고 싶을 때

예제 문제: 부서별 평균 급여를 구한 뒤 평균 급여가 3000 이상인 부서 조회

수도코드로 보기

부서별 평균 급여 테이블을 만든다 이 테이블에 이름을 붙인다 그 결과에서 평균 급여가 3000 이상인 부서를 조회한다

 

WITH dept_avg AS (
    SELECT department, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department
)
SELECT department, avg_salary
FROM dept_avg
WHERE avg_salary >= 3000;

 

실행 순서 (Line by Line)

1. WITH 절 실행 → 부서별 평균 급여 테이블 생성 → dept_avg라는 이름으로 저장 2. 메인 SELECT 실행 FROM dept_avg 3. WHERE 조건 적용 4. 결과 출력

 

 

---