• 주요 포인트
  ㄴ CTE의 정의와 필요성 인지하기
  ㄴ 서브 쿼리와의 차이점 및 서브쿼리 의 종류에 대해 알기
  ㄴ 어떤 상황에 어떤 서브쿼리를 써야 효율적인지 인지하기
  ㄴ 내 데이터에 적용시켜 보기

---

1. CTE란?

쿼리를 사용하는 동안만 사용하는 임시 테이블

• 기본 형식: 이어서 2개 이상의 cte를 사용하려면 with없이 , 만 추가해서 정의해주면 된다.

WITH cte_name AS (
    -- 서브쿼리 내용
    SELECT column1, column2
    FROM some_table
    WHERE condition
)
SELECT *
FROM cte_name

• CTE 필요성
  1) 논리적인 흐름 파악 가능
  2) 한번만 정의 하면 여러번 사용 가능
  

---

2. 서브쿼리

Q. 서브쿼리와 CTE의 차이점:
CTE는 메인 쿼리 "전"에 따로 정의 하고, "재사용" 가능
서브쿼리는 메인 쿼리 "안"에서 실행되며, "한번만" 실행 가능

• 서브쿼리가 있는 경우 쿼리 실행 순서 : 서브쿼리 부터 실행 > 메인 쿼리의 from 절 > where 절 > select 절
• 서브쿼리 필요성
  1) 어떤 데이터를 찾기 위해 먼저 다른 데이터를 찾아야 할때 (데이터를 두번 찾을 필요가 있을때)

예시) 학교에서 가장 높은 성적의 학생을 찾으려 할때 : 첫번째로 학생들의 성적을 확인, 두번째로 가장 높은 학생 확인 하는 논리구조 성립

SELECT name
FROM students ----- 1: 학생 테이블 확인
WHERE score = (SELECT MAX(score) FROM students) ------2: 학생 테이블에서 점수가 가장 높은 학생의 점수를 가져오는 조건절 

2.1 서브쿼리 종류 및 예시

1) 단일 행 서브쿼리

: 서브 쿼리의 결과가 딱 한 줄 (1개의 값)만 나오는 경우

예시) 직원들의 평균 급여 1개의 값만 가져오는 where 절 단일 행 서브쿼리

select name
from employees
where salary > (select avg(salary) from employees)

• 서브쿼리의 select 절이 전체 직원들의 평균 급여를 계산
• 메인 쿼리는 그 값을 받아 평균 이상의 급여를 받는 직원의 이름을 반환

2) 다중 행 서브 쿼리

: 서브쿼리의 결과가 여러줄로 나올 때 사용, 이 경우 in, any,all과 같은 연산자를 사용하여 여러값을 비교

예시) 뉴욕에 위치하고 있는 여러 부서들의 값을 가져오는 where 절 다중행 서브쿼리

select name
from employees
where department_id in (select department_id from departments where location ='NY')

• 서브쿼리의 select절이 뉴욕에 있는 모든 부서들의 id들을 반혼 (여러개)
• 메인쿼리는 그 값을 받아 뉴욕에 있는 부서에 속한 직원의 이름들을 반환

3) 상관 서브쿼리

: 서브쿼리가 메인쿼리에서 값을 받아서 처리하는 경우로 서브쿼리가 메인쿼리의 각 행에 대해 개별적으로 실행

예시) 부서별 평균 급여를 구하고, 평균 급여보다 많이 받는 사원들의 이름을 불러오기

* 숫자는 실행 순서를 뜻함

select name ---4
from employees e ---2
where salary --3 > (select avg(salary) from employees where department_id = e.department_id) ----1

• 부서별 평균 급여를 계산하여 메인쿼리의 각 행에 대해 개별적으로 실행
  • 1)서브쿼리에서 계산 해 둔 값: 부서별 평균 급여를 구하고 메인쿼리의 부서(각 직원의 부서 아이디)와 서브쿼리의 부서별 id(부서별 평균 급여)를 엮음 → 즉, 각 row별 해당 직원이 속한 부서의 id와 그 부서의 id에 맞는 평균 급여를 비교한다
  • 2) 직원 테이블을 조회
  • 3) 각 행 마다 부서별 평균 급여를 엮고, 그 값이 평균 급여보다 큰지를 확인
  • 4) 평균 급여가 큰 직원들의 이름만 출력

[직원 테이블 ]

[서브쿼리 평균 급여 계산 값]

department_id	평균 급여
10	60,000
20	65,000

[결과 값]
charlie, Eve

---

+) 차선택

• 윈도우 함수를 활용
  ㄴ 생각의 흐름을 볼 수 있어 직관적이지만, cte를 써야 한다는 점으로 인해 쿼리 길이가 생각보다 길어 진다는 단점이 있다. 하지만, 그만큼 직관적이기 때문에 이해하기 쉽다.

WITH EmployeeWithAvg AS (
    SELECT name, salary, department_id,
           AVG(salary) OVER (PARTITION BY department_id) AS avg_salary
    FROM employees
)
SELECT name
FROM EmployeeWithAvg
WHERE salary > avg_salary;

https://chatgpt.com/c/67086781-e5a8-8003-b8bb-b5a74f7d9a64