오라클 서브쿼리

최주영·2023년 4월 8일
오라클
0

오라클

목록 보기
13/22

서브쿼리

  • SELECT문 안에 SELECT문이 하나 더 있는 쿼리문
  • 서브쿼리는 반드시 ()안에 작성을 해야한다.
  • 하지만 서브쿼리문 보다는 join을 사용하는것이 속도가 더 빠르다
  • 단일 select 문 사용만으로 복잡한 조건식을 만들 때 사용
  • 메인쿼리 안에 서브쿼리가 존재

단일행 서브쿼리, 다중행 서브쿼리 다중열 서브쿼리 는 WHERE 절에 서브쿼리를 사용한 것!

✅ 단일행 서브쿼리

  • 서브쿼리 SELECT 문의 결과가 1개열, 1개행인 것
  • 단일행 연사자 : =, >, <, >=, <=, <>, !=
  • 컬럼, WHERE 절에서 사용한다!
// WHERE 절에 사용한 예시
// 윤은해 사원과 동일한 급여를 받고 있는 사원을 조회하기
SELECT *
FROM EMPLOYEE 
WHERE SALARY = (SELECT SALARY FROM EMPLOYEE WHERE EMP_NAME = '윤은해') AND EMP_NAME != '윤은해' ;


// 컬럼절에 사용한 예시
// 사원들의 급여 평균보다 많이 급여를 받는 사원의 이름, 급여, 부서코드를 출력하기
// 컬럼절에도 서브쿼리를 쓸수 있음 
SELECT EMP_NAME, SALARY, DEPT_CODE, (SELECT AVG(SALARY) FROM EMPLOYEE) AS AVG
FROM EMPLOYEE
WHERE SALARY >= (SELECT AVG(SALARY) FROM EMPLOYEE);

// 부서가 총무부인 사원을 조회하기
SELECT * FROM EMPLOYEE
    WHERE DEPT_CODE = (SELECT DEPT_ID FROM DEPARTMENT WHERE DEPT_TITLE = '총무부');

✅ 다중행 서브쿼리

  • 서브쿼리의 결과가 한개열 다수 행(ROW)을 갖는것

  • IN 연산자를 활용

SELECT * 
FROM EMPLOYEE
WHERE JOB_CODE IN(SELECT JOB_CODE FROM JOB WHERE JOB_NAME IN('과장','부장'));

SELECT *
FROM employees
WHERE salary IN (SELECT MAX(salary)  // 서브쿼리안의 값들은 다중 값들이 나옴  ex) 8200.6500.12008....
					FROM employees
                    GROUP BY department_id);
  • 다중행에 대한 대소비교
    -> ANY : OR로 ROW를 연결
    -> ALL : AND로 ROW를 연결

컬럼 >(=) ANY(서브쿼리) : 다중행 서브쿼리의 결과 중 하나라도 크면 참 -> 다중행 서브쿼리의 결과 중 최소값보다 크면
ex) ANY(6000,10000,12000) -> 6000 보다 큰 것들은 다 조회됨

컬럼 <(=) ANY(서브쿼리) : 다중행 서브쿼리의 결과 중 하나라도 작으면 참 -> 다중행 서브쿼리의 결과 중 최대값보다 작으면
ex) ANY(6000,10000,12000) -> 12000 보다 작은 것들은 다 조회됨

컬럼 >(=) ALL(서브쿼리) : 다중행 서브쿼리의 결과가 모두 클 때 참 -> 다중행 서브쿼리의 결과 중 최대값보다 크면 참
ex) ALL(6000,10000,12000)
-> 3개 조건 다 해당되야하기때문에 제일 큰 12000보다 큰 값들은 다 조회됨

컬럼 <(=) ALL(서브쿼리) : 다중행 서브쿼리의 결과가 모두 작을 때 참 -> 다중행 서브쿼리의 결과 중 최소값보다 작으면 참
ex) ALL(6000,10000,12000)
-> 3개 조건 다 해당되야하기때문에 제일 작은 6000보다 작은 값들은 다 조회됨

// 컬럼 >(=) ANY(서브쿼리)
SELECT * 
FROM EMPLOYEE
WHERE SALARY >= ANY(SELECT SALARY FROM EMPLOYEE WHERE DEPT_CODE IN('D5','D6'));
// ANY(SELECT SALARY FROM EMPLOYEE WHERE DEPT_CODE IN('D5','D6'); 의 결과의 최소값보다 큰값들만 출력

SELECT *  // 위결과와 동일한 결과
FROM EMPLOYEE
WHERE SALARY >= (SELECT MIN(SALARY) FROM EMPLOYEE WHERE DEPT_CODE IN('D5','D6'));


// 컬럼 <(=) ANY(서브쿼리)
SELECT * 
FROM EMPLOYEE
WHERE SALARY <= ANY(SELECT SALARY FROM EMPLOYEE WHERE DEPT_CODE IN('D5','D6'));
// ANY(SELECT SALARY FROM EMPLOYEE WHERE DEPT_CODE IN('D5','D6'); 의 결과의 최대값보다 작은값만 출력

