학습 과정 공유 및 후기/SQLD
[ SQLD ] Oracle SQL (6) - 서브쿼리
커피마시는어린왕자
2020. 5. 21. 16:05
1. 서브쿼리(Sub-Query)
- SQL 문장 안에서 보조로 사용되는 또 다른 SELECT문을 의미한다.
- 최종 결과를 출력하는 쿼리를 Main Query(메인 쿼리)라고 한다면, 이를 위한 중간 단계 혹은 보조 역할을 하는 SELECT문을 Sub Query(서브 쿼리)라 한다.
- 하나의 SQL문을 기준으로 메인 쿼리를 제외한 나머지 모든 SELECT문을 서브쿼리로 보면 된다.
- 서브쿼리는 메인쿼리가 실행되기 이전에 한 번만 실행된다.
- 서브쿼리는 여러 개를 사용할 수 있다.
- 서브쿼리가 가지는 장점은 한 번 디스크에서 읽어온 데이터를 메모리 안에서 가공해서 사용할 수 있도록 도와주는 것이다. 즉, 동일한 데이터를 다시 한번 이용하여 복잡한 가공에도 물리적인 I/O를 줄여준다.
- 서브 쿼리는 SELECT, FROM, WHERE 절 모두 사용가능하며, INSERT, UPDATE, MERGE, DELETE 문에서도 사용가능하다.
2. 서브쿼리 사용시 주의사항
- 반드시 WHERE절에, 비교연산자의 오른쪽에 위치해야 한다.
- 서브쿼리라는 것을 알리기 위해 괄호로 묶어야 한다.
- 서브쿼리 절 안에 Order by 절이 들어가면 안 된다.
1) ANY
- ANY는 서브쿼리의 여러 개의 결과 중 한 가지만 만족한다.
- 즉, ANY는 OR같이 하나만 조건이 충족해도 전부 출력해준다.
2) ALL
- ALL은 서브쿼리의 여러 개의 결과를 모두 만족한다.
- 즉, ALL은 AND같이 조건이 모두 만족해야만 출력해준다.
예제
Q1. 사원테이블에서 'JONES'보다 월급을 많이 받는 사원의 월급를 출력하자
먼저 기존에 알고 있는 방식으로 쿼리를 2개 작성해보자!
STEP 1. JONES의 월급을 구해본다
SELECT SAL FROM EMP
WHERE ENAME = 'JONES' SAL
----------
2975
STEP 2. 2975보다 월급을 많이 받는 사원들을 구한다.
SELECT * FROM EMP
WHERE SAL > 2975;
이제는 SUB-QUERY로 위의 두 과정을 합쳐볼 것이다. SUB-QUERY의 뜻 그대로, 쿼리 안에 쿼리가 있는 것이다.
SELECT * FROM EMP
WHERE SAL > (SELECT SAL
FROM EMP
WHERE ENAME = 'JONES');
STEP 2의 두 번째 줄 2975(JONES의 월급)가, 위 코드에서 2975의 자리 대신에
SELECT SAL
FROM EMP
WHERE ENAME = 'JONES'라는 하나의 쿼리가 있음을 알 수 있다.
이처럼 쿼리 안에 쿼리를 넣음으로써, 쿼리 2개를 하나로 합치는 작업을 수행한 것이다.
Q2. 사원테이블에서 작업이 'SALESMAN'인 사원들과 같은 월급 받는 사원의 이름과 월급을 출력해보자.
더보기
SQL> SELECT ENAME, JOB FROM EMP
2 WHERE SAL = (SELECT SAL FROM EMP WHERE JOB = 'SALESMAN');
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
// 두 번째 줄을 보면 '='가 오류임을 알 수 있는데, 그 이유는 =는 '하나의 값'만을 리턴하기 때문이다.
// 2번째 코드를 풀자면
// SAL = 1600,1200,1250,1500인 셈이다.
// 이렇게 실행하게 되면 오류가 나기 때문에, SAL을 복수의 월급과 비교할 필요가 있으니, IN 키워드를 써야 한다.
SQL> SELECT ENAME, SAL FROM EMP
2 WHERE SAL IN (SELECT SAL FROM EMP WHERE JOB = 'SALESMAN');
ENAME SAL
-------------------- ----------
ALLEN 1600
MARTIN 1250
WARD 1250
TURNER 1500
Q3. 직업이 CLERK인 사원과 같은 부서에서 근무하는 사원의 이름과 월급, 부서번호를 출력해보자.
ENAME SAL DEPTNO
-------------------- ---------- ----------
MILLER 1300 10
JAMES 950 30
SMITH 800 20더보기
SQL> SELECT ENAME, SAL, DEPTNO FROM EMP
2 WHERE DEPTNO IN (SELECT DEPTNO FROM EMP WHERE JOB = 'CLERK');
Q4. 'CHICAGO'에서 근무하는 사원들과 같은 부서에서 근무하는 사원의 이름과 월급을 출력. [CHICAGO > DEPT Tab]
더보기
SQL> DESC DEPT;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
SQL> SELECT ENAME, SAL FROM EMP
2 WHERE DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC = 'CHICAGO');
ENAME SAL
-------------------- ----------
ALLEN 1600
WARD 1250
MARTIN 1250
BLAKE 2850
TURNER 1500
JAMES 950
Q5. 부하직원이 있는 사원의 사원번호와 이름을 출력하자.
더보기
SQL> SELECT EMPNO, ENAME, MGR FROM EMP; // 데이터의 구조를 확인해본다.
// EMPNO가 MGR 중에 있으면 그 직원은 매니저라는 의미.
EMPNO ENAME MGR
---------- -------------------- ----------
7369 SMITH 7902
7499 ALLEN 7698
7521 WARD 7698
7566 JONES 7839
7654 MARTIN 7698
7698 BLAKE 7839
7782 CLARK 7839
7839 KING
7844 TURNER 7698
7900 JAMES 7698
7902 FORD 7566
EMPNO ENAME MGR
---------- -------------------- ----------
7934 MILLER 7782
SQL> SELECT EMPNO, ENAME FROM EMP
2 WHERE EMPNO IN (SELECT DISTINCT MGR FROM EMP);
EMPNO ENAME
---------- --------------------
7902 FORD
7698 BLAKE
7839 KING
7566 JONES
7782 CLARK
Q6. 부하직원이 없는 사원의 사원번호와 이름을 출력하자.
더보기
SQL> SELECT EMPNO, ENAME FROM EMP WHERE EMPNO NOT IN (SELECT NVL(MGR,0) FROM EMP);
**NVL(MGR,0) = ISNULL(MGR,0)
EMPNO ENAME
---------- --------------------
7844 TURNER
7499 ALLEN
7521 WARD
7934 MILLER
7654 MARTIN
7369 SMITH
7900 JAMES
Q7. 본인의 매니저가 'KING' 사원의 이름과 월급을 출력하자.
더보기
SQL> SELECT ENAME, SAL FROM EMP
2 WHERE MGR IN (SELECT EMPNO FROM EMP WHERE ENAME = 'KING');
ENAME SAL
-------------------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
Q8. 20번 부서에서 월급을 가장 많이 받는 직원의 월급보다 더 많이 받는 모든 사원의 이름과 월급을 출력하라.
더보기
SQL> SELECT ENAME, SAL FROM EMP
2 WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 20);
ENAME SAL
-------------------- ----------
KING 5000
// MAX를 사용하지 말고 코드를 작성한다면?***
SQL> SELECT ENAME, SAL FROM EMP
2 WHERE SAL > ALL(SELECT SAL FROM EMP WHERE DEPTNO = 20);
Q8. 20번 부서에서 월급을 가장 적게 받는 직원의 월급보다, 더 많이 받는 모든 사원의 이름과 월급을 출력하라.
더보기
// MIN 사용
SQL> SELECT ENAME, SAL FROM EMP
2 WHERE SAL > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 20);// ANY 사용
SQL> SELECT ENAME, SAL FROM EMP
2 WHERE SAL > ANY(SELECT SAL FROM EMP WHERE DEPTNO = 20);
Q9. 직업이 'SALESMAN'인 사원 중 가장 큰 월급을 받는 사원보다 더 많은 월급을 받는 사원의 이름과 월급을 출력하라.
(단, MAX() 함수 사용하지 말고 연산자를 사용하기)
더보기
SQL> SELECT ENAME, SAL FROM EMP
2 WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE JOB = 'SALESMAN');
ENAME SAL
-------------------- ----------
JONES 2975
BLAKE 2850
CLARK 2450
KING 5000
FORD 3000SQL> SELECT ENAME, SAL FROM EMP
2 WHERE SAL > ALL(SELECT SAL FROM EMP WHERE JOB = 'SALESMAN');
유용한 명령어
- HELP SET