서브쿼리란?
- 일반적인 쿼리(메인, 주 쿼리) 안에 있는 또 다른 쿼리
-> 보조, 하위 쿼리 - 서브쿼리는 하나의 SELECT 문장으로, 괄호로 둘러싸인 형태
- 메인 쿼리 기준으로 여러 개의 서브 쿼리 사용 가능
서브쿼리 종류
- 서브쿼리 위치에 따라
1) Select 절에 위치 : 스칼라 서브쿼리
2) From 절에 위치 : 인라인 뷰
3) Where 절에 위치 : 중첩 서브쿼리
1. 스칼라 서브쿼리 - select 절에 위치
- 메인쿼리의 select 절에 위치
- select 절에 위치하여 마치 하나의 컬럼이나 표현식 처럼 사용
- 서브쿼리에 Alias(별칭)을 주는 것이 일반적
-> 하나의 컬럼역할을 하므로 - 서브쿼리가 최종적으로 반환하는 로우 수와 컬럼(표현식) 수는 1개
- 서브쿼리 내에서 메인쿼리와 조인하는 것이 일반적(조인을 안하면 여러 건이 조회될 가능성이 많음)
- 조인과 비교해서 스칼라 서브쿼리는 성능상 좋지 않음
- 따라서 과도한 사용은 자제
- 다른 테이블에 있는 값을 가져올 때 사용하는 방법으로 조인과 스칼라 서브쿼리가 있음
- Examples
-- example #1
# 부서명 가져오기
SELECT a.employee_id
, a.first_name || ' ' || a.last_name
, a.department_id
,(SELECT b.department_name # 컬럼 1개만 가져올 수 있음 or 문자열 연결 연산자로 결합되어 최종 반환값 1개가 되게 한다.
FROM departments b
WHERE a.department_id = b.department_id) as dept_name
FROM employees a
ORDER BY 1 ; 
해석 : 178번 사원 -> 조인에서는 누락되었지만 서브쿼리에서는 조회됨
-- example #2
# 부서명, 나라이름 가져오기
SELECT a.employee_id
, a.first_name || ' ' || a.last_name
, a.department_id
,(SELECT b.department_name
FROM departments b
WHERE a.department_id = b.department_id) as dept_name -- 부서명
,(SELECT d.country_name
FROM departments b
, locations c
, countries d
WHERE a.department_id = b.department_id
AND b.location_id = c.location_id
AND c.country_id = d.country_id) as country_name -- 나라이름
FROM employees a
ORDER BY 1 ;2. 인라인 뷰 -From 절에 위치 ⭐️
- 메인쿼리의 from 절에 위치
- 마치 하나의 테이블처럼 동작
- 서브쿼리가 최종 반환하는 로우와 컬럼, 표현식 수는 1개 이상 가능
- 서브쿼리에 대한 별칭(Alias)은 반드시 명시
- 메인쿼리와 조인조건은 메인 쿼리의 WHERE(join ~ on) 절에서 처리가 일반적
인라인 뷰가 필요한 이유
- 기존 단일 테이블만 읽어서는 필요한 정보를 가져오기가 어려울 때
예, 특정 조건으로 집계한 결과와 조인 시 필요 - 인라인 뷰의 쿼리가 여러 테이블을 조인해 읽어오는 경우가 많음
- 복잡한 쿼리의 경우, 쿼리 작성을 좀 더 직관적으로 사용하기 위해
Examples
# 1. 부서별 평균 급여를 서브쿼리에서 구한 뒤
# 2. 사원급여와 부서 평균 급여를 같이 조회
SELECT a.department_id
, a.employee_id
, a.first_name || ' ' || a.last_name as emp_name
, a.salary
, b.avg_salary
FROM employees a
JOIN (SELECT department_id
, avg(salary) as avg_salary)
FROM employees
GROUP BY department_id) b
ON a.department_id = b.department_id
ORDER BY 1;3. 중첩쿼리 - Where절에 위치
- 메인쿼리의 WHERE 절에 위치
- 서브쿼리가 조건절의 일부로 사용됨
- 서브쿼리 최종 반환 값과 메인쿼리 테이블의 특정 컬럼 값을 비교 시 사용
- 서브쿼리가 최종 반환하는 로우와 컬럼, 표현식 수는 1개 이상 가능
- 조건절의 일부이므로 서브쿼리에 대한 별칭(alias) 사용 불가
- 서브쿼리 내에서 메인쿼리와 조인 가능
Examples
-- example #1
# 1. employees 테이블에 있는 department_id 조회
# 2. departments 테이블에서 이 서브쿼리에서 반환하는 값이 포함되는 건만 조회
SELECT *
FROM departments
WHERE department_id in (SELECT departmnet_id FROM employees);-- example #2
# 1. job_id, salary 두 값을 동시에 비교
# 2. job_id별 최소 급여를 받는 사원이 조회됨
SELECT employee_id
, first_name || ' ' || last_name as emp_name
, job_id
, salary
WHERE (job_id,salary) IN (SELECT job_id, min_salary FROM jobs)
ORDER BY 1;-- example #3
# 1. employees 테이블에서 자신이 속한 부서의 평균 급여보다 많이 받는 사원 조회
SELECT employee_id
, department_id
, first_name || ' ' || last_name as emp_name
, salary
FROM employees a
WHERE salary > (SELECT AGV(salary) as avg_salary
FROM employees b
WHERE a.department_id = b.department_id) -- 조인에 의해 group by 효과가 나타남4. With절
- 서브쿼리의 일종 (인라인 서브쿼리)
- 하나의 서브쿼리를 또 다른 서브쿼리에서 참조하여 재사용 가능한 구문
- 가독성이 좋음
- WITH 절은 내부적으로 TEMP 테이블 스페이스를 사용함
-> TEMP 테이블스페이스에 각 서브쿼리 결과를 담아두고 있음 - 과도한 WITH 절 사용 시, TEMP 테이블스페이스 공간을 점유해 성능에 좋지 않음
- 일반적인 경우에는 서브쿼리를 사용하고, 서브쿼리 사용이 수월치 않은 경우 WITH 절 사용
with절 구문
-- with 별칭 as 다음에 서브쿼리 형태
WITH -- with는 한번만 명시, 서브쿼리는 여러 개 사용 가능
temp1 as (
SELECT a.department_id, a.department_name
, b.location_id, b.street_address, b.city, b.country_id
FROM departments a
JOIN locations b
on a.location_id = b.location_id
),
temp2 as (
SELECT b.department_id, b.department_name, b.street_address, b.city
, a.country_name
FROM countries a
JOIN temp1 b -- 서브쿼리 내에서 다른 서브쿼리 참조 가능
on a.country_id = b.country_id
)
-- 메인 쿼리
SELECT a.employee_id
, a.first_name || ' ' || a.last_name as emp_name
, b.department_name
, b.street_address
, b.country_name
FROM employees a
JOIN temp2 b -- 메인 쿼리에서는 여러개의 서브쿼리 조인해 결과 조회 가능
on a.department_id = b.department_id
ORDER BY 1;
