쿼리를 사용하는 동안만 사용하는 임시 테이블
- 기본 형식: 이어서 2개 이상의 cte를 사용하려면 with없이 , 만 추가해서 정의해주면 된다.
WITH cte_name AS ( -- 서브쿼리 내용 SELECT column1, column2 FROM some_table WHERE condition ) SELECT * FROM cte_name
Q. 서브쿼리와 CTE의 차이점:
CTE는 메인 쿼리 "전"에 따로 정의 하고, "재사용" 가능
서브쿼리는 메인 쿼리 "안"에서 실행되며, "한번만" 실행 가능
예시) 학교에서 가장 높은 성적의 학생을 찾으려 할때 : 첫번째로 학생들의 성적을 확인, 두번째로 가장 높은 학생 확인 하는 논리구조 성립
SELECT name
FROM students ----- 1: 학생 테이블 확인
WHERE score = (SELECT MAX(score) FROM students) ------2: 학생 테이블에서 점수가 가장 높은 학생의 점수를 가져오는 조건절
: 서브 쿼리의 결과가 딱 한 줄 (1개의 값)만 나오는 경우
예시) 직원들의 평균 급여 1개의 값만 가져오는 where 절 단일 행 서브쿼리
select name
from employees
where salary > (select avg(salary) from employees)
: 서브쿼리의 결과가 여러줄로 나올 때 사용, 이 경우 in, any,all과 같은 연산자를 사용하여 여러값을 비교
예시) 뉴욕에 위치하고 있는 여러 부서들의 값을 가져오는 where 절 다중행 서브쿼리
select name
from employees
where department_id in (select department_id from departments where location ='NY')
: 서브쿼리가 메인쿼리에서 값을 받아서 처리하는 경우로 서브쿼리가 메인쿼리의 각 행에 대해 개별적으로 실행
예시) 부서별 평균 급여를 구하고, 평균 급여보다 많이 받는 사원들의 이름을 불러오기
* 숫자는 실행 순서를 뜻함
select name ---4
from employees e ---2
where salary --3 > (select avg(salary) from employees where department_id = e.department_id) ----1
[직원 테이블 ]
[서브쿼리 평균 급여 계산 값]
department_id | 평균 급여 |
---|---|
10 | 60,000 |
20 | 65,000 |
[결과 값]
charlie, Eve
+) 차선택
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;