SELECT *  // 위결과와 동일한 결과
FROM EMPLOYEE
WHERE SALARY <= (SELECT MAX(SALARY) FROM EMPLOYEE WHERE DEPT_CODE IN('D5','D6'));


// 컬럼 >(=) ALL(서브쿼리)
// 2000년 1월 1일 이전 입사자 중 2000년 1월 1일 이후 입사한 사원 중 가장 높게 받는 사원보다 급여을 높게 받는
// 사원의 사원명, 급여, 조회
SELECT EMP_NAME, SALARY
FROM EMPLOYEE
WHERE HIRE_DATE < '00/01/01'   
    AND SALARY > ALL(SELECT SALARY FROM EMPLOYEE WHERE HIRE_DATE > '00/01/01');

✅ 다중열 서브쿼리

  • 열이 다수, 행이 1개인 쿼리문
// 퇴직한 여사원의 같은부서, 같은 직급에 해당하는 사원 조회하기
SELECT *
FROM EMPLOYEE
WHERE DEPT_CODE = (SELECT DEPT_CODE FROM EMPLOYEE WHERE ENT_YN='Y' AND SUBSTR(EMP_NO,8,1) = '2')
    AND JOB_CODE = (SELECT JOB_CODE FROM EMPLOYEE WHERE ENT_YN='Y' AND SUBSTR(EMP_NO,8,1) = '2')
    AND ENT_YN = 'N';

✅ 스칼라 서브쿼리

  • SELECT 절에 사용하는 서브 쿼리로써 단순한 JOIN을 대체할 목적으로 사용되는 경우가 많다
SELECT department_name, (SELECT AVG(salary)
						FROM employees
                        GROUP BY department_name)
FROM departments;

✅ 상관 서브쿼리

  • 서브쿼리를 구성할 때 메인쿼리에 값을 가져와 사용하게 설정
  • 메인쿼리의 값이 서브쿼리의 결과에 영향을 주고, 서브쿼리의 결과가 메인쿼리의 결과에 영향을 주는 쿼리문
// 본인이 속한 부서의 사원수를 조회를 하기
// 사원명, 부서코드, 사원수
// 유동적으로 값이 바껴야함
SELECT EMP_NAME, DEPT_CODE, (SELECT COUNT(*) FROM EMPLOYEE WHERE DEPT_CODE = E.DEPT_CODE) AS 사원수
FROM EMPLOYEE E;


// WHERE에 상관서브쿼리 이용하기
// EXISTS(서브쿼리) : 서브쿼리에 결과가 1행 이상이면 TRUE, 0행 FALSE
// 각 게시글의 댓글수 구할 때 이용함
SELECT *
FROM EMPLOYEE E
WHERE EXISTS(SELECT 1 FROM EMPLOYEE WHERE MANAGER_ID = E.EMP_ID);


//  FROM 절에 서브쿼리 이용하기
// 가상컬럼을 포함하고 있거나, JOIN을 사용한 SELECT문을 사용
// EMPLOYEE 테이블에 성별을 추가해서 출력하기
SELECT *
FROM (
    SELECT E.*, DECODE(SUBSTR(EMP_NO,8,1),1,'남',2,'여',3,'남',4,'여')AS GENDER
    FROM EMPLOYEE E
)WHERE GENDER = '여';
// 사원의 이름, 급여, 부서명, 소속부서급여평균 조회하기
SELECT EMP_NAME,SALARY, DEPT_TITLE,(SELECT AVG(SALARY) FROM EMPLOYEE WHERE E.DEPT_CODE = DEPT_CODE)
FROM EMPLOYEE E  // 주쿼리인 테이블과 서브쿼리테이블을 비교하기 위해서 주쿼리 테이블을 E로 별칭 설정
    JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID; // DEPT_TITLE 컬럼을 출력하기 위해 JOIN한거임
// 자신이 속한 직급의 평균급여보다 많이 받는 직원의 이름, 직책명, 급여를 조회하기

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 E.JOB_CODE = JOB_CODE
// 서브쿼리 안의 값들에서 출력되는 SELECT AVG(SALARY)는 이중포문처럼 계속 값이 바뀜
// 즉 서브쿼리 안의 값들은 직급마다 평균값이 나온다

✅ 인라인 뷰 (INLINE VIEW)

  • 하나의 테이블처럼 사용하는 것

  • FROM 절에 서브쿼리 이용하기

  • FROM 절에서 사용된 서브쿼리의 결과가 하나의 테이블에 대한 뷰(View)처럼 사용됨
    💡 뷰 : 저장장치에 내에 물리적으로 존재하진 않지만 사용자에게 있는 것처럼 보여지는 가상테이블

  • 가상컬럼을 포함하고 있거나, JOIN을 사용한 SELECT문을 사용

