- 주요 포인트
ㄴ 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과 같은 연산자를 사용하여 여러값을 비교
: 여러개의 값이 반환 될 수 있으므로 in, any, all 같은 다중값 비교 연산자 사용
예시1) 뉴욕에 위치하고 있는 여러 부서들의 값을 가져오는 where 절 다중행 서브쿼리 (in 비교 연산자)
select name
from employees
where department_id in (select department_id from departments where location ='NY')- 서브쿼리의 select절이 뉴욕에 있는 모든 부서들의 id들을 반혼 (여러개)
- 메인쿼리는 그 값을 받아 뉴욕에 있는 부서에 속한 직원의 이름들을 반환
예시2) 어떤 학생의 점수가 다른 모든 학생의 점수보다 높은지 확인하고 싶을때 (all 비교 연산자)
: A 반에 속하는 모든 학생들의 점수보다 높은 점수를 가진 학생을 찾기
select name
from student
where score > all (select score from student where class ='A')예시3) 어떤 학생의 점수가 다른 모든 학생의 점수보다 높은지 확인하고 싶을때 (any 비교 연산자)
: A 반에 속하는 학생들의 점수 중 하나라도 점수를 초과하는지 알아보고 싶을때
SELECT name
FROM student
WHERE score > ANY (SELECT score FROM student WHERE class = 'A')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;
