서브쿼리(SUBQUERY) & DDL(Data Manipulation Language) 2021-09-23

목차

1. 서브쿼리

- 단일행 서브쿼리

- 다중행 서브쿼리

- 다중열 서브쿼리

- 다중행 다중열 서브쿼리

- 상관 서브쿼리

- 스칼라 서브쿼리

- 인라인 뷰 + WITH 

2.  DML(Data Manipulation Language) - 데이터 조작 언어

- INSERT

- UPDATE

- MERGE

- DELETE + TRUNCATE (DDL)

 

 

 

---

 

1. 서브쿼리 (SUBQUERY)

- 하나의 SQL문 안에 포함되어있는 또 다른 SQL문장

- 메인 쿼리 (기존 쿼리)를 위해 보조 역할을 하는 문장

- SELECT, FROM, WHERE, HAVING에서 사용 가능

 

1) 단일행 서브쿼리

- 서브쿼리의 조회 결과 값이 1개

- 비교 연산자를 사용한다 ( < , > , <= , >= , != , <> )

 

ex) 전 직원의 급여 평균보다 많은 급여를 받는 직원의 이름과 급여를 조회

-- 직원 테이블에서 평균보다 높은 급여를 받는 직원 조회

SELECT E_NAME   -- 직원 이름
      ,E_SALARY -- 직원 급여
  FROM EMPLOYEE 
 WHERE SALARY > (SELECT AVG(SALARY)
                   FROM EMPLOYEE);

 

2) 다중행 서브쿼리

- 서브쿼리의 조회 결과 값의 개수가 여러 행일 경우

- 일반적인 비교연산자 사용 X

- IN / NOT IN : 여러 결과 값 중 하나라도 일치하면 / 하나도 없다면 

- > ANY : 여러 결과 값 중 아무 것(ANY)보다 크다. ==> 결과 값들 중 가장 작은 값보다는 크다.

- < ANY : 여러 결과 값 중 아무 것(ANY)보다 작다. ==> 결과 값들 중 가장 큰 값보다는 작다.

- > ALL : 여러 결과 값 중 모든 것(ALL)보다 크다. ==> 결과 값들 중 가장 큰 값보다 크다.

- < ALL : 여러 결고 값 중 모든 것(ALL)보다 작다. ==> 결과 값들 중 가장 작은 값보다 작다.

- EXISTS / NOT EXISTS : 값이 존재한다 / 존재하지 않는다.

 

ex) 부서별 최고 급여를 받는 직원의 이름, 부서, 급여 조회

- 부서별 최고 급여를 서브쿼리로 사용

-- 부서별 최고 급여 받는 직원의 이름,부서,급여 조회

SELECT E_NAME       -- 직원 이름
      ,E_DEPARTMENT -- 직원 부서
      ,E_SALARY     -- 직원 급여
  FROM EMPLOYEE     -- 직원 테이블
 WHERE SALARY IN (SELECT MAX(SALARY)
                    FROM EMPLOYEE
                   GROUP BY E_DEPARTMENT);

 

ex2) 직급이 '대리'인 직원 중 '과장' 직급의 최소 급여보다 많이 받는 직원의 이름, 직급, 급여 조회

- > ANY 활용

-- '과장'의 최저 급여보다 많이 받는 '대리' 직원 조회

SELECT E_NAME   -- 이름
      ,E_JOB    -- 직급
      ,E_SALARY -- 급여
  FROM EMPLOYEE
 WHERE E_JOB = '대리'
       AND SALARY > ANY (SELECT SALARY
                           FROM EMPLOYEE
                          WHERE E_JOB = '과장');

 

3) 다중열 서브쿼리

- 서브쿼리의 결과 값은 단일행, 서브쿼리 SELECT 절에 나열된 컬럼 수가 여럿일 경우

- WHERE 절 다음 괄호 ( ) 안에 컬럼명들을 넣어서 비교한다.

 

ex) 퇴사한 직원과 같은 부서, 같은 직급을 가지고 있는 직원의 이름,부서,직급 조회 (조회 결과에 퇴사자 포함)

SELECT E_NAME       -- 이름
      ,E_DEPARTMENT -- 부서
      ,E_JOB        -- 직급
  FROM EMPLOYEE
 WHERE (E_DEPARTMENT, E_JOB) = (SELECT E_DEPARTMENT, E_JOB
                                 FROM EMPLOYEE
                                WHERE ENTIRE_YN = 'Y'); -- 은퇴여부 (Y/N)

 

4) 다중행 다중열 서브쿼리

- 서브쿼리 조회 결과 행 , 열이 모두 여러개

- 다중열 서브쿼리에 사용했던 WHERE절 다음 괄호 작성 + 다중행 서브쿼리에 사용했던 연산자 (IN , ANY , ALL 등)

 

ex) 본인이 속한 직급의 평균 급여만큼 받고 있는 직원의 이름, 직급, 급여 조회

SELECT E_NAME   -- 이름
      ,E_JOB    -- 직급
      ,E_SALARY -- 급여
  FROM EMPLOYEE
 WHERE (E_JOB, E_SALARY) IN (SELECT TRUNC(AVG(SALARY),-4) -- 급여 평균은 만원단위로 나오도록 잘라냄
                               FROM EMPLOYEE
                              GROUP BY E_JOB);

 

