서브 쿼리

DataBase+SQL

서브 쿼리

campanula 2023. 6. 21. 17:16

SQL 가로 목록으로 표현

SELECT 
    COUNT(DECODE(DEPARTMENT_ID, 10, 1)) "10번 부서 인원 수", --부서번호 기준 DECODE는 범위값X, 일치하는 값 대상, 값이 같으면 1을 준다고 표현했음
    COUNT(DECODE(DEPARTMENT_ID, 20, 1)) "20번 부서 인원 수", --1명씩 COUNT, 2줄 써도 같은 의미,1의 개수 COUNTING
    COUNT(DECODE(DEPARTMENT_ID, 30, 1)) "30번 부서 인원 수",
    COUNT(DECODE(DEPARTMENT_ID, 40, 1)) "40번 부서 인원 수",
    COUNT(DECODE(DEPARTMENT_ID, 50, 1)) "50번 부서 인원 수",
    COUNT(DECODE(DEPARTMENT_ID, 50, 1)) "50번 부서 인원 수"
FROM EMPLOYEES;

1) 서브 쿼리

하나의 SELECT 문장의 절 안에 포함된 또 하나의 SELECT 문 => 메인쿼리: 서브 쿼리를 포함하고 있는 쿼리문, 서브쿼리: 포함된 또 하나의 쿼리

SELECT 컬럼
FROM 테이블
WHERE 칼럼 연산자 (SELECT~--서브쿼리): 괄호 묶기

실행 순서(우선 순위)는 서브쿼리가 먼저 한 번만 실행.

SUB QUERY 부분은 WHERE 절에 연산자 오른쪽에 위치해야 하며 반드시 괄호로 묶어야 한다.
서브 커리에서 SELECT 하지 않은 컬럼은 주 쿼리에서 사용할 수 없다.
특별한 경우(인라인 뷰 등)를 제외하고는 SUB QUERY 절에 ORDER BY 절이 올 수 없다.
서브 쿼리 안에 서브 쿼리가 들어갈 수 있다. 메모리가 허용하는 한 무제한으로 중첩할 수 있다.
단일 행 SUB QUERY와 다중 행 SUB QUERY에 따라 연산자를 잘 선택해야 한다.

--ORA-00907: 누락된 우괄호
--괄호 안에 ORDER BY
SELECT DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID=(SELECT DEPARTMENT_ID
FROM EMPLOYEES
WHERE FIRST_NAME='Susan'
ORDER BY DEPARTMENT_ID);

SELECT EMPLOYEE_ID, FIRST_NAME, SALARY, NVL(COMMISSION_PCT,0), TO_CHAR(HIRE_DATE,'YYYY.MM.DD') HIRE_DATE
FROM EMPLOYEES
WHERE DEPARTMENT_ID=(
    SELECT DEPARTMENT_ID
    FROM EMPLOYEES
    WHERE FIRST_NAME='Guy'
)
AND FIRST_NAME <> 'Guy'; --WHERE절 부정 연산자 <>:같지않음

2)단일 행 서브 쿼리

내부 SELECT문장으로부터 오직 하나의 로우(행, ROW)만을 반환 받으며, 단일 행 비교 연산자를 사용

1] 서브 쿼리에서 그룹 함수의 사용: 서브 쿼리를 사용하여 평균 급여보다 더 많은 급여를 받는 사원을 검색

SELECT 그룹함수(컬럼) FROM 테이블; --일반 컬럼과 같이 쓰여지지 않았을 경우

SELECT 컬럼 그룹함수(컬럼)
FROM 테이블
GROUP BY 컬럼

--서브 쿼리를 사용하여 평균 급여보다 더 많은 급여를 받는 사원을 검색
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY>(SELECT AVG(SALARY)
              FROM EMPLOYEES);
              
--문제1)EMPLOYEES 테이블에서 LAST_NAME 컬럼에서 Kochhar의 급여보다 많은 사원의 정보를
--사원번호, 이름, 담당업무, 급여를 출력해 주세요
SELECT EMPLOYEE_ID, FIRST_NAME, JOB_ID, SALARY
FROM EMPLOYEES
WHERE SALARY >(SELECT SALARY
               FROM EMPLOYEES
               WHERE LAST_NAME='Kochhar');

--문제2) 가장 적은 급여를 받는 사원의 사번, 이름, 급여를 출력
SELECT EMPLOYEE_ID,FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY = (SELECT MIN(SALARY) FROM EMPLOYEES);

