6. ORACLE 정리 :: 서브쿼리(SUBQUERY) - 단일행, 다중행, 다중열, 상관, 스칼라

 

0. 서브쿼리(SUBQUERY)란?

주 SELECT문 안에 포함된 또 다른 보조 SELECT 문

• 위치는 원하는 결과에 따라서 다르게 사용 가능
  • 주 select문의 컬럼위치, from절, where절에 사용가능
    • select문의 컬럼위치 → 단일행, 스칼라 서브쿼리, 상관 서브쿼리
    • SELECT 컬럼명, (SELECT 컬럼명 FROM 테이블)
    • from절(인라인뷰, 스토어드뷰) → 다중행, 다중행다중열 서브쿼리 가상 테이블
      • inline view: 1회용
      • stored view: 저장해놓고 사용함
    • FROM (SELECT 컬럼명 …… FROM 테이블)
    • where절 → 단일행, 다중행, 다중열 서브쿼리, 상관 서브쿼리
    • WHERE 컬럼명 비교연산(SELECT 컬럼명 FROM 테이블명)
  • insert, update, create문에도 사용이 가능하다
• 메인 쿼리 실행 전 한번만 실행되며 반드시 괄호()로 묶어야함
• 서브쿼리와 비교하려면 반드시 서브쿼리 항목 개수와 자료형이 일치해야함

하지만 서브쿼리는 성능이 썩 좋지는 않아서 조인문이 더 좋은 퍼포먼스를 보여준다.
동일 결과를 보여준다면 조인문을 사용하는 것이 더 좋다.

 

1. 유형

결과(RESULTSET)에 따라 유형을 나눌 수 있다.

유형select결과 값 개수

	유형	select 갯수	결과 값 개수
1	단일행 서브쿼리	1	1
2	다중행 서브쿼리	1	1+
3	다중열 서브쿼리	1+	1
4	다중행 서브쿼리	1+	1+
5	상(호연)관 서브쿼리		where exists와 함께 사용하여 true인 경우만큼 출력
6	스칼라 서브쿼리		1

(1) 단일행 서브쿼리

서브쿼리 조회 결과 값의 개수가 1개인 서브쿼리

--부서가 총무부인 사원 조회
-- join
SELECT *
FROM EMPLOYEE E **JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID**
WHERE DEPT_TITLE = '총무부';
-- 같은 결과 -> 하지만 서브쿼리는 성능이 썩 좋지는 않아서 조인문이 더 좋은 퍼포먼스를 보여준다.
SELECT *
FROM EMPLOYEE
WHERE DEPT_CODE = **(SELECT DEPT_ID FROM DEPARTMENT WHERE DEPT_TITLE = '총무부')**;

-- 직책인 과장인 사원 조회 : 서브쿼리
SELECT *
FROM EMPLOYEE
WHERE JOB_CODE = **(SELECT JOB_CODE FROM JOB WHERE JOB_NAME = '과장')**;

(2) 다중행 서브쿼리

서브쿼리의 조회 결과 값의 행이 여러 개인 서브쿼리

• 대입 연산 불가 → IN (서브쿼리) 사용

-- 직책이 과장, 부장인 사원 조회
-- 이게 서브로 들어간다(2개 행 출력)
SELECT JOB_CODE FROM JOB WHERE JOB_NAME IN ('과장', '부장'); 
-- 다중행 서브쿼리
SELECT *
FROM EMPLOYEE
--where job_code = (select job_code from job where job_name in ('과장', '부장')); -- 1개 행에 2개 행을 대입하려고 하니 에러 발생
WHERE JOB_CODE IN (SELECT JOB_CODE FROM JOB WHERE JOB_NAME IN ('과장', '부장')); -- in을 써줘야한다.

