[ SQL ] Oracle 확장 sql문 #3 _ 서브쿼리

[ 서브쿼리 ]

 1. 서브쿼리의 개요 

• 서브쿼리(SUBQUERY)는 하나의 쿼리문의 결과를 다른 쿼리문에 전달하기 위해 두 개 이상의 쿼리문을 하나의 쿼리문으로 연결하여 처리하는 방법이다.
• 서브쿼리는 select 문에 내포된 select 문을 ( )(퍼렌씨시스) 안에 적용하여 사용할 수 있다.

---

• 사원인 SMITH 의 부서명을 조회하기 위해서는 사원인 SMITH 의 부서번호를 조회하고 나서 조회한 부서번호로 부서명을 조회할 수 있다.
• 서브쿼리를 적용하면 부서번호인 20 대신에 사원인 SMITH 의 부서번호를 조회하는 쿼리문을 기술하면 한 개의 쿼리문으로 부서명을 조회할 수가 있다.

 

▼SELECT * FROM EMP;

▼ 부서번호인 20 대신에 사원인 SMITH 의 부서번호를 조회하는 쿼리문

SELECT DEPTNO FROM EMP
WHERE ENAME = 'SMITH';

---

▼SELECT * FROM DEPT;

 

▼

SELECT DNAME FROM DEPT
WHERE DEPTNO = 20;

---

▼서브쿼리 적용

SELECT DNAME FROM DEPT     --메인쿼리
WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME = 'SMITH');   --서브쿼리

---

• 서브쿼리는 메인쿼리가 실행되기 전에 한 번씩 실행되고 서브쿼리에서 실행된 결과가 메인쿼리에 전달되어 최종적인 결과를 출력한다.
• 내포된 서브쿼리는 메인 쿼리 이전에 한 번만 수행되며 서브쿼리의 결과를 메인쿼리의 조건으로 제공하게 된다.
• 서브쿼리는 테이블 자체의 데이터에 의존하는 조건으로 테이블의 컬럼값을 조회할 필요가 있을때 아주 유용하다.
• 서브쿼리는 다른 하나의 쿼리문에 포함된 select 문으로 명시되어 지지 않은 조건에 근거한 값들을 조회하는 select 문을 작성하는 데 유용하다.
• 서브쿼리의 결과는 메인 쿼리에 의해 조건 또는 데이터로 사용된다.

select 컬럼명, 컬럼명 from table

where 조건식(select 컬럼명, 컬럼명 from table where 조건식);

 

 

 2. 서브쿼리의 유형

 

(1) 단일행 서브쿼리

• 단일행 서브쿼리는 서브쿼리에서 단 하나의 행만을 검색하여 메인쿼리에 반환하는 질의문이다.
• 단일행 서브쿼리는 내부 select 문으로부터 하나의 행만을 조회한다.
• 단일행 서브쿼리는 서브쿼리의 결과로 하나의 행만이 출력되어야 한다.
• 메인쿼리의 where 문에서 서브쿼리의 결과와 비교할 때는 반드시 단일행 비교 연산자 중 하나만 사용해야 한다.
• 단일행 비교 연산자:
  *  =(이퀄) : 같다.
  *  !=(엑스클러메이션 포인트 이퀄) : 같지 않다.
  *  >(라이트 앵글브래킷) : 크다.
  *  >=(라이트 앵글브래킷 이퀄) : 크거나 같다.
  *  <(레프트 앵글브래킷) : 작다.
  *  <=(레프트 앵글브래킷 이퀄) : 작거나 같다.

① 단일행 연산자 적용

단일행 서브쿼리는 단일행 연산자를 사용하고 where 문에 기술된 컬럼의 개수와 데이터 타입은 select 문에 기술된 컬럼과 좌측으로부터 일대일 대응이 되며 데이터 타입이 일치해야 한다.

 ex 1 )  emp 테이블에서 SMITH 의 부서명을 호출한다.

SELECT DNAME FROM DEPT WHERE DEPTNO=(SELECT DEPTNO FROM EMP WHERE ENAME = 'SMITH');

 

 ex 2 )  emp 테이블에서 사원번호가 7698 인 사원의 급여보다 많은 사원의 사원번호, 사원명, 직급, 급여 순으로 조회한다.

SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE  SAL > (SELECT SAL FROM EMP WHERE EMPNO = 7698);

 

 

 ex 3 )   emp 테이블에서 사원번호가 7698 인 사원의 급여보다 적은 사원의 사원번호, 사원명, 직급, 급여 순으로 조회한다.

SELECT EMPNO, ENAME, JOB, SAL FROM EMP WHERE SAL < (SELECT SAL FROM EMP WHERE EMPNO = 7698);

 

②  그룹 함수 적용

단일행을 반환하는 서브쿼리에 그룹 함수를 사용하여 메인쿼리로부터 데이터를 출력할 수 있다.

 ex )   emp 테이블에서 부서번호가 20 번인 부서의 최소 급여보다 많은 부서번호를 조회한다.

SELECT DEPTNO, MIN(SAL) FROM EMP GROUP BY DEPTNO HAVING MIN(SAL) > (SELECT MIN(SAL)FROM EMP WHERE DEPTNO = 20);

 

③조회 순번 적용

3-1) rownum 컬럼