// 부서별 평균 연봉보다 높은 사람들의 연봉 출력하기
SELECT E.last.name, E.salary, D.avg_sal
FROM employees E, (SELECT department_id, AVG(salary) avg_sal
				  FROM employees
                  GROUP BY department_id) D //부서별 연봉을 구하기 위해서 인라인 뷰 사용
WHERE E.department_id = D.department_id
AND E.salary > D.avg_sal;

// EMPLOYEE 테이블에 성별을 추가해서 출력하기 (여자만 출력)
SELECT *
FROM (
    SELECT E.*, DECODE(SUBSTR(EMP_NO,8,1),1,'남',2,'여',3,'남',4,'여')AS GENDER
    FROM EMPLOYEE E  	
)WHERE GENDER = '여';


// 인라인뷰에 JOIN 활용했을 때
SELECT * 
FROM (
    SELECT * FROM EMPLOYEE
        LEFT JOIN DEPARTMENT ON DEPT_CODE = DEPT_ID
        JOIN JOB USING(JOB_CODE)
)
WHERE DEPT_TITLE = '총무부';

	
// 인라인뷰에 집합연산자 활용했을 때
SELECT *
FROM (SELECT EMP_ID AS CODE, EMP_NAME AS TITLE
     FROM EMPLOYEE
     UNION
     SELECT DEPT_ID, DEPT_TITLE
     FROM DEPARTMENT
     UNION
     SELECT JOB_CODE, JOB_NAME
     FROM JOB
)
WHERE CODE LIKE '%1%';    
    
    
// EMPLOYEE 테이블 E에 모든 값들과 자동으로 1부터 부여되는 ROW 번호 출력
SELECT *
  	FROM(SELECT ROWNUM AS RNUM, E.*
        FROM(SELECT * FROM EMPLOYEE)E);
        
        
// 밑에 SELECT E.* 부분이 없으면 오류남 (FROM절의 테이블을 갖고 조회하기때문)
SELECT T.*, T.SALARY*12 AS 연봉  
FROM (SELECT E.*, D.*, (SELECT TRUNC(AVG(SALARY),-1) FROM EMPLOYEE 
        WHERE DEPT_CODE = E.DEPT_CODE) AS DEPT_SAL_AVG
        FROM EMPLOYEE E 
        LEFT JOIN DEPARTMENT D ON DEPT_CODE = DEPT_ID) T
WHERE DEPT_SAL_AVG > 3000000;

✅ RANK_OVER() 함수

함수를 사용하지 않고 순위를 구할 수 있다! 

// ROW에 순위를 정하고 출력하기, ORDER BY 같이 사용해야함
// TOP-N 출력하기
// 급여를 많이 받는 사원 1~3위까지 출력하기
SELECT ROWNUM, E.* FROM EMPLOYEE E
WHERE ROWNUM BETWEEN 1 AND 3
ORDER BY SALARY DESC;


// SELECT문을 실행할때마다 ROWNUM이 생성이됨
// ROWNUM 의 범위는 반드시 1부터 시작해야 범위를 구할 수 있다
SELECT ROWNUM, T.*
FROM(
    SELECT ROWNUM AS INNERNUM, E.*
    FROM EMPLOYEE E
    ORDER BY SALARY DESC
)T
// WHERE ROWNUM >= 2;  // 결과값이 출력되지 않음
WHERE ROWNUM >= 1; // 출력 됨


// 이 방법으로 하면 1부터 시작안해도 출력 가능
// from절로 한번 더 덮었기 때문에
SELECT *
FROM(
    SELECT ROWNUM AS RNUM, T.*
    FROM(SELECT *
        FROM EMPLOYEE 
        ORDER BY SALARY DESC) T)
WHERE RNUM BETWEEN 5 AND 10;

RANK_OVER 함수를 이용한 방법

SELECT *
   FROM(SELECT EMP_NAME, SALARY, 
        RANK() OVER(ORDER BY SALARY DESC) AS NUM,//공동 등수 있으면 중복해서 값을넣고 값 순서는 계속 증가됨
        DENSE_RANK() OVER(ORDER BY SALARY DESC) AS NUM2 
        // -- DENSE_RANK는 공동 등수가 있어도, 중복해서 랭크를 넣지않고, 그 다음값 출력해줌
         FROM EMPLOYEE
        )
WHERE NUM BETWEEN 1 AND 23;	



// 평균급여를 많이 받는 부서 3개 출력
SELECT *
FROM(
    SELECT RANK() OVER(ORDER BY SAL_AVG DESC) AS SAL_ORDER, D.*
    FROM (    
        SELECT DEPT_TITLE, AVG(SALARY) AS SAL_AVG
        FROM DEPARTMENT 
            JOIN EMPLOYEE ON DEPT_CODE = DEPT_ID
            GROUP BY DEPT_TITLE
        ORDER BY 2 DESC
    )D
)
WHERE SAL_ORDER > 3;
profile
최주영
우측 상단 햇님모양 클릭하셔서 무조건 야간모드로 봐주세요!!
이전 포스트

오라클 DDL CREATE

다음 포스트

오라클 제약조건

0개의 댓글