[Oracle]ORACLE SUB QUERY

목차

서브쿼리

서브쿼리는 하나의 메인쿼리안에 또 하나의 쿼리가 담겨있는 걸 의미

종류 : 

스칼라 서브쿼리(SELECT절에 사용), 인라인뷰 서브쿼리(FROM절에 사용), 일반 서브쿼리(WHERE절에 사용)

SELECT col1, (SELECT ...)     -- 스칼라 서브쿼리(Scalar Sub Query): 하나의 컬럼처럼 사용 (표현 용도)
FROM (SELECT ...)             -- 인라인 뷰(Inline View): 하나의 테이블처럼 사용 (테이블 대체 용도)
WHERE col = (SELECT ...)    -- 일반 서브쿼리: 하나의 변수(상수)처럼 사용 (서브쿼리의 결과에 따라 달라지는 조건절)
출처: https://data-make.tistory.com/25 [Data Makes Our Future]

 

oracle sub query

-- 스칼라 서브쿼리(사원테이블에서 사원명, 부서명, 부서테이블에서 부서명 조회)
SELECT e.ENAME, e.DEPTNO, (SELECT d.DNAME FROM DEPT d WHERE e.DEPTNO=d.DEPTNO) dname
FROM emp e
;

-- 인라인뷰 서브쿼리(부서별 최대연봉 받는 사람과 동일하게 받는 사람의 정보 조회)
SELECT e.ENAME, e.DEPTNO, e.sal
FROM emp e, (SELECT deptno,max(sal) max_sal FROM emp GROUP BY DEPTNO) i
WHERE e.SAL = i.max_sal
;

-- 일반 서브쿼리
-- smith보다 많이 받는 사람의 데이터를 조회, 일단 SMITH의 급여 조회결과 800이 나왔음
SELECT * FROM 
emp WHERE ENAME = 'SMITH'
;

-- smith보다 많이 받는 사람의 데이터를 조회
SELECT * FROM 
emp WHERE sal > 800
;

-- smith보다 많이 받는 사람의 데이터를 조회(서브쿼릐 이용) 
SELECT * FROM 
emp WHERE sal > (SELECT sal FROM emp WHERE ENAME='SMITH')
;

 

스칼라 서브쿼리

예를 들어 사원 테이블(emp)과 부서 테이블(dept)이 있을 경우

사원테이블에는 부서명 데이터는 없다.

조회하고 하는 값이 사원명, 부서번호, 부서명일 경우 

emp테이블에서는 ename, deptno는 가져올 수 있지만 부서명은 dept테이블에서 가져와야 한다.

이 경우 join을 사용할 수도 있지만 데이터가 많지 않은 경우는 아래처럼 스칼라 서브쿼리를 사용하기도 한다.

부서번호로 조인해서 부서명을 가져온 데이터를 ()안에 넣어서 하나의 컬럼처럼 조회

스칼라 서브쿼리

스칼라 서브쿼리 작동원리

메인 쿼리를 수행한 후 스칼라 서브쿼리에 필요한 값을 제공
스칼라 서브쿼리를 수행하기 위해 필요한 데이터가 들어 있는 블록을 메모리로 로딩
메인 쿼리에서 주어진 주건을 가지고 필요한 값을 찾고 이 결과를 입력값과 출력값으로 메모리 내의 query execution cache라는 곳에 저장 해 둠(여기서 입력값은 메인쿼리에서 주어진 값이고 출력값은 스칼라 서브쿼리를 수행 후 나온 결과값임)
다음 조건이 메인 쿼리에서 스칼라 서브 쿼리로 들어오면 해시 함수를 이용해서 해당 값이 캐시에 존재하는지 찾고 있으면 즉시 결괏값을 출력, 없으면 다시 블록을 엑세스 해서 해당 값을 찾은 후 다시 메모리에 캐시해 둠, 이 작업을 메인 쿼리가 끝날 때까지 반복
※ 위의 순서에서 알 수 있듯이 스칼라 서브 쿼리가 빠른 이유는 찾는 데이터가 메모리에 있는 값을 참조하기 때문임
만약 모든 데이터가 메모리에 없거나 또는 데이터양이 많을 경우에는 Join이 더 빠름
스칼라 서브 쿼리의 결과가 1개가 아닐 경우 "단일행 하위 질의에 2개 이상의 행이 리턴되었습니다." 에러 발생 함
스칼라 서브 쿼리에서 2개 이상의 컬럼을 조회할 경우에도 "ORA-00913 : 값의 수가 너무 많습니다." 에러 발생 함

