서브 쿼리

1. 서브 쿼리의 기본 개념

 

하나의 SELECT문장의 절 안에 포함된 또 하나의 SELECT문장이다. 그렇기에 서브 쿼리를 포함하고 있는 쿼리문을 메인 쿼리, 포함된 또 하나의 쿼리를 서브 쿼리라 한다.

 

사원의 이름이 SCOTT인 사원이 어떤 부서 소속인지 소속 부서명을 알아내려면 조인을 사용해서 해결했지만 조인이 아닌 서브 쿼리문을 이용해서 해결할 수 있다.

 

먼저 SCOTT의 부서명을 알기 위해 부서 번호를 알아야 한다.

 

SQL> select deptno

            from emp

            where ename='SCOTT';

 

결과로 부서 번호가 20임을 알아내고 다음과 같이 쿼리문을 사용한다.

 

SQL> select dname

            from emp

            where deptno=20;

 

이 내용들을 서브쿼리로 변경하면 다음과 같다

 

SQL> select dname

            from emp

            where deptno=(select deptno

                                                          from emp

                                                          where ename='SCOTT')

서브 쿼리는 비교연산자의 오른쪽에 기술해야 하고 반드시 괄호로 둘러싸여야 한다.

서브 쿼리는 메인 쿼리가 실행되기 전에 한 번만 실행된다.

 

2. 단일 행 서브 쿼리

 

가장 많이 사용하는 조인 방법. 조인 대상이 되는 두 테이블에서 공통적으로 존재하는 칼럼의 값이 일치되는 행을 연결하여 결과를 생성하는 조인 방법이다.

 

EMP 테이블과 DEPT 테이블의 공통 칼럼인 DEPTNO의 값이 일치되는 조건을 WHERE절에 사용한다.

두테이블을 조인하려면 일치되는 공통 칼럼을 사용해야 한다. 컬럼 명이 같게 되면 혼동이 오기 때문에 칼럼명 앞에 테이블명을 점(.)과 함께 기술한다.

 

SQL> select ename, dname

            from emp, dept

            where emp.deptno=dept.deptno;

 

 

 

3. 다중 행 서브 쿼리

 

다중 행 서브 쿼리는 서브 쿼리에서 반환되는 결과가 하나 이상의 행일 때 사용하는 서브 쿼리이다.

 

다중행 서브쿼리는 반드시 아래와 같은 다중 행 연산자와 함께 사용해야 한다.

종류	의미
IN	메인쿼리의 데이터가 서브쿼리의 결과 중 하나라도 일치한 데이터가 있다면 true
ANY, SOME	메인쿼리의 조거식을 만족하는 서브쿼리의 결과가 하나 이상이면 true
ALL	메인쿼리의 조건식을 서브쿼리의 결과 모두가 만족하면 true
EXISTS	서브쿼리의 결과가 존재하면(즉, 행이 1개 이상일 경우) true

 

1) IN 연산자

 

결과가 2개 이상 구해지는 쿼리문을 서브 쿼리로 기술할 경우에는 다중 행 연산자와 함께 사용해야 한다.

 

Q) 급여를 3000이상 받는 사원이 소속된 동일한 부서에서 근무하는 사원을 출력

SQL> select ename, sal, deptno

            from emp

            where deptno in (select distinct deptno

                                                              from emp where sal >= 3000);

 

 

2) ALL 연산자

 

메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 모든 값이 일치하면 TRUE

 

Q) 30번 소속 사원들 중에서 급여를 가장 많이 받는 사원 보다 더 많은 급여를 받는 사람의 이름, 급여를 출력 (30번 부서 사원 급여들 모두에 대해서 커야하므로 최대값보다 큰 급여만 해당)

SQL> select ename, sal
            from emp
            where sal>all(select sal from emp where deptno=30);

 

3) ANY 연산자

 

메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 하나 이상만 일치하면 TRUE

 

Q) 부서 번호가 30번인 사원들의 급여 중 가장 작은 값보다 많은 급여를 받는 사원의 이름, 급여를 출력

SQL> select ename, sal
            from emp
            where sal>any(select sal from emp where deptno=30);

 

 

4. 서브쿼리로테이블 작성하기

 

1) 서브 쿼리로 테이블 복사하기

CREATE TABLE 테이블명
		AS SELECT * FROM 복사할테이블명;

 

2) 모든 칼럼이 아닌 특정 칼럼만 선택적으로 복사하려면 복사할 칼럼의 이름을 기술

CREATE TABLE 테이블명
		AS SELECT 복사할컬럼1, 복사할컬럼2 FROM 복사할테이블명;

 

3) 테이블 구조만 복사하기

CREATE TABLE 테이블명
		AS SELECT * FROM 복사할테이블명 WHERE 1=0;

 

 

5.  서브 쿼리를 이용한 데이터 추가

 

서브 쿼리를 사용하여 INSERT문장을 작성하며, VALUES 절은 사용하지 않는다.

VALUES 절에 기술하는 자료를 서브 쿼리에서 얻어오지만 서브 쿼리의 값 개수와 INSERT할 테이블의 열 수가 일치해야 한다

 

INSERT INTO 테이블명
	SELECT * FROM 복사할테이블명;

 

 

6.  서브 쿼리를 이용한 데이터 수정

 

Q) 10번 부서의 지역명을 40번 부서의 지역명으로 변경하기 위해 서브쿼리를 사용

SQL> update dept01

          set loc = ( select loc

                          from dept01

                          where deptno=40)

          where deptno=10;

 

 

7.  서브 쿼리를 이용한 두 개 이상의 칼럼에 대한 값 변경

 

서브 쿼리를 사용한 update 형식 두가지

 

1)

update 테이블명
       set 컬럼명1 = (서브쿼리1), 컬럼명2 = (서브쿼리2), ...
       where조건

 

2)

update 테이블명
       set (컬럼명1, 컬럼명2) = (서브쿼리)
       where조건

 

Q) 20번 부서의 부서명과 지역명을 30번 부서의 부서명과 지역명으로 수정

SQL> update dept01

          set (dname, loc) = ( select dname, loc

                                          from dept01

                                          where deptno=30)

          where deptno=20;

 

 

8.  서브 쿼리를 이용한 데이터 삭제

 

delete from 테이블명
            where 컬럼명 = (select 컬럼명
                           from 테이블명
                           where절)