04_subqueries.sql

  1-- =============================================================================
  2-- PostgreSQL 서브쿼리와 CTE 예제
  3-- Subqueries and Common Table Expressions (CTE)
  4-- =============================================================================
  5
  6-- 먼저 이전 예제 파일들을 실행하여 테이블과 데이터를 생성하세요.
  7
  8-- =============================================================================
  9-- 1. 스칼라 서브쿼리 (단일 값 반환)
 10-- =============================================================================
 11
 12-- SELECT 절에서 사용
 13SELECT
 14    first_name,
 15    last_name,
 16    salary,
 17    (SELECT AVG(salary) FROM employees) AS company_avg,
 18    salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
 19FROM employees;
 20
 21-- WHERE 절에서 사용
 22SELECT first_name, last_name, salary
 23FROM employees
 24WHERE salary > (SELECT AVG(salary) FROM employees);
 25
 26-- 부서별 평균보다 높은 급여를 받는 직원
 27SELECT e.first_name, e.last_name, e.salary, d.dept_name
 28FROM employees e
 29JOIN departments d ON e.dept_id = d.dept_id
 30WHERE e.salary > (
 31    SELECT AVG(salary)
 32    FROM employees
 33    WHERE dept_id = e.dept_id
 34);
 35
 36-- =============================================================================
 37-- 2. 인라인 뷰 (FROM 절 서브쿼리)
 38-- =============================================================================
 39
 40-- 부서별 통계를 서브쿼리로 구한 후 조인
 41SELECT
 42    d.dept_name,
 43    ds.employee_count,
 44    ds.avg_salary,
 45    ds.total_salary
 46FROM departments d
 47JOIN (
 48    SELECT
 49        dept_id,
 50        COUNT(*) AS employee_count,
 51        ROUND(AVG(salary), 2) AS avg_salary,
 52        SUM(salary) AS total_salary
 53    FROM employees
 54    WHERE dept_id IS NOT NULL
 55    GROUP BY dept_id
 56) ds ON d.dept_id = ds.dept_id;
 57
 58-- 급여 순위와 함께 조회
 59SELECT *
 60FROM (
 61    SELECT
 62        first_name,
 63        last_name,
 64        salary,
 65        RANK() OVER (ORDER BY salary DESC) AS salary_rank
 66    FROM employees
 67) ranked
 68WHERE salary_rank <= 5;
 69
 70-- =============================================================================
 71-- 3. EXISTS / NOT EXISTS
 72-- =============================================================================
 73
 74-- 프로젝트에 참여 중인 직원
 75SELECT e.first_name, e.last_name
 76FROM employees e
 77WHERE EXISTS (
 78    SELECT 1
 79    FROM employee_projects ep
 80    WHERE ep.emp_id = e.emp_id
 81);
 82
 83-- 프로젝트에 참여하지 않는 직원
 84SELECT e.first_name, e.last_name
 85FROM employees e
 86WHERE NOT EXISTS (
 87    SELECT 1
 88    FROM employee_projects ep
 89    WHERE ep.emp_id = e.emp_id
 90);
 91
 92-- 직원이 있는 부서
 93SELECT d.dept_name
 94FROM departments d
 95WHERE EXISTS (
 96    SELECT 1
 97    FROM employees e
 98    WHERE e.dept_id = d.dept_id
 99);