--추가문제 가장 많은 급여를 받는 사원 이름과 사원의 핸드폰 번호를 출력
SELECT FIRST_NAME, PHONE_NUMBER
FROM EMPLOYEES
WHERE SALARY = (SELECT MAX(SALARY) FROM EMPLOYEES);

--문제3)가장 오랜 기간 근무한 사원의 이름과 이메일, 담당업무, 입사일을 출력
SELECT FIRST_NAME, EMAIL, JOB_ID, HIRE_DATE
FROM EMPLOYEES
WHERE HIRE_DATE = (SELECT MIN(HIRE_DATE) FROM EMPLOYEES);

3)다중 행 서브 쿼리 => 비교 연산자 사용 불가능

서브 쿼리에서 반환되는 결과가 하나 이상의 행일 때 사용하는 서브 쿼리

반드시아래와 같은 다중 행 연산자와 함께 사용

종류	의미
IN	메인 쿼리의 비교 조건('=' 연산자로 비교할 경우)이 서브 쿼리의 결과 중에서 하나라도 일치하면 참
ANY	메인 쿼리의 비교 조건이 서브
ALL	메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 모든 값이 일치하면 참
EXIST	메인 쿼리의 비교 조건이 서브 쿼리의 결과 중에서 만족하는 값이 하나라도 존재하면 참

1] IN 연산자

결과가 2개 이상 구해지는 쿼리문을 서브 쿼리로 기술할 경우에 다중 행 연산자와 함께 사용해갸 한다.

IN 연산자는 메인 쿼리의 비교 조건에서 서브 쿼리의 출력 결과와 하나라도 일치하면 메인 쿼리의 WHERE 절의 참이 되도록하는 연산자

WHERE 컬럼 IN(값,값,값,..)
WHERE 컬럼 = 값 OR 컬럼 = 값 OR...

2] ALL연산

ALL 조건은 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 모든 값이 일치하면 참. 찾아진 값에 대해서 AND 연산을 해서 모두 참이면 참, > ALL은 모든 비교값보다 크냐고 묻는 것이 되므로 최대값보다 더 크면 참이 된다.

따라서 < 로 비교하면 서브쿼리에 MIN 함수를 적용한 효과, > 로 비교하면 서브쿼리에 MAX 함수를 적용한 효과

--30번 소속 직원들 중에서 급여를 가장 많이 받은 사원보다 더 많은 급여를
--받는 사람의 이름, 급여를 출력 (30번 부서 직원 급여들 모두에 대해서 커야 하므로 최대값보다 큰 급여만)
SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > ALL (SELECT SALARY
                    FROM EMPLOYEES
                    WHERE DEPARTMENT_ID =30); --전부 만족

3] ANY 연산자(연산자 > ANY 는 찾아진 값에 대해서 하나라도 크면 참)

ANY조건은 메인 쿼리의 비교 조건이 서브쿼리의 검색 결과와 하나 이상만 일치하면 참

> ANY는 찾아진 값에 대해서 하나라도 크면 참이 되므로 찾아진 값 중에서 가장 작은 값 즉, 최소값 보다 크면 참이 된다.

< 연산자로 비교하면 서브쿼리에 MAX 함수를 적용한 효과가 나고, > 연산자로 비교하면 서브 쿼리에 MIN 함수를 적용한 효과가 난다.

SELECT FIRST_NAME, SALARY
FROM EMPLOYEES
WHERE SALARY > ANY (SELECT SALARY
                   FROM EMPLOYEES
                   WHERE DEPARTMENT_ID =110);

상호 연관 서브 쿼리: 상호 연관 Sub Query란 Main Query값을 Sub Query에 주고 Sub Query를 수행한 후
그 결과를 다시 Main Query로 반환해서 수행하는 Sub Query를 말한다.
즉, Main Query와 Sub Query가 서로 데이터를 주고 받는 형태이다.

EXISTS 연산자
EXISTS 연산자는 서브 쿼리문에서 주로 사용하며 서브 쿼리의 결과 값이 참이 나오기만 하면
바로 메인 쿼리의 결과 값을 리턴한다.

SELECT EMPLOYEE_ID, FIRST_NAME, E.JOB_ID, JOB_TITLE
FROM EMPLOYEES E INNER JOIN JOBS J ON E.JOB_ID = J.JOB_ID
WHERE EXISTS (SELECT *
              FROM JOB_HISTORY --사원에 잇는 정보를 참조해서 가져온다: 상호 연관 서브 쿼리-> 괄호 안에 것만 실행 불가능
              WHERE E.EMPLOYEE_ID = EMPLOYEE_ID)
