DBMS/Oracle
D+27 [14. 서브 쿼리]
구일일구
2022. 8. 30. 17:27
반응형
14. 서브 쿼리
두 개 이상의 테이블 정보가 필요한 경우 서브 쿼리를 사용
서브 쿼리의 기본 개념
서브 쿼리는 하나의 SELECT 문장의 절 안에 포함된 또 하나의 SELECT 문장
메인 쿼리 : 서브 쿼리를 포함하고 있는 쿼리문
서브 쿼리 : 메인 쿼리에 포함된 또 하나의 쿼리
서브 쿼리는 비교 연산자의 오른쪽에 기술해야함 & 반드시 괄호로 둘러싸여야 함
서브 쿼리는 메인 쿼리가 실행되기 전에 한 번만 실행됨
>SCOTT의 부서 번호 출력 : 부서명을 알 수 있음
SELECT DEPTNO
FROM EMP
WHERE ENAME='SCOTT';
>부서 번호 20 : 부서명 출력
SELECT DNAME
FROM DEPT
WHERE DEPTNO = 20
>서브 쿼리로 변경하기
SELECT DNAME
FROM DEPT
WHERE DEPTNO = (SELECT DEPTNO
FROM EMP
WHERE ENAME='SCOTT');단일 행 서브 쿼리
내부 SELECT 문장으로부터 오직 하나의 로우(행, row)만을 반환 받음
단일행 비교 연산자(=, >, >=, <. <=, <>)를 사용함
2) 단일 행 서브 쿼리
> SMITH와 같은 부서에서 근무하는 사원의 정보를 출력하는 예
SELECT *
FROM EMP
WHERE DEPTNO = (SELECT DEPTNO FROM EMP WHERE ENAME='SMITH');
2-1) 서브 쿼리에서 그룹 함수의 사용
> 서브 쿼리 사용해서 평균 급여(2073)보다 더 많은 급여를 받는 사원 검색하기
SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT AVG(SAL) FROM EMP);다중 행 서브 쿼리
서브 쿼리에서 반환되는 결과가 하나 이상의 행일 때 사용하는 서브 쿼리
다중행 서브쿼리는 반드시 다중행 연산자와 함께 사용해야 함 : 결과가 2개 이상 구해지는 쿼리문을 서브 쿼리로 기술할 경우 다중행 연산자와 함께 사용
| 종류 | 의미 |
| IN | 메인 쿼리의 비교 조건('=' 연산자로 비교할 경우)이 서브 쿼리의 결과 중에서 하나라도 일치하면 참 |
| ANY, SOME | 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 하나 이상 일치하면 참 |
| ALL | 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 모든 값이 일치하면 참 |
| EXIST | 메인 쿼리의 비교 조건이 서브 쿼리의 결과 중에서 만족하는 값이 하나라도 존재하면 참 |
IN 연산자
하나라도 일치하면 참인 결과를 구하는 IN 연산자
3-1) IN 연산자
> 메인 쿼리의 비교 조건이 서브 쿼리 결과 중에서 하나라도 일치하면 참
> 급여를 3000 이상 받는 사원이 소속된 부서와, 동일한 부서에서 근무하는 사원 출력
SELECT ENAME, SAL, DEPTNO
FROM EMP
WHERE DEPTNO IN (SELECT DISTINCT DEPTNO
FROM EMP WHERE SAL>=3000);ALL 연산자
메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 모든 값이 일치하면 참
3-2) ALL 연산자
> 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 모든 값이 일치하면 참
> 30번 소속 사원들 중, 급여를 가장 많이 받은 사원보다 더 많은 급여를 받는 사람의 이름, 급여 출력
SELECT ENAME, SAL
FROM EMP
WHERE DEPTNO=30;
-> BLAKE, 2850받음
SELECT ENAME, SAL
FROM EMP
WHERE SAL > ALL (SELECT SAL FROM EMP WHERE DEPTNO=30);ANY 연산자
메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 하나 이상만 일치하면 참
찾아진 값에서 가장 작은 값(최소값)보다 크면 참
3-3)ANY 연산자
> 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 하나 이상만 일치하면 참
> 찾아진 값에서 가장 작은 값(최소값)보다 크면 참
> 부서 번호가 30번인 사람들의 급여 중 가장 작은 값(950)보다 많은 급여를 받는 사원의 이름,급여 출력
SELECT ENAME, SAL
FROM EMP
WHERE DEPTNO=30;
SELECT ENAME, SAL
FROM EMP
WHERE SAL > ANY(SELECT SAL FROM EMP WHERE DEPTNO=30);서브 쿼리로 테이블 작성하기
서브 쿼리로 테이블 복사하기
4) 서브 쿼리로 테이블 작성하기
4-1)서브 쿼리로 테이블 복사하기
DROP TABLE EMP01; --다음번에 할때 삭제할 수 있도록
CREATE TABLE EMP01 AS SELECT * FROM EMP; --복사하기
SELECT * FROM EMP01; --가져오기
> 모든 칼럼이 아닌 특정 칼럼만 복사하려면, 복사할 칼럼의 이름을 기술하기
DROP TABLE EMP02;
CREATE TABLE EMP02 AS SELECT EMPNO, ENAME FROM EMP;
SELECT * FROM EMP02;테이블의 구조만 복사하기
4-2) 테이블의 구조만 복사하기
DROP TABLE EMP03;
CREATE TABLE EMP03
AS SELECT * FROM EMP WHERE 1=0; --구조만 복사하기
SELECT * FROM EMP03; --내용은 없음
DESC EMP03; --구조만 복사된 것을 확인할 수 있음서브 쿼리를 이용한 데이터 추가
서브 쿼리를 사용하여 INSERT 문장을 작성, VALUES 절은 사용하지 않음
VALUES 절에 기술하는 자료를 서브 쿼리에서 얻어옴
단, 서브 쿼리의 값 개수와 INSERT할 테이블의 열 수가 일치해야 함
5) 서브 쿼리를 이용한 데이터 추가
> 서브 쿼리를 사용하여 INSERT 문장을 작성, VALES 절은 사용하지 않음
> VALUES절에 기술하는 자료를 서브 쿼리에서 얻어올 것
> 단, 서브 쿼리의 값 개수와 INSERT할 테이블의 열 수가 일치해야 함!
DROP TABLE DEPT01;
CREATE TABLE DEPT01 AS SELECT * FROM DEPT WHERE 1=0; --구조만 복사
SELECT * FROM DEPT01;
INSERT INTO DEPT01
SELECT * FROM DEPT; --서브 쿼리로 데이터 추가했음
SELECT * FROM DEPT01;서브 쿼리를 이용한 데이터 수정
6) 서브 쿼리를 이용한 데이터 수정
> 10번 부서의 지역명을 40번 부서의 지역명으로 변경
UPDATE DEPT01
SET LOC = (SELECT LOC
FROM DEPT01
WHERE DEPTNO=40)
WHERE DEPTNO=10;
SELECT * FROM DEPT01; --NEW YORK이 BOSTON으로 변경됨서브 쿼리를 이용한 두 개 이상의 칼럼에 대한 값 변경
7) 서브 쿼리를 이용한 두 개 이상의 칼럼에 대한 값 변경
> 서브 쿼리를 사용한 UPDATE 형식 2가지
--1)SET column_name1=(sub_query1), column_name2=(sub_query2),...
--2)SET (column_name1, column_name2) = (sub_query)
> 20번 부서의 부서명과 지역명을 30번 부서의 부서명과 지역명으로 수정하기(1)
UPDATE DEPT01
SET(DNAME, LOC) = (SELECT DNAME, LOC
FROM DEPT01
WHERE DEPTNO=30)
WHERE DEPTNO=20;
> 20번 부서의 부서명과 지역명을 30번 부서의 부서명과 지역명으로 수정하기(2)
UPDATE DEPT01
SET DNAME = (SELECT DNAME FROM DEPT01 WHERE DEPTNO=30),
LOC = (SELECT LOC FROM DEPT01 WHERE DEPTNO=30)
WHERE DEPTNO=20;
SELECT * FROM DEPT01;서브 쿼리를 이용한 데이터 삭제
8) 서브 쿼리를 이용한 데이터 삭제
> 예제로 사용할 테이블을 생성한다.
CREATE TABLE EMP01 AS SELECT * FROM EMP;
SELECT * FROM EMP;
>EMP01에서 DEPTNO=30번(서브쿼리 결과) 삭제
DELETE FROM EMP01
WHERE DEPTNO=(SELECT DEPTNO FROM DEPT WHERE DNAME='SALES');
SELECT * FROM EMP01; --30번 부서가 전부 삭제됨
반응형