서브 쿼리
📌 사용되는 곳
1. WHERE, HAVING절
2. FROM절
📌 사용하는 이유?
원하는 결과를 하나의 쿼리로 얻기 어려운 경우가 있다.
사원의 평균 급여보다 많이 받는 직원의 목록을 알고 싶은 경우, 1) 우선 사원의 평균 급여를 알아내기 위한 쿼리를 하고,
2) 이를 이용해 평균 급여보다 많이 받는 직원을 알아내기 위해 쿼리를 해야한다.
cf. 서브 쿼리와 조인
사용 목적을 보면 알 수 있지만, 어떤 데이터를 얻어내는데 있어서 조인과 서브 쿼리가 모두 가능한 경우가 많다. 성능 면에서는 조인이 더 좋다고 하는데, 자세한 내용은 옵티마이저와 실행 계획에 대해 다룰 때 살펴볼 예정이다.
📌 서브 쿼리문 작성 순서
1. 서브 쿼리문 먼저 작성 -> 다중 row, 다중 column을 처리해야할 경우, 서브 쿼리의 결과를 먼저 알아야 메인 쿼리를 짤 수 있다.
2. 메인 쿼리문 작성
SELECT last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)1. WHERE 절의 서브쿼리문 (중첩서브쿼리, nested subqueries)
--일반화학 수업을 듣는 관우의 학점
SELECT grade FROM student s, course c, score r, scgrade g
WHERE s.sno = r.sno
AND c.cno = r.cno
AND cname = '일반화학'
AND sname = '관우'
AND result BETWEEN loscore AND hiscore
--해당 학점보다 낮은 학생들의 이름
SELECT s.sno, s.sname, grade
FROM student s, course c, score r, scgrade g2
WHERE s.sno = r.sno
AND c.cno = r.cno
AND cname = '일반화학'
AND result BETWEEN loscore AND hiscore
AND grade > (SELECT grade FROM student s, course c, score r, scgrade g
WHERE s.sno = r.sno
AND c.cno = r.cno
AND cname = '일반화학'
AND sname = '관우'
AND result BETWEEN loscore AND hiscore)select *
from student
where avr <= (select avg(avr)
from student
where major = '화학'
and syear = 1)
and major = '화학'
and syear = 1;주의
메인 쿼리문에서 major, syear와 같은 조건 빠뜨리지 않고 다시 넣기
2. Having 절의 서브쿼리문
서브 쿼리문은 where 절에 select 문이 다시 들어가는 형태를 말하는데,
만약 이 select 문에서 얻은 결과를 또 다시 그룹 함수와 비교해줘야하는 경우,
서브 쿼리문은 having 절에 수반된다.
그 예시는 아래와 같다.
- 핵화학 과목보다 기말고사 평균 성적이 낮은 과목에 대한 정보
주의select c.cno, c.cname, p.pname, avg(sc.result) as "평균 성적" from course c, professor p, score sc where c.pno = p.pno and c.cno = sc.cno group by c.cno, c.cname, p.pname having avg(sc.result) > (select avg(s.result) from score s, course c where s.cno = c.cno and c.cname = '핵화학' group by c.cno) --이렇게 JOIN을 해서 구해도 되고 그냥 바로 SELECT FROM COURSE해줘도 됨 order by avg(sc.result);
그룹 함수를 제외한 모든 컬럼을 group by의 기준 컬럼으로 넣어줘야한다.
3. 다중 column, 다중 row가 결과로 나오는 서브 쿼리문
1) 다중 row
서브 쿼리문의 결과로 다중 row가 나오는 경우,
서브 쿼리문을 having, where 절에 써줄 때 해당 결과와 비교 대상 간에 =를 사용해줄 수 없다.
결과들 중 하나가 비교 대상과 같다는 의미에서 IN을 사용해줘야한다.
--01번 부서 사람들의 comm과 같은 모든 사원의 정보
select *
from emp
where comm in (select comm
from emp
where dno = '01');2) 다중 column
서브 쿼리문의 결과가 다중 row이기는 한데, 다중 row 중 값이 같은 하나를 찾는 것만으로는 충분하지 않은 경우가 있다.
이럴 경우에는, 서브 쿼리의 결과로 다중 row, 다중 column을 함께 넘겨줘야한다.
--각 직업 그룹에서 max salary를 받는 사원들의 목록
select employee_id, last_name, salary, job_id
from employees
where (salary, job_id) in (select max(salary), job_id from employees
group by job_id);위 예시의 경우 job_id 컬럼을 넘겨주지 않으면, 직업 그룹 별 max salary와 일치하는 모든 사원의 목록이 결과로 나오게 된다.
3) 다중행 함수 ALL과 ANY, EXISTS
ALL
: 검색된 값 중에 조건에 모두 일치하는 것만 참
컬럼 > max
컬럼 > ALL(서브 쿼리)
컬럼 < min
컬럼 < ALL(서브 쿼리)ANY
: 검색된 값 중에 조건에 맞는 것이 하나 이상 있으면 참
컬럼 > min
컬럼 > ANY(서브쿼리) : 가장 작은 값보다는 크다.(= 어떤 한 값보다는 크다)
컬럼 < max
컬럼 < ANY(서브쿼리) : 가장 큰 값보다는 작다.(= 어떤 한 값보다는 작다)- 예제
--30번 부서의 최대 급여자보다 급여가 높은 사원의 목록
select *
from employees
where salary > all(select salary
from employees
where department_id = 30);
--30번 부서의 최대 급여자보다 급여가 적은 사원의 목록
select *
from employees
where salary < any(select salary
from employees
where department_id = 30);EXISTS
: 서브쿼리의 결과를 만족하는 값의 존재 여부를 확인하는 조건. 조건을 만족하는 건이 여러 건이더 라도 1건만 찾으면 더 이상 검색하지 않는다.
4. FROM 절의 서브쿼리
원래 테이블에서 일부를 뽑거나 가공한 임시 테이블(실행 중에 임시로 생성된 동적인 뷰)을 만들고, 그 테이블에 대해 where 조건절 등을 통해 데이터를 뽑아오는 형식의 서브쿼리이다.
- 예시
SELECT t.team_name, p.player_name, p.back_no
FROM (SELECT team_id, player_name, back_no
FROM player
WHERE positon = 'MF') p, team t
WHERE p.team_id = t.team_id
OREDER BY 2;--입사 순으로 5명
SELECT ROWNUM, alias.*
FROM (SELECT employee_id, last_name, hire_date
FROM employees
ORDER BY hire_date
)alias
WHERE ROWNUM <= 5;cf. ROWNUM과 TOP
: 테이블이나 집합에서 원하는 만큼의 행을 가져오고 싶을 때 사용한다.
- 🚨 ROWNUM을 사용할 때 주의할 점
ROWNUM을 이용해서 ROWNUM 5~8과 같이 중간의 데이터를 바로 뽑아오는 것은 불가능하기 때문에 그럴 경우 아래와 같이 사용해주어야 한다.
SELECT * FROM(
SELECT ROWNUM as ROW_NUM, temp.* FROM
(SELECT * FROM board
ORDER BY seq DESC
)temp
)
WHERE ROW_NUM BETWEEN 5 AND 8;5. SELECT 절의 서브쿼리 (스칼라 서브 쿼리)
SELECT player_name, height,
(SELECT AVG(height) FROM player L
WHERE p.team_id = l.team_id)
FROM player p
ORDER BY 1;