• 비교 연산 단독 사용 불가 → 비교연산 + ANY(), ALL() 함께 이용 서브 쿼리 결과가 6, 10, 12일 때ANY(서브쿼리)ALL(서브쿼리)

  	ANY(서브쿼리)	ALL(서브쿼리)
  >(=)	다중행 서브쿼리문 결과값 중 하나라도 크면 참	다중행 서브쿼리 결과 중 모든 값보다 크면 참
  	7 → 참 / 13 → 참	7 → 거짓 / 13 → 참
  <(=)	다중행 서브쿼리문 결과값 중 하나라도 작으면 참	다중행 서브쿼리 결과 중 모든 값보다 작으면 참
  	7 → 참 / 13 → 거짓	7 → 거짓 / 13 → 거짓

  -- D5, D6사원 급여보다 많이 받는 사원 조회 
  SELECT *
  FROM EMPLOYEE
  --where salary > (select salary from employee where dept_code in ('D5', 'D6')); -- 2개 이상의 행 리턴 에러 발생
  where salary > any(select salary from employee where dept_code in ('D5', 'D6')); -- 버스쿼리 결과 중 최소값보다 하나라도 크면 참
  -- 같은 결과
  WHERE SALARY > (SELECT MIN(SALARY) FROM EMPLOYEE WHERE DEPT_CODE IN ('D5', 'D6'));
  

(3) 다중열 서브쿼리

서브쿼리의 결과가 1개행, 다수 컬럼을 갖는 것

-- 기술지원부면서 급여 200만원 이상이 사원의 이름, 부서코드, 급여 출력(다중열 서브쿼리로)
SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
WHERE SALARY = 2000000 AND DEPT_TITLE = '기술지원부';

SELECT EMP_NAME, DEPT_CODE, SALARY
FROM EMPLOYEE JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
WHERE (DEPT_CODE, SALARY) = (SELECT DEPT_CODE, SALARY FROM EMPLOYEE 
                             WHERE DEPT_CODE = (SELECT DEPT_ID FROM DEPARTMENT WHERE DEPT_TITLE = '기술지원부')
                                    AND SALARY = 2000000);

(4) 다중열 다중행 서브쿼리

서브쿼리의 결과가 다수행, 다수 컬럼을 갖는 것

from 절에 많이 사용함

SELECT *
FROM (SELECT * FROM EMPLOYEE 
            JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID 
            JOIN JOB USING(JOB_CODE));
--select e.*, (select emp_id from employee where dept_code = 'D5') -- 다중행 서브쿼리는 select문에 사용이 불가하다
SELECT E.*, (SELECT EMP_ID FROM EMPLOYEE WHERE EMP_ID = '200') -- 단일행 서브쿼리처럼 하나의 행만 셀렉트문에 사용 가능함
FROM EMPLOYEE E;

(5) 상(호연)관 서브쿼리 🚨

java의 중첩 반복문과 비슷하다.
상관 서브쿼리는 내부 for문인 셈이다. 단독 운영될 수 없다.

 

서브쿼리의 select문을 작성할 때, 메인 쿼리 값을 가져와서 사용하는 구문

즉, 메인 쿼리 테이블의 값이 변경되면 서브쿼리 결과값도 바뀐다.

• from 절에는 잘 안쓴다.
• 본인값이 from에 있는 테이블로부터 계속 바뀌는 경우 상관서브쿼리를 사용한다.
  • 예) 실제로 상품에 달려있는 댓글개수, 게시글에 달려있는 첨부파일 개수 등을 조회할 때 상관서브쿼리가 사용된다.

-- 본인(메인쿼리의 emp_name)이 속한 부서의 사원수 출력
select emp_name, dept_code, (select count(*) from employee where dept_code = e.dept_code) as 사원수
from employee e;

@ 실행 순서
: 먼저 e를 가져오고 서브 쿼리문을 먼저 실행 -> 
 (e.dept_code가 바뀌면서 32번, 즉 employee테이블 row수만큼 실행됨) ->
 다음 dept_code와 새로운 e.dept_code 체크가 실행된다 ->
 최종 셀렉트문의 결과까지 합쳐서 출력

