[Oracle] subquery / Scalar subquery / inline view

 subquery 

- 쿼리문 안에 (select) 쿼리문을 정의하는 쿼리문

- create, insert, update, delete, select에서 사용할 수 있음

- 안쪽 쿼리문이 한 행을 조회하는 단수행 서브쿼리(single row subquery)와

   안쪽 쿼리문이 여러 행을 조회하는 복수행 서브쿼리(multi row subquery)를 사용할 수 있음

=> subquery 문이 1행만 조회하는 거면 단수행 서브쿼리(single row subquery)
      여러 행을 조회하는 거면 복수행 서브쿼리(multiple row subquery)

 

 

1) create subquery

- 조회된 결과로 테이블을 복사할 때, 조회된 결과로 비어있는 테이블을 생성할 때

- 제약사항은 복사되지 않으나, not null 조건은 복사됨

- 복수행 subquery

- 비어있는 테이블(구조만)을 생성하기 위해서는 where절에 1=0(항상 거짓)로 기입하면 된당

    > where절에 해당하는 레코드만 검색되는 것이므로!

       (1=1은 항상 참으로 모든 레코드를 조회하고 1= 0은 항상 거짓으로 모든 레코드를 조회 X)

    > 1=1은 dynamic query문을 만들 때 동적 조건을 편하게 붙이기 위해 사용

- 문법) create table 테이블명 as (select ,,,) ; --> 원천 데이터가 조회된 결과로 테이블 만들쟈~

- 설정된 constraints(제약사항)은 user_constraints 테이블에서 확인할 수 있음

**user_constraints 도 data dictionary의 일종, dic objects

> 전체 제약사항 조회 : select * from user_constraints

 

 

2) insert subquery

- 다른 테이블에 존재하는 (또는 같은 테이블의 다른 컬럼) 값으로 insert 작업을 할 때

- 단수행 subquery와 복수행 subquery 사용 가능

   > 복수행 subquery는 정산작업할 때 많이 사용함

- select로 조회하는 컬럼명과 insert하는 컬럼명이 동일해야 함!

- 단수행 문법) insert into 테이블명 (컬럼명,,,) values ( (select,,,) ,,, )

  > 컬럼 하나에 레코드 하나 나와야 함 // Ex) select sal from emp where empno=7788

- 복수행 문법) insert into 테이블명 (컬럼명,,,) (select,,,)

  > 컬럼 여러 개에 레코드 여러 개 나올수있음

 

 

3) update subquery

- 다른 테이블의 컬럼 값으로 현재 테이블의 값을 변경해야 할 때

- 단수행 서브쿼리만 가능 (복수행 서브쿼리 사용하려면 in 씀)

   > =은 하나의 값만 비교할 수 있으므로 in 써야 함

- 문법) update 테이블명 set 컬럼명 = (select,,,) where 컬럼명 = (select,,,)

- update subquery에서 복수행 subquery를 사용하려면 in을 사용하면 됨

  > where empno in (select empno from emp where ename = 'ADAMS' or ename = 'SCOTT');

      또는 where empno in (select empno from emp where ename in ('ADAMS', 'SCOTT'));

 

 

4) delete subquery

- 단수행 서브쿼리만 가능 (복수행 서브쿼리 사용하려면 in 씀)

- 다른 테이블의 컬럼 값으로 현재 테이블의 레코드를 삭제해야 할 때

- 문법) delete from 테이블명 where 컬럼명 = (select,,,)

 

 

5) select subquery

- 단수행 서브쿼리와 복수행 서브쿼리 모두 사용 가능

- 단수행 서브쿼리 : 다른 테이블의 컬럼 값으로 조회, Scalar subquery 사용하여 조회

 

** Scalar subquery

- 조회되는 컬럼의 subquery를 사용하는 것

- 조회된 컬럼의 값으로 다른 테이블에서 조회하는 서브쿼리

- 서브쿼리만 별도로 실행될 수 없음

- 복수행 서브쿼리 : inline view를 사용한 조회 (조회된 결과를 가지고 재조회하는 것)

- 단수행 문법) select 컬럼명 from 테이블명 where 컬럼명 = (select)

- Scalar subquery 문법)

    select 컬럼명, (select 컬럼명 from 테이블명 where 테이블명.컬럼명 = 테이블명.컬럼명) from 테이블명

     >> Scalar 서브쿼리 안에서는 from에 있는 테이블명.컬럼명 먼저 써주고 나중에 전체 테이블명

- 복수행 문법)

• where절 in : 여러 개의 값에 해당하는 레코드를 조회할 때
    > 문법) where in ( select … )
• inline view를 사용한 조회 : 조회된 결과를 가지고 재조회할 때 사용
    > 문법) select 컬럼명 from (select ,,, ) where group by ~ 주고싶은거 다 주면 됨
    > 실제 테이블이 아닌 서브쿼리의 검색 결과(inline view)를 사용한 조회
    > 바깥 컬럼명은 실제 컬럼이 아닌 inline view에 의해 조회된 컬럼명을 사용해야 함 (또는 alias)
   

* inline view : select 쿼리에 의해 조회된 결과로 생성된 테이블
 - 바깥 쿼리문은 inline view를 사용한 검색을 수행하므로 사용할 수 있는 컬럼은

    inline view에 나오는 컬럼만 사용할 수 있음 (컬럼명 동일해야 함, alias면 alias로 조회)

- inline view를 통해 재조회할 때, 안쪽 서브 쿼리에서 조회한 컬럼을 모두 사용할 필요는 없음

- inline view는 같은 쿼리 안에서 inline view를 중복하여 사용할 수 있음

 

---

# 오늘의 코딩 #

- select subquery를 해보자!

 

#단수행 subquery

-- 부서의 지역이 'DALLAS'인 부서에 근무하는 사원정보 조회
-- 조회컬럼 : 사원번호, 사원명, 부서번호, 연봉, 직무
select empno, ename, deptno, sal, job
from emp
where deptno = (select deptno from dept where LOC = 'DALLAS');

#출력결과#

-> where절의 select subquery의 연산결과로 추출된 deptno을 비교하여 필요한 정보가 출력된다!

---

#Scalar subquery

-- 사원테이블에서 사원번호, 사원명, 부서번호, 부서명 조회
select empno, ename, deptno,
       (select dname from dept where dept.deptno=emp.deptno) dename
from   emp;

#출력결과#

-> 사원 테이블에는 부서명이 없으나, 부서 테이블과 사원 테이블의 부서번호를 매칭하여 

일치하는 부서번호에 해당하는 부서명을 부서테이블에서 가져온당

---

#복수행 subquery

-- 사원테이블에서 부서의 인원수가 4명 이상인 부서의 사원정보 조회
-- 조회컬럼 : 사원번호, 사원명, 부서번호, 부서명, 연봉, 입사일
select empno, ename, deptno,
       (select dname from dept where dept.deptno=emp.deptno) dname
       , sal, hiredate
from   emp
where  deptno in (select deptno
			from emp
			group by deptno
			having count(empno) >= 4);

#출력결과#

-> where절에서 도출된 deptno은 복수이므로, in을 사용하여 복수행 subquery를 수행했다!