[Oracle SQL] 서브쿼리 개념 정리

yoonheekim·2022년 11월 7일
0

Oracle SQL

목록 보기
14/15

📌서브쿼리란

  • 다른 select문에 삽입된 select문
  • 모든 절에서 사용가능
  • 서브쿼리는 비교를 위해 괄호로 묶어 사용하는 연산자 오른 쪽에 정의한다.
  • 서브쿼리가 먼저 실행되고 그 결과를 메인쿼리에서 사용한다.
  • 서브쿼리에서는 order by절을 사용하지 않는다 (top-N쿼리제외)
  • where절에서 쓸 수 없는 그룹함수를 서브쿼리로 사용할 수 있다.

서브쿼리의 종류는 메인쿼리와의 상호작용 방식에 따라 단일행 서브쿼리(single-row-subquery)와 다중행 서브쿼리()로 나뉜다. 그 외에도 다중열 서브쿼리, from절에서 사용하는 서브쿼리도 있다.

[서브쿼리종류]

1. 단일행서브쿼리

  • 서브쿼리의 실행결과가 1행1열인 쿼리
  • 서브쿼리의 특정함수 결과값이 하나일 때
  • =, >, >=, <, <=, !=, <> 연산자를 이용해서 작업

ex) 20번 부서에 속한 사원 중 전체 사원의 평균급여보다 높은 급여를 받는 사원의 정보와 소속부서 조회하기

select e.empno, e.ename, e.job, e.sal, d.deptno, d.dname
from emp e, dept d
where e.deptno=d.deptno
and d.deptno=20
and e.sal > (select avg(sal) from emp);

2. 다중행서브쿼리

  • 서브쿼리의 결과가 다중 행인 경우
  • 비교연산자를 사용할 수 없다. (비교연산자를 사용해야 하는 경우 any와 all을 같이 사용)
  • 다중행 연산자를 사용해야한다 : in, any, all

in
column in (값1, 값2.. ) => or 개념

any
: 서브쿼리가 반환한 값 중 하나라도 참이면 메인쿼리 조건식 true
column < any(값1, 값2..) : 최대값보다 작은 값을 조회
select ename, sal
from emp
where sal <any(800,1250,1600)

column > any : 최소값보다 큰 값을 조회
select ename, sal
from emp
where sal > any(900,1250,1600)

all
: 서브쿼리가 반환한 값 모두 참이여야 메인쿼리 조건식 true
column > all : 최대값보다 큰 값을 조회
select ename, sal
from emp
where sal > all(900,1250,1600)

column < all : 최소값보다 큰 값을 조회
select ename, sal
from emp
where sal < all(900,1250,1600)

3. 다중열 서브쿼리

  • 비교할 데이터를 여러 개 지정하여 두개 이상의 컬럼을 리턴하는 서브쿼리
where (컬럼1, 컬럼2) in (서브쿼리의 실행결과 값1,2)

ex: 각 부서에서 최소급여를 받는 사용자의 사원번호 ,부서번호, 급여를 중복값 없이 출력하기

select empno, deptno, sal
from emp
where (deptno,sal) in (select deptno, min(sal) 
			from emp 
			group by deptno);

4. 상호연관서브쿼리

  • 서브쿼리를 실행할 때, 메인쿼리 테이블의 컬럼을 참조해서 작업해야 하는 서브쿼리
  • 서브쿼리의 비교 값이 계속 바뀌는 경우 메인쿼리의 값을 참조해서 사용
    ————————————————
    |
    ——— 각각의 행에 입력된 값에 따라서 서브쿼리의 결과가 달라지는 경우
  • 메인쿼리의 테이블에 alias를 추가하고 참조해서 사용
  • 메인쿼리의 한 row 에 대해서 서브쿼리가 한 번씩 실행된다
  • 서브쿼리에서는 메인쿼리의 컬럼을 사용할 수 있으나 메인쿼리에서는 서브쿼리의 컬럼을 사용할 수 없다.

[실행흐름]
1. 메인쿼리에서 참조할 값을 이용해서 서브쿼리를 실행
2. 서브쿼리에서 실행 결과를 이용해서 메인쿼리를 실행
3. 1번과2번을 레코드 갯수 만큼 반복해서 실행

ex) sal이  본인 부서의 평균 급여보다 많은 사원의 사원번호, 사원명, 부서번호, sal을 구할때

select empno, ename, deptno, sal
  2  from emp main 
  3  where sal> (select avg(sal) 
  				from emp 
                where deptno = main.deptno);
     EMPNO ENAME	  DEPTNO	SAL