출처 : 오라클 SQL과 PL/SQL 책

---

인라인뷰 서브쿼리

아래 서브쿼리는 부서별 최고급여 받는 사람과 연봉이 같은 사람을 조회하는 쿼리이고

부서별 최고급여를 받는 사람을 뽑을 때 인라인뷰 서브쿼리를 사용 했다.

인라인뷰 서브쿼리

 

---

일반 서브쿼리

'ALLEN'이라는 사원의 급여보다 높은 사람의 데이터를 조회하는 쿼리이고

여기서 서브쿼리를 사용한 이유는 'ALLEN'의 급여가 예를 들어 1800이라고 했을 때 아래처럼 사용할 경우

SELECT * FROM 
emp 
WHERE sal > 1800

만일 'ALLEN'의 급여가 2000으로 변경 될 경우 다시 조건을 변경해줘야 한다.

그래서 'ALLEN'의 급여를 변수처럼 활용한 것이다.

일반 서브쿼리

 

---

단일행 서브쿼리

단일행 서브쿼리

-- 단일행 서브쿼리일 경우 WHERE절에서 사용되는 연산자 
= : 같다.
<> : 같지 않다.
> : 크다.
< : 작다.
<= : 작거나 같다.

---

다중행 서브쿼리

다중행 서브쿼리

 

---

다중행 서브쿼리 함수

다중행 서브쿼리 함수

ANY와 ALL은 연산자의 방향에 따라 최댓값, 최솟값이 달라짐
서브쿼리에서 반환되는 값은 최솟값이거나 최댓값이지만 연산자 좌측에 어떤 값이 오는가에 따라서 출력되는 결괏값은 다르게 나옴

※ 서브 쿼리 작성 시 주의사항
서브쿼리 부분은 WHERE절의 연산자 오른쪽에 위치해야 하며 반드시 괄호로 묶어야 함
특별한 경우(Top-n 분석 등)를 제외하고는 서브 쿼리절에 Order by절이 올 수 없음
단일행 서브쿼리(일반적으로 사용되는 유형이고 서브 쿼리 수행결과가 1건만 나오는 경우)와 다중행 서브쿼리에 따라 연산자를 잘 선택해야 함

사진 출처 : https://mjn5027.tistory.com/51

---

다중 컬럼 서브쿼리

다중 컬럼 서브 쿼리는 서브 쿼리의 결과가 여러 컬럼인 경우를 말함
주로 pk를 여러 컬럼으로 합쳐서 만들었을 경우() 한꺼번에 비교하기 위해서 자주 사용

다중 컬럼 서브쿼리

위를 보면 서브 쿼리 부분에서 학년별로 최대 몸무게를 구한 다음 한행 씩 메인 쿼리로 넘겨줘서 메인쿼리를 수행한 후 그 조건에 맞는 행을 출력한 것임
여기서 중요한 점은 서브 쿼리에서 두 개의 컬럼을 동시에 메인 쿼리로 넘겨서 비교한 다는 것

---

 WITH절을 활용한 서브쿼리

오라클 9i 버전부터 지원됨, WITH절을 사용하여 원하는 테이블을 메모리에 미리 뷰처럼 가상의 테이블로 생성 후 데이터를 가져오는 기법
성능이 좋아서 현업에서 아주 많이 사용되지만 사용법이 어렵다는 단점이 있음
테이블을 임시로 만드는 점은 VIEW와 쓰임이 비슷하지만 VIEW는 DROP하기 전까지는 없어지지 않지만 WITH절은 한번 실행할 쿼리문안에서만 실행됨
오라클 공유 메모리에 임시 테이블을 생성하여 반복 재사용이 가능하도록 해줌
WITH절 사용 시 동일 테이블 접근을 최소화 하여 메모리에 생성된 임시 테이블에서 필요한 데이터를 메모리로 접근하기에 디스크 IO보다 성능 개선의 이점이 있음
그리고 SQL도 가독성 있게 바꿀 수 있음
※ with 절 안에는 SELECT 문장만 쓸 수 있음, WITH 절 안에 또 다른 WITH 절을 쓸 수 없음

기본 문법
- 단일 가상 테이블 생성
WITH a AS
(
  SELECT query..
)
SELECT * FROM a
;

- 다중 가상 테이블 생성
WITH a AS
(
  SELECT query..
),
b AS
(
  SELECT query..
) 
SELECT * FROM a
UNION ALL
SELECT * FROM b
;

with절 서브쿼리