-- 본인이 속한 부서의 사원수가 3명 이상인 사원만 조회
select * 
from employee e
where (select count(*) from employee where dept_code = e.dept_code) >= 4;

-- 자신이 속한 직급의 평균 급여보다 많은 급여를 받는 사원의 이름, 직책명, 급여 조회
select emp_name, job_name, salary
from employee e join job j on e.job_code = j.job_code
where salary > (select avg(salary) from employee 
									where job_code = e.job_code); 
-- 상관커리문은 job코드가 같은 사람의 평균 급여를 돌려주는 셈이다.

-- 같은 결과
select emp_name, job_name, salary
from employee e join job using(job_code) -- 별칭은 using과 함께 못씀
where salary > (select avg(salary) from employee 
                where job_code = e.job_code
                group by job_code);
                --group으로 묶어도 결국 1개 결과밖에 안나와서 > 사용이 가능

-- (비교) 모든 직무의 평균 월급보다 높은 사람의 이름, 직무, 월급 조회                
select emp_name, job_name, salary
from employee e join job j on e.job_code = j.job_code
where salary > (select avg(salary) from employee); 

(6) 스칼라 서브쿼리

상관커리이면서 단일행(결과 값의 갯수가 1개인) 서브쿼리

2. Exists(서브쿼리) 예약어

• 서브쿼리 결과가 1개 이상이라도 나오면 true, 0개면 false를 반환해줌
• where절을 함께 서서 해당 조건이 있는지 없는지 체크해주는 기능으로 활용할 수 있다.

-- 매니저인 사원만 조회하기
select * -- exists 서브쿼리가 true값인 row의 *들이 출력됨
from employee e
where exists (select 1 from employee where manager_id = e.emp_id);  -- 1은 아무값 입력한거임
-- e.emp_id가 java 중첩 반복문의 i, manager_id가 j인 셈
-- where은 해당 테이블의 각 row에 조건이 맞는지 체크해주는 역할을 수행한다.
-- 서브쿼리 안에도 where가 있기 때문에 메인쿼리보다 먼저 수행되면서 
--서브쿼리의 manager_id와 일치하는 서브쿼리의 emp_id를 체크하고
-- exists로 인해 true일 경우 1을 반환하고 1인 경우 메인쿼리는 조건이 충족되어 *을 출력한다.

-- 최고 급여를 받는 사원 조회하기 
select *
from employee e
where not exists (select * from employee where e.salary < salary); 
    --where salary = (select max(salary) from employee); -> (그냥 max 쓰면 더 쉽긴 하다)
--e.salary에 메인 쿼리의 월급값이 하나씩 돌아가면서 해당 과 서브 쿼리 테이블 내 23개 row 월급값과 비교 진행
-- 메인쿼리 내 23개 월급값을 모두 검증한다. **즉, 서브 쿼리는 (23 * 23)회 돌아가는 셈**
--작으면 1이 반환되므로 최종적으로 최고값인 애만 0을 반환한다.
--하지만 not이 붙었으므로 True가 되어서 최고값만 출력됨

3. From 절에 서브쿼리 사용할 때

resultset을 테이블처럼 이용할 수 있게 해주는 기능

• 서브쿼리를 기준으로 컬럼 생성함
  → 서브 쿼리문에서 선정한 select 컬럼만 사용할 수 있으니 주의
• 통계쿼리, 자주사용하는 조인문, 집합연산결과, 가살컬럼이 추가된 select문 사용시 활용할 수 있다. (물론 stored를 많이 쓰지만, 계속 쓰지 않아도 되고 가독성도 좋아짐)
• 해당 컬럼을 메인 쿼리에서 where 조건절로 필터할 수도 있음.

1) INLINE VIEW

해당 구문에서만 쓰는 일회성 칼럼 테이블 생성