• rownum 컬럼은 슈도(Pseudo) 컬럼으로 mysql 데이터베이스의 limit 키워드와 같은 기능이다.
• rownum 컬럼은 쿼리의 결과에 차례대로 숫자 1 부터 순차적으로 번호를 부여한다.
• rownum 컬럼은 게시판의 페이징 처리할 때 사용한다.

 ex 1 )   emp 테이블에서 rownum 슈도 컬럼으로 ename 컬럼을 10 개까지만 조회한다.

SELECT ROWNUM, ENAME FROM(SELECT * FROM EMP ORDER BY EMPNO) WHERE ROWNUM <= 10;

 

 

 ex 2 )   emp 테이블에서 rownum 슈도 컬럼의 별칭으로 ename 컬럼을 10 개까지만 조회한다.

SELECT * FROM (SELECT ROWNUM NUM, ENAME FROM EMP ORDER BY EMPNO) WHERE NUM <= 10;

 

 

3-2) row_number( ) over(order by column…) 문

• row_number 함수는 번호를 입력하는 함수로 over 함수와 order by 문과 같이 사용한다.
• over 함수는 order by 문이나 group by 문의 서브쿼리를 개선하기 위해 나온 함수이다.
• over 함수는 집계함수나 분석함수와 함께 사용된다.
• row_number( ) over(order by column…) 문은 게시판의 페이징 처리할 때 사용한다

row_number( ) over(order by column1, column2, … columnN)

 

 ex )   emp 테이블에서 row_number( ) over(order by column…) 문으로 ename 컬럼을 10 개까지만 조회한다.

SELECT * FROM(SELECT ROW_NUMBER() OVER(ORDER BY EMPNO)AS NUM, ENAME FROM EMP) WHERE NUM<=10;

 

 

(2) 다중행 서브쿼리 

• 다중행 서브쿼리는 select 문으로부터 하나 이상의 행을 조회한다.
• 다중 행 서브쿼리는 다중행 연산자를 사용하며 다중행 연산자는 다음과 같다.
  *  in 연산자 : 같은 값을 찾는다.
  *  all 연산자 : 최대값과 최소값을 반환한다.
  *  any 연산자 : 최소값과 최대값을 반환한다.
  *  exists 연산자 : 서브쿼리에 값이 있을 때 반환한다.

①  in 연산자

• in 연산자는 2 개 이상의 값을 반환하는 서브쿼리에 대하여 비교 연산자를 비교하여 반환하면 에러가 발생하므로 이런 경우 서브쿼리에서 반환된 목록에 대한 각각의 값을 비교하여 값의 결과가 참일 때 쿼리를 수행한다.
• in 연산자는 메인쿼리의 비교 조건에서 서브쿼리의 출력 결과와 하나라도 일치하면 메인쿼리의 where 문으로 조회할 조건을 참으로 반환한다.
• in 연산자는 =(이퀄) 연산자로 or 연산자로 연결한 것과 같은 의미이다.

 ex )   emp 테이블에서 20 번을 초과한 부서번호의 사원 급여를 조회한다.

SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL IN (SELECT SAL FROM EMP WHERE DEPTNO > 20);

 

 

② any 연산자

• any 연산자는 비교 연산자와 서브쿼리 사이에 반환된 목록에 대한 각각의 값을 비교하여 값의 결과가 참일 때 쿼리를 수행한다.
• any 연산자는 메인쿼리의 비교 조건이 서브쿼리의 조회 결과와 하나 이상만 일치하면 참으로 반환한다.
• any 연산자는 서브쿼리의 결과값 중에 어느 하나의 값이라도 만족이 되면 결과값을 반환한다

 

 ex )   emp 테이블에서 30 번인 부서번호의 사원 중에 최소 급여보다 많은 급여를 받는 사원을 조회한다.

SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL > ANY (SELECT SAL FROM EMP WHERE DEPTNO = 30);

 

③ all 연산자

• all 연산자는 비교 연산자와 서브쿼리 사이에 반환된 목록에 대한 모든 값을 비교하여 값의 결과가 참일 때 쿼리를 수행한다.
• all 연산자는 메인쿼리의 비교 조건이 서브쿼리의 조회 결과와 모든 값이 일치하면 참으로 반환한다.
• all 연산자는 서브쿼리의 결과값 중에 모든 결과값이 만족하여야만 결과값을 반환한다.

 ex )   emp 테이블에서 30 번인 부서번호의 사원 중에 최대 급여보다 많은 급여를 받는 사원을 조회한다.

SELECT ENAME,SAL,DEPTNO FROM EMP WHERE SAL > ALL (SELECT SAL FROM EMP WHERE DEPTNO = 30);

 

④ exists 연산자

• exists 연산자는 서브쿼리에서 적어도 1 개의 행을 반환하면 논리식은 참이고 그렇지 않으면 거짓이 된다.
• exists 연산자는 서브쿼리의 결과값이 참이 나오기만 하면 바로 메인쿼리의 결과값을 반환한다.
• exists 연산자를 사용하면 서브쿼리의 데이터가 존재하는가를 먼저 따져 존재하는 값들만을 결과로 반환한다.

 ex )   emp 테이블의 부서번호가 30 인 조건에 의해서 dept 테이블을 조회한다.

SELECT*FROM DEPT WHERE EXISTS(SELECT * FROM EMP WHERE DEPTNO = 30);