100
101-- =============================================================================
102-- 4. IN / NOT IN
103-- =============================================================================
104
105-- Engineering 부서 직원
106SELECT first_name, last_name
107FROM employees
108WHERE dept_id IN (
109    SELECT dept_id
110    FROM departments
111    WHERE dept_name = 'Engineering'
112);
113
114-- 프로젝트에 참여하지 않는 직원 (NOT IN 주의: NULL 처리)
115SELECT first_name, last_name
116FROM employees
117WHERE emp_id NOT IN (
118    SELECT emp_id FROM employee_projects
119);
120
121-- =============================================================================
122-- 5. ANY / ALL
123-- =============================================================================
124
125-- Engineering 부서의 어떤 직원보다 급여가 높은 직원
126SELECT first_name, last_name, salary
127FROM employees
128WHERE salary > ANY (
129    SELECT salary
130    FROM employees e
131    JOIN departments d ON e.dept_id = d.dept_id
132    WHERE d.dept_name = 'Engineering'
133);
134
135-- Engineering 부서의 모든 직원보다 급여가 높은 직원
136SELECT first_name, last_name, salary
137FROM employees
138WHERE salary > ALL (
139    SELECT salary
140    FROM employees e
141    JOIN departments d ON e.dept_id = d.dept_id
142    WHERE d.dept_name = 'Engineering'
143);
144
145-- =============================================================================
146-- 6. 상관 서브쿼리 (Correlated Subquery)
147-- =============================================================================
148
149-- 각 직원의 부서 평균과 비교
150SELECT
151    e.first_name,
152    e.last_name,
153    e.salary,
154    (
155        SELECT ROUND(AVG(e2.salary), 2)
156        FROM employees e2
157        WHERE e2.dept_id = e.dept_id
158    ) AS dept_avg_salary
159FROM employees e
160WHERE e.dept_id IS NOT NULL;
161
162-- 부서 내에서 가장 높은 급여를 받는 직원
163SELECT e.first_name, e.last_name, e.salary, d.dept_name
164FROM employees e
165JOIN departments d ON e.dept_id = d.dept_id
166WHERE e.salary = (
167    SELECT MAX(e2.salary)
168    FROM employees e2
169    WHERE e2.dept_id = e.dept_id
170);
171
172-- =============================================================================
173-- 7. CTE (Common Table Expression) - WITH 절
174-- =============================================================================
175
176-- 기본 CTE
177WITH dept_stats AS (
178    SELECT
179        dept_id,
180        COUNT(*) AS employee_count,
181        ROUND(AVG(salary), 2) AS avg_salary,
182        SUM(salary) AS total_salary
183    FROM employees
184    WHERE dept_id IS NOT NULL
185    GROUP BY dept_id
186)
187SELECT
188    d.dept_name,
189    ds.employee_count,
190    ds.avg_salary,
191    ds.total_salary
192FROM departments d
193JOIN dept_stats ds ON d.dept_id = ds.dept_id
194ORDER BY ds.total_salary DESC;
195
196-- 여러 CTE 사용
197WITH
198high_earners AS (
199    SELECT emp_id, first_name, last_name, salary
200    FROM employees
201    WHERE salary > 50000
202),
203dept_names AS (
204    SELECT e.emp_id, d.dept_name
205    FROM employees e
206    JOIN departments d ON e.dept_id = d.dept_id
207)
208SELECT
209    h.first_name,
210    h.last_name,
211    h.salary,
212    dn.dept_name
213FROM high_earners h
214LEFT JOIN dept_names dn ON h.emp_id = dn.emp_id;
215
216-- CTE를 여러 번 참조
217WITH emp_summary AS (
218    SELECT
219        dept_id,
220        COUNT(*) AS emp_count,
221        AVG(salary) AS avg_salary
222    FROM employees
223    GROUP BY dept_id
224)
225SELECT
226    'Total Departments' AS metric,
227    COUNT(*) AS value
228FROM emp_summary
229UNION ALL
230SELECT
231    'Avg Employees per Dept',
232    ROUND(AVG(emp_count), 2)
233FROM emp_summary
234UNION ALL
235SELECT
236    'Overall Avg Salary',
237    ROUND(AVG(avg_salary), 2)
238FROM emp_summary;
239
240-- =============================================================================
241-- 8. 재귀 CTE (Recursive CTE)
242-- =============================================================================
243
244-- 숫자 시퀀스 생성
245WITH RECURSIVE numbers AS (
246    -- Base case
247    SELECT 1 AS n
248    UNION ALL
249    -- Recursive case
250    SELECT n + 1
251    FROM numbers
252    WHERE n < 10
253)
254SELECT n FROM numbers;
255
256-- 조직도 (직원-관리자 계층)
257WITH RECURSIVE org_chart AS (
258    -- Base case: 최상위 관리자 (manager_id가 NULL)
259    SELECT
260        emp_id,
261        first_name || ' ' || last_name AS name,
262        manager_id,
263        1 AS level,
264        ARRAY[emp_id] AS path
265    FROM employees
266    WHERE manager_id IS NULL
267
268    UNION ALL
269
270    -- Recursive case: 부하 직원
271    SELECT
272        e.emp_id,
273        e.first_name || ' ' || e.last_name,
274        e.manager_id,
275        oc.level + 1,
276        oc.path || e.emp_id
277    FROM employees e
278    JOIN org_chart oc ON e.manager_id = oc.emp_id
279)
280SELECT
281    REPEAT('  ', level - 1) || name AS employee_hierarchy,
282    level
283FROM org_chart
284ORDER BY path;
285
286-- 날짜 시리즈 생성
287WITH RECURSIVE date_series AS (
288    SELECT DATE '2024-01-01' AS date
289    UNION ALL
290    SELECT date + INTERVAL '1 day'
291    FROM date_series
292    WHERE date < DATE '2024-01-10'
293)
294SELECT date FROM date_series;
295
296-- =============================================================================
297-- 9. LATERAL JOIN (상관 서브쿼리의 대안)
298-- =============================================================================
299
300-- 각 부서의 상위 2명
301SELECT d.dept_name, top_employees.*
302FROM departments d
303CROSS JOIN LATERAL (
304    SELECT first_name, last_name, salary
305    FROM employees e
306    WHERE e.dept_id = d.dept_id
307    ORDER BY salary DESC
308    LIMIT 2
309) AS top_employees;
310
311-- =============================================================================
312-- 10. 서브쿼리 vs CTE vs LATERAL 비교
313-- =============================================================================
314
315-- 방법 1: 서브쿼리 (인라인 뷰)
316SELECT *
317FROM (
318    SELECT dept_id, AVG(salary) AS avg_sal
319    FROM employees
320    GROUP BY dept_id
321) sub
322WHERE avg_sal > 50000;
323
324-- 방법 2: CTE (더 읽기 쉬움)
325WITH dept_avg AS (
326    SELECT dept_id, AVG(salary) AS avg_sal
327    FROM employees
328    GROUP BY dept_id
329)
330SELECT * FROM dept_avg WHERE avg_sal > 50000;
331
332-- 방법 3: LATERAL (행별로 서브쿼리 필요할 때)
333SELECT d.dept_name, stats.avg_salary
334FROM departments d
335CROSS JOIN LATERAL (
336    SELECT ROUND(AVG(salary), 2) AS avg_salary
337    FROM employees e
338    WHERE e.dept_id = d.dept_id
339) stats
340WHERE stats.avg_salary > 50000;
341
342-- =============================================================================
343-- 서브쿼리와 CTE 요약
344-- =============================================================================
345/*
346서브쿼리 위치:
347- SELECT: 스칼라 서브쿼리 (단일 값)
348- FROM: 인라인 뷰 (테이블처럼 사용)
349- WHERE: 조건에서 사용
350
351서브쿼리 연산자:
352- =, <, >: 스칼라 비교
353- IN, NOT IN: 목록 포함 여부
354- EXISTS, NOT EXISTS: 존재 여부
355- ANY, ALL: 조건 비교
356
357CTE 장점:
358- 가독성 향상
359- 재사용 가능
360- 재귀 쿼리 지원
361- 실행 계획 최적화 힌트 (MATERIALIZED)
362
363LATERAL:
364- 행별로 상관 서브쿼리 실행
365- TOP-N 문제에 유용
366*/