[DB]02-06.Oracle DB 서브 쿼리(sub Query), IN, ALL, EXISTS

Sub Query는 쿼리 안의 쿼리라고 이해하면 쉬울수도 있다.

일반적으로 Select ~ From ~;과 같이 쓰는데

서브 쿼리는 Select ~ From (Select ~ From ~);

위와 같이 쿼리 안에 쿼리는 넣는다

특정 테이블에서 뽑은 데이터를

다시한번 검색하는것이다.

보통 테이블을 2개 이상 사용할 때나 key값이 연관이 있을 때 사용한다.

예를 보면 좀 더 쉽게 이해될것이다.

-- 테이스를 위한 테이블 생성

-- 직원 테이블 생성
CREATE TABLE emp(
    ename VARCHAR2(20),
    job VARCHAR2(50),
    deptno VARCHAR2(10),
    hiredate DATE
);

-- 부서 테이블 생성
CREATE TABLE dept(
    deptno VARCHAR2(10),
    deptname VARCHAR2(20),
    loc VARCHAR2(10)
);

-- 테스트를 위한 데이터 삽입
INSERT INTO emp VALUES('lee', 'staff', 1, to_date('15/01/02', 'YY/MM/DD'));
INSERT INTO emp VALUES('kim', 'staff', 1, to_date('15/03/06', 'YY/MM/DD'));
INSERT INTO emp VALUES('jun', 'assistant', 2, to_date('15/05/15', 'YY/MM/DD'));
INSERT INTO emp VALUES('chu', 'staff', 1, to_date('15/07/08', 'YY/MM/DD'));
INSERT INTO emp VALUES('sin', 'manager', 2, to_date('15/10/28', 'YY/MM/DD'));
INSERT INTO emp VALUES('jin', 'assistant', 1, to_date('15/12/25', 'YY/MM/DD'));
INSERT INTO emp VALUES('son', 'staff', 2, to_date('16/01/13', 'YY/MM/DD'));
INSERT INTO emp VALUES('han', 'staff', 3, to_date('16/04/06', 'YY/MM/DD'));
INSERT INTO emp VALUES('hyun', 'leader', 4, to_date('16/08/15', 'YY/MM/DD'));
INSERT INTO emp VALUES('ma', 'manager', 4, to_date('16/11/11', 'YY/MM/DD'));

INSERT INTO dept VALUES(1, 'sales', 'NEWYORK');
INSERT INTO dept VALUES(2, 'dev01', 'LA');
INSERT INTO dept VALUES(3, 'personal', 'NEWYORK');
INSERT INTO dept VALUES(4, 'delevery', 'BOSTON');

SELECT * FROM emp;
SELECT * FROM dept;

-- deptno가 pk, fk가 된다.

-- 문제 1) han의 근무 부서를 찾아라
-- 1단계 - 결과 3
SELECT deptno FROM emp WHERE ename = 'han';
-- 2단계 - 결과 찾기
SELECT deptname FROM dept WHERE deptno = 3;
-- 3단계 - 합치기
SELECT deptname FROM dept WHERE (SELECT deptno FROM emp WHERE ename = 'han') = deptno;


-- 문제 2) 위치가 LA나 BOSTON인 부서에 속한 사람들의 이름과 직책
-- 1단계(in이 마음에 안들면 or을 쓰자
SELECT deptno FROM dept WHERE loc IN('LA', 'BOSTON');
-- 2단계
SELECT ename, job FROM emp WHERE deptno IN(2,4);
-- 3단계
SELECT ename, job FROM emp WHERE deptno IN(SELECT deptno FROM dept WHERE loc IN('LA', 'BOSTON'));
-- 비교 연산자
-- IN       : 메인 쿼리와 비교 조건이 서브 쿼리의 결과 중 하나라도 일치하면, OR와 비슷 (비교만 가능)
-- ALL      : 메인 쿼리와 비교 조건이 서브 쿼리의 결과 중 모두 일치하면, AND와 비슷
-- EXISTS   : 메인 쿼리와 비교 조건이 서브 쿼리의 결과 중 값이 하나라도 있으면, OR와 비슷 (작다, 크다 등 가능)


-- 문제 3) 직책이 MANAGER인 사원들 중 가장 입사일이 빠른 사원보다 입사일이 빠른 직원의 데이터
-- 1단계
SELECT MIN(hiredate) FROM emp WHERE job = 'manager';
-- 2단계
SELECT ename, job FROM emp WHERE hiredate < '15/10/28';
-- 3단계
SELECT ename, job FROM emp WHERE hiredate < (SELECT MIN(hiredate) FROM emp WHERE job = 'manager');


-- 문제 4) sales 부서에서 일하는 사원 정보 추출(이름, 직책, 입사일)
-- 1단계
SELECT deptno FROM dept WHERE deptname = 'sales';
-- 2단계
SELECT ename, job, hiredate FROM emp WHERE deptno = 1;
-- 3단계
SELECT ename, job, hiredate FROM emp WHERE deptno = (SELECT deptno FROM dept WHERE deptname = 'sales');



-- 문제 5) 부서별로 직원이 몇명인지 확인
-- 추출한 값을 하나의 컬럼처럼 보여주는 것 -> 상하관계 쿼리
SELECT deptname, loc, (SELECT COUNT(*) FROM emp WHERE emp.deptno = dept.deptno) AS cnt FROM dept;
-- dept d, dept AS d 와 같이 식별자를 지정하여 사용할 수 있음
SELECT deptname, loc, (SELECT COUNT(*) FROM emp WHERE emp.deptno = d.deptno) AS cnt FROM dept d;