-- 연봉 포함한 사원의 사원명, 부서, 직책명, 자신이 속한 부서별 평균 급여 조회(소수점버림, 원화) 출력
-- from절의 가상컬럼을 별칭으로 하나의 table처럼 사용
select emp_name, to_char(dept_avg, 'L999,999,999') as dept_avg
from(
    select emp_name, dept_title, job_name, salary + (salary * nvl(bonus, 0)) * 12 as year_sal, 
            (select floor(avg(salary)) from employee where e.dept_code = dept_code) as dept_avg
    from employee e left join department d on dept_code = dept_id
                join job j on e. job_code = j.job_code) v
where dept_avg >= 3000000;

• 계층처럼 여러개를 한번에 작성할수도 있다.
  • 효율적이진 않지만 그냥 다중 inline view 작성 구조에 대한 예시 살펴보기

select a.*
from (select t.*, (select avg(salary) from employee where job_code = t.job_code) avg_job
    from (select salary * 12 as 연봉, e.*, d.*, (select avg(salary) from employee where dept_code = e.dept_code) as avg_dept
	        from employee e join department d on dept_code = dept_id
	                        join job j on e.job_code = j.job_code
        ) t
) a;

2) 순위 부여하기

order by 정렬은 출력에 대한 순서만 오라클에서 변경해주는 것일 뿐 순위 등 번호가 row에 매칭된 것은 아니다.

따라서, 데이터에 순번을 부여해주는 아래 2개 방법을 활용해야한다.

(1) ROWNUM

오라클이 기본적으로 제공하는 가상 컬럼

select 구문에 적어주거나 where 조건절에 원하는 순위만큼 지정해주면 된다.

• 단, 여러 쿼리가 있을 경우 별칭을 꼭 써줘야함

select문 실행하면서 순서가 매칭된다.

• from → where → select → order by

@ JOIN과 활용
-- 급여 많이 받는 사원 3명 조회하기: 사원명 부서명 직책명 급여
SELECT ROWNUM, EMP_NAME, DEPT_TITLE, JOB_NAME, SALARY
FROM EMPLOYEE E LEFT JOIN DEPARTMENT D ON E.DEPT_CODE = D.DEPT_ID
                JOIN JOB J ON E.JOB_CODE = J.JOB_CODE
WHERE ROWNUM <= 3;

-- 인라인뷰 서브 쿼리문과 활용
select a.*, rownum 순위
from (select emp_id, emp_name, dept_title, job_name, hire_date, (salary + (salary * nvl(bonus,0))) av
        from employee e join job using(job_code) 
                        left join department on dept_code = dept_id
        where rownum < 6 -- 클래스에는 안써도 되네?
        order by av desc) a;

⇒ 중간 순서부터 순위를 매기려면 인라인뷰를 꼭 활용해야 한다.

• select 와 함께 rank 번호가 부여되고, select한 컬럼들의 출력 전에 처음~해당번호까지만 출력을 하도록 선별을 할 수 있는데 중간 순위는 시작지점을 알 수 없어서 찾을 수없게 됨

-- 인라인 사용 안할 경우 칼럼명을 제외하고 아무 데이터도 나오지 않는다.
SELECT *
FROM (SELECT EMP_NAME, SALARY FROM EMPLOYEE ORDER BY SALARY)
WHERE ROWNUM BETWEEN 5 AND 10;
-- 해결방법: 인라인으로 넣어주면 이미 있는 컬럼의 순위에서 조건만 추리면 되니까 실행할 수 있게 됨
    -- 순위를 부여할 칼럼을 내부 쿼리문에 넣어주면 된다.
    -- 나중에 웹 페이징 처리할 때 페이지별로 데이터 연결 다르게 세팅하는 장면에서 사용하니까 매우 중요.

• 올바른 예시