ORDER BY E.EMPLOYEE_ID;

4) 서브 쿼리로 테이블 작성하기

SUB QUERY는 오는 위치에 따라서 그 이름이 다름

--서브쿼리
SELECT 컬럼 -- 메인쿼리
FROM 테이블
WHERE 컬럼 연산자()(SUBQUERY);

--인라인 뷰
SELECT 컬럼
FROM (SELECT~(SUBQUERY));

--스칼라 서브쿼리
SELECT 컬럼명, (SELECT 컬럼(SUBQUERY) FROM 테이블
				WHERE 내부 테이블. 컬럼 = 외부 테이블.컬럼)
FROM 테이블;

--모든 사원의 사원번호, 이름, 관리자 번호, 관리자명을 조회
SELECT EMPLOYEE_ID, FIRST_NAME, MANAGER_ID, NVL((SELECT M.FIRST_NAME FROM EMPLOYEES M
                                             WHERE M.EMPLOYEE_ID = E.MANAGER_ID), '없음') 관리자명
FROM EMPLOYEES E
ORDER BY 1;

[문제] EMPLOYEES 테이블에서/ 급여가/ 자신이 속한 부서/의 평균 급여/보다 /많이 받는/ 사원의 부서번호, 이름, 급여를 출력하는 SELECT문을 작성하시오.

--[문제] EMPLOYEES 테이블에서/ 급여가/ 자신이 속한 부서/의 평균 급여/보다 /많이 받는/ 사원의 부서번호, 이름, 급여를 출력하는 SELECT문을 작성하시오.
SELECT E.DEPARTMENT_ID, FIRST_NAME, E.SALARY
FROM EMPLOYEES E
WHERE E.SALARY > (SELECT AVG(SALARY)
                    FROM EMPLOYEES D
                    WHERE D.DEPARTMENT_ID = E.DEPARTMENT_ID)
ORDER BY E.DEPARTMNET_ID, E.SALARY DESC;

테이블의 구조만 복사

CREATE TABLE EMP03
AS
SELECT *
FROM EMPLOYEES
WHERE 1=0;

5)서브 쿼리를 이용한 데이터 추가

서브 쿼리를 사용하여 INSERT 문장을 작성하며 VALUES 절은 사용하지 않는다.

VALUES 절에 기술하는 자료를 서브 쿼리에서 얻어온다. 단, 서브 쿼리의 값 개수와 INSERT할 테이블의 열 수가 일치해야한다.

6)서브 쿼리를 이용한 데이터 수정

7)서브 쿼리를 이용한 두 개 이상의 칼럼에 대한 값 변경

UPDATE TABLE_NAME
SET COLUMN_NAME1 = (SUB_QUERY1), COLUMN_NAME2 = (SUB_QUERY2),...
WHERE 조건;

UPDATE TABLE_NAME
SET(COLUMN_NAME1, COLUMN_NAME2) = (SUB_QUERY)
WHERE 조건;

--20번 부서의 부서명과 지역명을 30번 부서의 부서명과 지역명으로 수정.
UPDATE DEPT01
SET DEPARTMENT_NAME = (SELECT DEPARTMENT_NAME
                       FROM DEPT01
                       WHERE DEPARTMENT_ID =30),
    LOCATION_ID = ( SELECT LOCATION_ID
                    FROM DEPT01
                    WHERE DEPARTMENT_ID =30)
WHERE DEPARTMENT_ID =20;

UPDATE DEPT01
SET(DEPARTMENT_NAME, LOCATION_ID) = (SELECT DEPARTMENT_NAME, LOCATION_ID
                                    FROM DEPT01
                                    WHERE DEPARTMENT_ID = 30)
WHERE DEPARTMENT_ID =20;

8) 서브 쿼리를 이용한 데이터 삭제

외래키, 참조키를 찾아 삭제, 수정하기 위한 쿼리문

--HR 사용자로 생성한 DEPT01 테이블과 참조키(외래키) 설정 테이블 확인
SELECT FK.OWNER, FK.CONSTRAINT_NAME, FK.TABLE_NAME
FROM ALL_CONSTRAINTS FK, ALL_CONSTRAINTS PK
WHERE FK.R_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
AND PK.OWNER = 'HR'
AND FK.CONSTRAINT_TYPE='R'
AND PK.TABLE_NAME = 'DEPT01'
ORDER BY FK.TABLE_NAME;