5) 상관 서브쿼리 ************* (헷갈리므로 계속 공부하기!)

- 메인쿼리가 사용하는 테이블 값을 서브쿼리가 이용해서 결과를 만들어 낸다.

- ==> 메인쿼리의 테이블 값이 변경되면 서브쿼리의 결과 값도 바뀌게 된다.

- 기존 서브쿼리와는 다르게 메인쿼리 먼저 조회하고, 서브쿼리의 조건을 충족하는지 확인하고 SELECT 진행

 

ex) 급여 평균보다 많이 받는 직원을 직급별로 나누고 이름, 직급, 급여를 조회

SELECT E_NAME
      ,JOB
      ,SALARY
  FROM EMPLOYEE E1
 WHERE SALARY > (SELECT AVG(SALARY)
                 FROM EMPLOYEE E2
                 WHERE E2.JOB = E1.JOB);

* 상관 서브쿼리를 사용하지 않고 단순히 GROUP BY로 묶어서 사용하면,

  조건이 불명확해서 원하는 결과 값이 나오지 않을 수도 있다.

 

6) 스칼라 서브쿼리

- SELECT 절에 사용되는 서브쿼리 결과로 1행만 반환한다. (단일행)

 

ex) 모든 사원의 사번, 이름, 관리자 사번, 관리자명 조회

     단, 관리자가 없는 경우 '없음'으로 표시

- 스칼라 + 상관 쿼리

SELECT  E_ID "사번"
      , E_NAME "이름"
      , NVL(MANAGER_ID, '없음') "관리자사번"
      , NVL( (SELECT E_NAME 
                FROM EMPLOYEE E2
               WHERE E1.MANAGER_ID = E2.E_ID), '없음') "관리자명"
  FROM EMPLOYEE E1;

 

7) 인라인 뷰 (INLINE-VIEW)

- FROM 절에 사용되는 서브쿼리

- 서브쿼리가 만들어낸 결과의 집합 (RESULT SET)을 테이블 대신 사용

 

ex) 전 직원 중 급여가 높은 상위 5명의 순위, 이름, 급여 조회

SELECT ROWNUM  -- 행번호(순위)
      ,E_NAME  -- 이름
      ,SALARY  -- 급여
  FROM (SELECT E_NAME, SALARY 
          FROM EMPLOYEE
         ORDER BY SALARY DESC)
 WHERE ROWNUM <= 5;

- 순위대로 정렬된 서브쿼리 결과 값을 FROM으로 받아 순위 출력

 

+) WITH 

- 서브쿼리에 이름을 붙이고 사용 (변수 선언과 비슷한 느낌?)

- 인라인 뷰로 사용될 서브쿼리에 주로 사용

- 실행속도가 빨라진다

 

ex) 전 직원의 급여 순위 조회

-- WITH 미사용

SELECT ROWNUM 순위
     , E_NAME 이름
     , SALARY 급여
  FROM (SELECT E_NAME, SALARY 
          FROM EMPLOYEE
         ORDER BY SALARY DESC);
         
         
-- WITH 사용!
WITH SALARY_RANK AS (
                     SELECT E_NAME, SALARY 
                       FROM EMPLOYEE
                      ORDER BY SALARY DESC
                     )
SELECT ROWNUM 순위
     , E_NAME 이름
     , SALARY 급여
  FROM SALARY_RANK;

---

2. DML (Data Manipulation Language) - 데이터 조작 언어

- 테이블에 값을 삽입, 수정, 삭제하는 구문

 

 

1) INSERT 

- 테이블에 새로운 행을 추가하는 구문

- INSERT INTO 테이블명 VALUES ( 데이터, 데이터, ... )

=> 테이블에 모든 컬럼에 대한 값을 삽입(INSERT) 할 때 사용

=> INSERT하고자 하는 컬럼이 모든 컬럼인 경우 컬럼명 생략 가능. 단, 컬럼의 순서를 지켜서 값을 삽입해야 한다.

- INSERT INTO 테이블명 ( 컬럼명, 컬럼명, 컬럼명,... ) VALUES ( 데이터1, 데이터2, 데이터3, ... ) ;

=> 테이블에 선택한 컬럼에 대한 값만 INSERT할 때 사용.

=> 선택안된 컬럼은 값이 NULL

- (참고) INSERT 시 VALUES 대신 서브쿼리도 사용 가능하다!

 

 

2) UPDATE

- 테이블에 기록된 컬럼의 값을 수정하는 구문

- UPDATE 테이블명 SET 컬럼명 = 바꿀값 [WHERE 컬럼명 비교연산자 비교값];

- 조건을 설정하지 않고 작성하면 모든 행의 컬럼값이 변경된다.

- 여러 컬럼을 수정할 경우 콤마( , )로 컬럼 구분

- UPDATE에서도 서브쿼리 사용 가능하다

 

 

3) DELETE

- 테이블의 행을 삭제하는 구문

- DELETE FROM 테이블명 WHERE 조건설정

- 조건절 설정 안하면 모든 행이 삭제됨

 

+) TRUNCATE (DDL)

- 테이블 전체 행을 삭제하는 DDL

- DELETE보다 수행속도가 빠름

- ROLLBACK을 통해 복구 불가능