@중간 순위 구하기
-- 10~15등 조회하기
SELECT ROWNUM, B.*
FROM (SELECT ROWNUM RNUM, A.*
      FROM (SELECT EMP_NAME, DEPT_TITLE, JOB_NAME, SALARY
                FROM EMPLOYEE E LEFT JOIN DEPARTMENT D ON E.DEPT_CODE = D.DEPT_ID
                            JOIN JOB J ON E.JOB_CODE = J.JOB_CODE
                            ORDER BY SALARY DESC
            )A
    ) B
WHERE RNUM BETWEEN 10 AND 15;

SELECT ROWNUM, A. *
FROM (
    SELECT ROWNUM AS RNUM, E. * -- 별칭을 부여해서 값 고정을 해주지 않으면 안나온다.
    FROM (SELECT EMP_NAME, SALARY FROM EMPLOYEE ORDER BY SALARY) E -- 실제로 순위를 얻고자하는 내용을 담은 쿼리문. 유일하게 rownum을 쓰지 않는다.
    ) A
WHERE RNUM BETWEEN 5 AND 10;

(2) RANK

• RANK() OVER(ORDER BY)
  • 동일 값이 나왔을 때 번호를 생략 → 누락번호 발생 가능
  • 공동 3위, 5위, 6위
• DENSE_RANK(ORDER BY) OVER
  • 동일 값이 나왔을 때 번호 생략 X
  • 공동 3위, 4위, 5위 …

-- 평균 급여를 많이 받는 부서 3개: 부서명, 평균급여 출력
select *
from(
    select a.*
    from (
         select rank() over (order by avg(salary) desc) 순위, dept_title 부서명, avg(salary) 평균급여
         from employee join department on dept_code = dept_id
         group by dept_title
        ) a
    ) b
where 순위 between 1 and 3;

 

4. WITH :: 미리 구문 만들어놓기

한개 쿼리문에서 지정된 쿼리문을 재사용하기 위해 사용함.

• 여러구문 사용시 재호출해서 쓸 때 사용하는 편
• 쿼리문 상단 with에 (최상단)쿼리 한개와 별칭을 저장함

@ 사용법
with test_with as (select emp_name, salary from employee order by salary desc),
     test_with2 as (select * from employee)
select emp_name, salary from test_with
union
select email, bonus
from test_with2;

 

5. 게시글과 댓글에 적용하기 (예제)

• 각 게시글에 몇개 댓글이 달려있는지 파악하기

SELECT * FROM
	(SELECT rownum AS rnum, b.*,
			(SELECT count(*) FROM board_comment
				where board_ref = b.board_no) AS commnet_count
		FROM
			(SELECT * FROM board ORDER BY board_date desc) b)
	WHERE rnum BETWEEN 1 AND 5;

• 특정 게시글 번호에서 댓글, 대댓글 순으로 정렬하여 조회하기

select * from 	(select * from board 
				left join BOARD_COMMENT on board_no = board_ref) 
				where board_no=33
				start with board_comment_level = 1 
				connect by prior board_comment_no = board_comment_ref;

• 게시글 테이블과 댓글 테이블의 단순 조인 및 순서정렬 조회

select * from (select rownum as rnum, b.* 
				from (select * from board 
						left join board_comment on board_no=board_ref 
						order by board_no desc) b) 
				where rnum between 1 and 10;

• realnum을 도입: 전체 댓글 중 같은 글에 달린 댓글은 같은 숫자를 갖게 됨

select * from (select rownum AS rnum, b.* 
				from (select * from 
					(SELECT rownum AS realnum, inb.* FROM
					(SELECT * FROM board ORDER BY board_date desc ) inb )
						left join board_comment on board_no=board_ref) b )
				where realnum between 1 and 10;

• join을 하지 않았기 때문에 select문으로 선택된 댓글수만 게시글 칼럼에 붙어서 조회되었다.

SELECT * FROM (SELECT rownum AS rnum, b.*, 
				(SELECT count(*) FROM board_comment where board_ref = b.board_no) AS comment_count 
				FROM (SELECT * FROM board ORDER BY board_date desc) b) 
				WHERE rnum BETWEEN 1 AND 10;