SUB QUERY (서브쿼리)
~ SUB QUERY ~
- SELECT문 안에 SELECT문이 있음
- 단일 SELECT문으로 조건식을 끝내기엔 조건이 부족할 때
- 완전히 다른 테이블에서 데이터 값을 조회한 뒤 메인 쿼리의 조건으로 사용하려 할 때 유용
- 하나의 SQL문에 포함되어 있는 또 다른 SQL문을 의미한다
사용방법
- 서브쿼리를 (괄호)로 감싸서 사용
- 서브쿼리는 단일/복수 비교연산자와 함께 사용이 가능하다.
- 단 ORDER BY문과 사용하지 못 한다!
사용 가능한 위치
- SELECT, FROM, WHERE, HAVING, ORDER BY, INSERT문 VALUES절
단일행 서브쿼리
- 서브쿼리의 결과가 ★한 개★일 때 사용
-- 2021년 10월 보너스가 가장 많은 직원의 정보를 조회하는 쿼리(단일행 서브쿼리)
-- 조회항목 : 급여년월, 사원번호, 사원명, 직급, 급여액, 보너스
SELECT S.SALDATE, S.EMPNO, E.ENAME, E.JOB, S.SAL, S.COMM
FROM SALARY S, EMP E
WHERE S.SALDATE = '202110'
AND S.COMM = (SELECT MAX(COMM)
FROM SALARY
WHERE SALDATE = '202110')
AND S.EMPNO = E.EMPNO;
다중행 서브쿼리
- 서브쿼리 결과가 두 개 이상일 때 사용
- 다중행 비교 연산자 (IN, ALL, ANY, EXISTS)
| 다중행 연산자 | 설명 |
| IN | 임의의 값과 동일한 경우 |
| ALL | 모든 값을 만족할 경우 |
| ANY | 어느 하나의 값이라도 만족할 경우 |
| EXISTS | 만족하는 값이 존재하는지 여부를 확인 |
-- 2021년 10월 급여액이 3000만원 이상인 직원 정보를 조회하는 쿼리 작성
-- 조회항목 : 급여년월, 사원번호, 사원명, 직급, 부서명, 급여액
SELECT S.SALDATE 급여년월, E.EMPNO 사원번호, E.ENAME 사원명,
E.JOB 직급, D.DNAME 부서명, S.SAL 급여액
FROM EMP E, SALARY S, DEPT D
WHERE E.EMPNO IN(SELECT EMPNO
FROM SALARY
WHERE SALDATE = '202110'
AND SAL >= 3000)
AND E.EMPNO = S.EMPNO
AND S.SALDATE = '202110'
AND E.DEPTNO = D.DEPTNO;
쿼리 작성을 할 땐 순서도 중요하다. 옵티마이저가 위에서 아래로 읽어내려가기 때문에 많이 거르고 시작해야 불러오는 속도가 빠르다! WHERE절 첫번째에서 이미 21년 10월 테이블 중에서 3000이상 버는 사원을 뽑았기 때문에 적은 양의 컬럼을 가지고 온다.
FROM절에 사용하는 서브쿼리
- 인라인뷰
- FROM절에는 테이블만 오는 것이 원칙이지만 동적 생성이 된 테이블의 형태로 사용
- SQL문이 실행될 때만 임시적으로 생성되기 때문에 데이터베이스엔 저장이 안 됨
- 인라인뷰는 동적으로 조인 방식을 사용한다.
--INLINE VIEW
SELECT S.SALDATE 급여년월, E.EMPNO 사원번호, E.ENAME 사원명,
E.JOB 직급, D.DNAME 부서명, S.SAL 급여액
FROM EMP E, DEPT D, (SELECT EMPNO, SALDATE, SAL
FROM SALARY
WHERE SALDATE = '202110'
AND SAL >= 3000) S
WHERE E.EMPNO = S.EMPNO
AND E.DEPTNO = D.DEPTNO;
- 처음부터 테이블을 다 갖고오지 않기 때문에 속도가 빠르다!
- 처음 테이블을 갖고 올 때부터 2021년 10월 3000 이상의 칼럼들만 갖고 오기 때문에 일반 다중쿼리(위의 예제)에 필요한 S.SALDATE = '202110' 절이 없어도 된다.
다중행 연산자(ALL, ANY, EXISTS)
- 서브쿼리 작성시 결과값이 단일 튜플이 나올 수 있지만 다중 튜플일 경우도 있음
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE EMPNO IN(SELECT EMPNO
FROM SALARY
WHERE EMPNO >= ALL(SELECT EMPNO
FROM SALARY
WHERE EMPNO >= 7500
AND SALDATE = '202110')
AND SALDATE = '202110');
EXISTS
- 데이터가 존재할 경우 특정 액션을 해야할 때 사용
- 서브쿼리에 해당하는 튜플이 적어도 한 건 이상 존재한다면 'TRUE' 없으면 'FALSE'를 리턴한다. 일치하는 결과가 하나라도 있으면 쿼리를 더 이상 수행하지 않음 (SELECT, INSERT, UPDATE, DELETE에서 사용 가능)
-- EXISTS
-- 지난달 급여액이 3000만원 넘는 애가 있으면 모든 직원들 정보를 불러와서 50만원씩 추가
-- 직원번호, 직원이름, 이번달 급여
-- 경영진에서 보는 자료를 만들 때 사용을 많이 한다.
SELECT E.EMPNO, E.ENAME, S.SAL + 50 "이번달 급여"
FROM EMP E, SALARY S
WHERE SALDATE = '202109'
AND E.EMPNO = S.EMPNO
AND EXISTS (SELECT SAL
FROM SALARY
WHERE SALDATE = '202109'
AND SAL >= 3000);
NOT EXISTS
- 데이터가 존재하지 않을 경우 특정 액션을 해야할 때 사용
-- NOT EXISTS
SELECT E.EMPNO, E.ENAME, S.SAL + 50 "이번달 급여"
FROM EMP E, SALARY S
WHERE SALDATE = '202109'
AND E.EMPNO = S.EMPNO
AND NOT EXISTS (SELECT SAL
FROM SALARY
WHERE SALDATE = '202109'
AND SAL > 5000);
서브쿼리를 활용한 DDL
- 테이블의 구조와 정보를 복사하는 방법
- CREATE, ALTER, DROP, TRUNCATE
테이블의 구조와 정보를 그대로 복사
--서브쿼리를 활용한 DDL
CREATE TABLE EMP_TEMP AS
SELECT * FROM EMP;
EMP와 똑같은 구조를 가진 EMP_TEMP 테이블이 생겼다.
+) 복사한 테이블의 정보를 갖고오기
DESC EMP_TEMP;
스크립트 창을 통해서 컬럼명과 유형 등을 볼 수 있다.
두 개 이상의 테이블을 조인해서 복사
--EMP_INPO 사원번호, 사원명, 직급, 부서명
CREATE TABLE EMP_INFO AS
SELECT E.EMPNO, E.ENAME, E.JOB, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;
DESC EMP_INFO;
구조를 확인하면 복사가 잘 된 것을 확인할 수 있다.
서브쿼리를 활용한 DML
- 테이블의 구조와 정보를 복사하는 방법
DELETE FROM EMP_TEMP WHERE EMPNO >= '7600';
COMMIT;
SELECT * FROM EMP_TEMP;
TRUNCATE TABLE EMP_TEMP;
DELETE 조건에 해당하는 데이터를 삭제한다.
TRUNCATE TABLE 데이터를 모두 삭제했다.
둘의 차이점은 COMMIT이 있냐 없냐의 차이이다.
다시 셀렉트로 확인해보면 테이블과 구조는 그대로 있고 데이터만 날아간 걸 확인할 수 있다.
-- 2021년 10월 급여가 3000만원 이상인 직원이 있다면,
-- 급여테이블을 불러와서 전 직원에게 50만원 추가(UPDATE)
UPDATE SALARY_TEMP SET SAL = SAL + 50
WHERE EMPNO IN (SELECT EMPNO
FROM SALARY_TEMP
WHERE SALDATE = '202110'
AND SAL >= 3000);
-- 2021년 10월 급여와 보너스를 포함하여 3000만원 이상인 급여테이블 정보 삭제
DELETE FROM SALARY_TEMP
WHERE EMPNO IN (SELECT EMPNO
FROM SALARY_TEMP
WHERE SALDATE = '202110'
AND SAL+COMM >= 3000)
AND SALDATE = '202110';
ROLLBACK; --되돌리기-- SALARY 테이블에 2021년 10월 데이터 중 급여액이 3000만원 이상인 경우 삭제(커밋함)
-- 단, 기존 데이터를 SALARY_TEMP로 생성 및 백업후 SALARY 테이블은 삭제한 후에 다시
-- SALARY_TEMP 데이터와 일치하도록 적용
-- 옮기기 / 삭제 / 옮기기
CREATE TABLE SALARY_TEMP AS
SELECT * FROM SALARY;
DESC SALARY_TEMP;
DELETE FROM SALARY
WHERE EMPNO IN(SELECT EMPNO
FROM SALARY
WHERE SALDATE = '202110'
AND SAL >= 3000)
AND SALDATE = '202110';
COMMIT;
DROP TABLE SALARY;
CREATE TABLE SALARY AS
SELECT * FROM SALARY_TEMP;
SALARY_TEMP 테이블을 SALARY 테이블 복사해서 만들기 => 테이블 정보 확인 => 해당 데이터 골라서 삭제 => 커밋 => 테이블 삭제 => SALARY를 SALARY_TEMP 테이블 복사해서 만들기