오라클 서브쿼리

서브쿼리(subQuery)

 - select구문 내부에 또 다른 select 구문이 있는 것 (ex 페이징처리)

단일행 서브쿼리

- select 문장으로 부터 하나의 행만 검색하는 것을 단일행 서브쿼리라고 합니다.

예제) emp테이블에서 SCOTT이 받는 급여보다 많이 받는 사원의 사번, 이름, 업무, 급여순으로 출력

*서브쿼리에 익숙치 않은 분들은 바로 출력을 하는 것보다 하나하나 나눠서 출력하는 게 좋습니다.

1. SCOTT이 받는 급여를 먼저 출력

2. 서브쿼리를 이용해서 급여를 3000보다 많이 받는 사원의 사번, 이름, 업무, 급여 출력

예제) emp테이블에서 입사일이 가장 오래된 사원의 이름과 입사일을 출력

1.가장 오래된 입사일을 먼저 출력

2. 서브쿼리를 이용해서 입사일이 80/12/17인 사원의 이름과 입사일을 출력

 -> 서브쿼리 내에서 그룹함수도 사용 가능합니다.(아래참조)

예제) emp테이블에서 사원의 급여가 20번 부서의 최소급여보다 많은 부서와 최소급여 출력

1. 20번 부서의 최소급여를 먼저 출력

2. 서브쿼리를 이용해서 최소급여가 800보다 많은 부서와 그 부서의 최소급여 출력

 -> 서브쿼리에서 having절 사용가능

참고 : 오라클 그룹함수, group by, having

 

다중행 서브쿼리

- 하나 이상의 행을 반환하는 서브쿼리입니다. 

- 복수행 연산자를 사용 할 수 있습니다.

 

in연산자

예제) emp테이블에서 각 그룹의 최소급여를 받는 사원의 사원번호, 사원이름, 업무, 부서번호를 출력

1. 각 그룹당 최소급여를 출력

2. 그룹당 최소급여를 받는 사원의 사원번호, 이름, 업무, 부서번호 출력

 -> ' = ' 연산자가 아닌 in을 사용

'=' 연산자는 단일행 서브쿼리에서만 사용이 가능합니다. 위에처럼 아몰랑 난 '=' 연산자 쓸거야! 했을 경우

ORA-01427: 단일 행 하위 질의에 2개 이상의 행이 리턴되었습니다.

 -> 간절히 기도하면 온 우주가 도와줄거라 생각했는데 안되네요.... 그냥 in 쓰세요.

any 연산자

- 작은값보다 큰 값을 찾아서 내부적으로 정렬(리턴값 중에서 작은값보다 커야 한다는 규칙이 있음)

- 서브쿼리의 결과가 최대값을 내부적으로 구한 후 정렬해서 출력

무슨말인지 모르시겠죠? 저도 모르겠으니 바로 예제로 넘어갈게요.

예제) emp테이블에서 30번 부서의 최소급여를 받는 사원보다 많은 급여를 받는 사원의

사번, 이름, 업무, 부서번호를 출력(단, 30번 부서는 제외)

1. 20번 부서의 최소급여를 출력

2. 최소급여가 800보다 많이 받는 사원의 사번, 이름, 업무, 부서번호를 출력(20번부서 제외)

3. 2번을 any 연산자를 사용하여 출력

 -> 2번과 3번의 차이점이 보이시나요? 2번에서 사용하였던 그룹함수 min(sal)을 3번에서는 사용하지 않았어요.

any 연산자가 20번 부서의 sal에서 가장 작은값을 찾아서 반환해주었습니다. => 가장 작은 수를 구해줌

위의 예제를 이용해서 반대로 max(sal) 을 출력해볼게요.

-> max(sal) 을 사용하지 않아도 any연산자가 알아서 sal의 최대값을 구해서 반환해줍니다. => 가장 큰 수를 구해줌

서브쿼리 응용문제 -> 페이징처리

예제) emp, dept테이블에서 업무가 MANAGER인 사원의 이름, 부서명, 근무지를 조회하는 SQL 

1. 업무가 MANAGER인 사원의 정보 출력

2. inner join을 이용하여 부서명(dname) 출력

3. 2번을 서브쿼리로

 -> 만약 emp 테이블에 많은 양의 데이터(큰 데이터)가 있다면 불러오는 시간이 오래 걸리기 때문에

속도 향상을 위해 바로 조건에 맞는 데이터만 불러오게 합니다.

DML(insert, update, delete)도 서브쿼리 사용 가능

 

update

형식)

update 수정할테이블명
set (수정할필드명1,수정할필드명2) = (서브쿼리)
where 조건식

예제) emp테이블에서 SCOTT의 업무와 급여가 일치하도록 JONES의 업무와 급여를 수정시키는 SQL작성

=> JONES -> SCOTT와 같은 부서로 이동

1. SCOTT의 업무와 급여를 출력

2. JONES의 정보를 수정

delete (잘 안씀)

형식)

delete from 테이블명         
where 조건식 = ( 서브쿼리)