오라클 서브쿼리, IN, EXISTS 차이

서브쿼리

SELECT *
FROM EMP
WHERE SAL > 800;

800을 서브쿼리로 바꾸기

SELECT *
FROM EMP
WHERE SAL > 
(
    SELECT SAL
    FROM EMP
    WHERE ENAME = 'SMITH'
);

 

 

조건이 1개, 비교값도 1개일 경우

SELECT *
FROM EMP
WHERE SAL > 
(
    SELECT SAL
    FROM EMP
    WHERE ENAME = 'SMITH'
);

 

조건이 1개, 비교값이 여러개일 경우

SELECT *
FROM EMP
WHERE SAL IN 
(
    SELECT SAL
    FROM EMP
    WHERE JOB = 'MANAGER'
);


--위 코드는 아래와 같다

SELECT *
FROM EMP
WHERE SAL IN (2975, 2850, 2450);

 

 

STUDENT 테이블과 DEPARTMENT 테이블을 사용하여 
'서진수'학생과 1전공(DEPTNO1)이 동일한 학생들의 
이름과 1전공 이름을 출력하세요

 

먼저 서진수 학생의  전공번호를 확인한다

SELECT DEPTNO1
FROM STUDENT
WHERE NAME = '서진수';

 

전공이 101인 학생의 이름과 전공이름을 출력한다

SELECT S.NAME STUD_NAME, d.dname DEPT_NAME
FROM STUDENT S, DEPARTMENT D
WHERE D.DEPTNO = 101 AND S.DEPTNO1 = d.deptno;

 

101자리에 서브쿼리를 넣는다

SELECT S.NAME STUD_NAME, d.dname DEPT_NAME
FROM STUDENT S, DEPARTMENT D
WHERE D.DEPTNO =
(
SELECT DEPTNO1
FROM STUDENT
WHERE NAME = '서진수'
) AND S.DEPTNO1 = d.deptno;

 

 

PROFESSOR 테이블과 DEPARTMENT 테이블을 조회하여

'주승재'교수보다 나중에 입사한 사람의 이름과 입사일, 학과명을 출력하세요.

 

먼저 주승재 교수의 입사일을 확인한다

SELECT HIREDATE
FROM PROFESSOR
WHERE NAME = '주승재';

 

위의 테이블을 서브쿼리하여 이름과 입사일 학과명을 출력한다

SELECT P.NAME , P.HIREDATE, d.dname
FROM PROFESSOR P, DEPARTMENT D
WHERE HIREDATE > 
(
SELECT HIREDATE
FROM PROFESSOR
WHERE NAME = '주승재'
)
AND p.deptno = d.deptno;

 

STUDENT 테이블에서 1전공(DEPTNO1)이 201번인 학과의

평균 몸무게보다 몸무게가 많은 학생들의 이름과 몸무게를 출력하세요.

 

먼저 201번 학과의 평균 몸무게를 검색한다

SELECT AVG(WEIGHT)
FROM STUDENT
WHERE DEPTNO1 = 201
GROUP BY DEPTNO1;

 

위의 테이블을 서브쿼리하여 학생들의 이름과 몸무게를 출력한다

SELECT NAME, WEIGHT
FROM STUDENT
WHERE WEIGHT >
(
SELECT AVG(WEIGHT)
FROM STUDENT
WHERE DEPTNO1 = 201
GROUP BY DEPTNO1
);

 

EMP2 테이블을 사용하여 전체 직원 중 '과장' 직급의 최소 연봉자보다
연봉이 높은 사람의 이름과 직급, 연봉을 출력하세요.
단, 연봉 출력 형식은 아래와 같이 천 단위 구분 기호화 $표시를 하세요.

 

먼저 EMP2 테이블의 과장직급의 최소 연봉을 구한다

    SELECT MIN(PAY)  -- MIN함수는 월급을 찾는다
    FROM EMP2
    WHERE POSITION = '과장'

 

4900만원이 최소치이므로 WHERE으로 그 이상인 사람들을 찾을 수 있다.

SELECT NAME, POSITION, to_char(pay, '$999,999,999')
FROM EMP2
WHERE pay > 49000000;

 

서브쿼리로 처음 코드를 넣으면

SELECT NAME, POSITION, to_char(pay, '$999,999,999')
FROM EMP2
WHERE pay >
(
    SELECT MIN(PAY)
    FROM EMP2
    WHERE POSITION = '과장'
);

 

 

EMP2 테이블과 DEPT2 테이블을 조회하여 각 부서별 평균 연봉을 구하고
그중에서 평균 연봉이 가장 적은 부서의 평균 연봉보다
적게받는 직원들의 부서명, 직원명, 연봉을 출력하세요.

 

먼저 각부서별 평균연봉을 구한다

SELECT AVG(PAY)
FROM EMP2
GROUP BY  DEPTNO
ORDER BY  AVG(PAY);

 

위의 코드를 서브쿼리하여서 1번째 것을 출력

SELECT *
FROM
(
  SELECT AVG(PAY)
  FROM EMP2
  GROUP BY  DEPTNO
  ORDER BY  AVG(PAY)
)
WHERE ROWNUM = 1;

 

그리고 출력할 것들을 SELECT 정리하고 25000000자리에 위 코드를 서브쿼리한다

SELECT DEPT2.DNAME, EMP2.NAME, TO_CHAR(EMP2.PAY, '$999,999,999') SALARY
FROM EMP2, DEPT2
WHERE PAY < 25000000 AND EMP2.DEPTNO = DEPT2.DCODE;

 

SELECT DEPT2.DNAME, EMP2.NAME, TO_CHAR(EMP2.PAY, '$999,999,999') SALARY
FROM EMP2, DEPT2
WHERE PAY < 
(
  SELECT *
  FROM
  (
    SELECT AVG(PAY)
    FROM EMP2
    GROUP BY  DEPTNO
    ORDER BY  AVG(PAY)
  )
  WHERE ROWNUM = 1
) 
AND EMP2.DEPTNO = DEPT2.DCODE;

 

 

IN 과  EXISTS의 차이

IN 은 서브쿼리 데이터를 WHERE 오른쪽의 DEPTNO 와 비교해서 같은 값인 경우에만 출력한다

    SELECT DEPTNO
    FROM DEPT
    WHERE DEPTNO = 20;

20이 출력

 

DEPTNO가 20인 모든 데이터를 출력

SELECT * FROM DEPT
WHERE DEPTNO IN (
    SELECT DEPTNO
    FROM DEPT
    WHERE DEPTNO = 20
    );

 

EXISTS는 서브쿼리 데이터가 있으면 메인 쿼리를 모두 실행하고, 없으면 아예 실행하지 않는다

SELECT * FROM DEPT
WHERE EXISTS (
    SELECT DEPTNO
    FROM DEPT
    WHERE DEPTNO = 20
    );

 

SELECT * FROM DEPT
WHERE EXISTS (
    SELECT DEPTNO
    FROM DEPT
    WHERE DEPTNO = 50
    );