---------- ---------- ---------- ----------
      7499 ALLEN	      30       1600
      7566 JONES	      20       2975
      7698 BLAKE	      30       2850
      7788 SCOTT	      20       3000
      7839 KING 	      10       5000
      7902 FORD 	      20       3000

3. select절에 서브쿼리 (상호연관 서브쿼리와 연관 ㅇ)

  • 서브쿼리의 실행결과 컬럼이 한개, row도 한개의 결과
  • 메인쿼리의 컬럼을 사용하는 경우 성능저하

ex) 사원번호, 사원명, sal, 부서의 평균 급여 출력하기

select empno, ename, sal, (select avg(sal) 
							from emp 
                            where deptno=main.deptno)
							from emp main;
     EMPNO ENAME	     SAL (SELECTAVG(SAL)FROMEMPWHEREDEPTNO=MAIN.DEPTNO)
---------- ---------- ---------- ----------------------------------------------
      7369 SMITH	     800					   2175
      7566 JONES	    2975					   2175
      7788 SCOTT	    3000					   2175
      7876 ADAMS	    1100					   2175
      7902 FORD 	    3000					   2175
      7499 ALLEN	    1600					   1575
      7521 WARD 	    1300					   1575
      7654 MARTIN	    1250					   1575
      7698 BLAKE	    2850					   1575
      7844 TURNER	    1500					   1575
      7900 JAMES	     950					   1575

     EMPNO ENAME	     SAL (SELECTAVG(SAL)FROMEMPWHEREDEPTNO=MAIN.DEPTNO)
---------- ---------- ---------- ----------------------------------------------
      7782 CLARK	    2450				     2916.66667
      7839 KING 	    5000				     2916.66667
      7934 MILLER	    1300				     2916.66667
      7777 지연
  • view : 하나 이상의 테이블을 조회하는 select문(실행한 결과)을 저장할 수 있는 객체 => 자주 사용하는 코드에 이름을 주고 저장해놓는 것 !

    [생성방법]

create view avgempview
  2  as
  3  select deptno,avg(sal) avg
  4  from emp
  5  group by deptno;
View created.

[조회하기]

select * from avgempview;
    DEPTNO	  AVG
---------- ----------
	20	 2175
	30	 1575
	10 2916.66667

전제조건 : (시스템 계정에서) grant create view to scott; - 뷰 권한주기

5. from절에 사용하는 서브쿼리

프롬절에 사용하는 서브쿼리 = 인라인뷰

ex) 사원번호, 사원명, sal, 부서의 평균 급여 출력하기

select e.empno, e.ename, e.deptno, e.sal, a.avgsal  
	from emp e, (select deptno,avg(sal) avgsal 
  				from emp 
                group by deptno)a
  3  where e.deptno=a.deptno //기본키와 외래키(조인조건)
  4  and e.sal>a.avgsal;
     EMPNO ENAME	  DEPTNO	SAL	AVGSAL
---------- ---------- ---------- ---------- ----------
      7499 ALLEN	      30       1600	  1575
      7566 JONES	      20       2975	  2175
      7698 BLAKE	      30       2850	  1575
      7788 SCOTT	      20       3000	  2175
      7839 KING 	      10       5000 2916.66667
      7902 FORD 	      20       3000	  2175

5. top-N쿼리

ex) emp테이블 사원 중 급여를 많이 받는 상위 3명의 이름, 급여, 부서번호를 조회

  • rownum컬럼 : 오라클에서 제공하는 가상 컬럼
 select rownum, ename, sal
 from (select * 
 	from emp 
    order by sal desc)
where rownum<4 
and sal is not null;
    ROWNUM ENAME	     SAL
---------- ---------- ----------
	 1 KING 	    5000
	 2 SCOTT	    3000
	 3 FORD 	    3000

더 정확히 랭크를 알기 위해서는 rank() over() 를 쓴다. 👇🏻

select ename, sal, rank() over(order by sal desc) as 순위
	from emp where sal is not null;
ENAME             SAL       순위
---------- ---------- ----------
KING		 5000	       1
FORD		 3000	       2
SCOTT		 3000	       2
JONES		 2975	       4
BLAKE		 2850	       5
CLARK		 2450	       6
ALLEN		 1600	       7
TURNER		 1500	       8
MILLER		 1300	       9
WARD		 1300	       9
MARTIN		 1250	      11
ENAME             SAL       순위
---------- ---------- ----------
ADAMS		 1100	      12
JAMES		  950	      13
SMITH		  800	      14
profile
개발 걸음마 떼기 👩🏻‍💻

0개의 댓글