Oracle SQL - Sub Query 서브 쿼리

서브쿼리.sql.html

0.01MB

 

SQL : 05. Sub Query (서브 쿼리)

1. Sub Query 란?

문법 : select select_list from table 또는 view where 조건 연산자 (select select_list from table where 조건);

- Sub Query란 쿼리안에 또 다른 쿼리가 담겨 있는 것을 말한다.

- 괄호 안에 있는 쿼리를 sub query 또는 Inner Query라고 부르고, 나머지 괄호 밖에 있는 쿼리를 Main Query 또는 Outer Query라고 부른다.

 

사용 이유

• 서브쿼리란 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문.
• 서브쿼리를 쓰는 이유는 알려지지 않은 기준을 이용한 검색을 하기 위해서이다.
• * 서브쿼리는 메인쿼리의 컬럼을 모두 사용할 수 있지만 메인쿼리는 서브쿼리의 컬럼사용 못함.
• 서브쿼리를 사용해야할 때 조인을 사용하는 실수를 할 수 있다.

 

---

 

 

-- 이문세의 부서명 검색
--1. 이문세 deptno 찾기 emp
select * from emp where ename='이문세';

 

 

--2. dpetno가 10이니까 부서명
select dname from dept where deptno=10;

 

-- 1번과 2번 한번에 합쳐서 같이 쓰기
select dname from dept where deptno = ( select deptno from emp where ename='이문세');

 

---

-- 평균 급여 구하기

-- 평균 급여 구하기
select avg(sal) from emp;

-- 문제 평균급여보다 더 많은 급여를 받는 사원은 사원명, 급여

-- 문제 평균급여보다 더 많은 급여를 받는 사원은 사원명, 급여
select ename, sal from emp where sal > 463.5;

 

Sub Query로 작성하기

select ename, sal from emp where sal > (select avg(sal) from emp);

 

 

---

문제2 : Student 테이블과 department 테이블을 이용하여

'Anthony Hopkins' 학생과 전공1이 동일한 학생들의 이름과 전공1 이름을 출력하라.

select deptno1 from STUDENT where name='Anthony Hopkins';

위 절은 단일행 서브쿼리이다.

 

학생 이름의 전공 번호를 확인한다.

select s.name as 학생이름, d.dname as 전공명
from student s, department d
where s.deptno1 = d.DEPTNO
and deptno1 = ( select deptno1 from student where name = 'Anthony Hopkins');

 

---

다중행 서브 쿼리

다중 행 서브 쿼리는 서브 쿼리에서 반환되는 결과가 하나 이상의 행일 때 사용하는 서브 쿼리입니다. 다중 행 서브 쿼리는 반드시 다중 행 연산자(Multiple Row Operator)와 함께 사용해야 합니다.

 

---

 

IN 연산자

IN 연산자는 메인 쿼리의 비교 조건에서 서브 쿼리의 출력 결과와 하나라도 일치하면 메인 쿼리의 WHERE 절의 참이 되도록 하는 연산자입니다.

연산자	의미
IN	같이 값을 찾음

예제 : 정보 미디어학부(부서번호:100)에 소속된 모든 학생의 학번, 이름, 학과번호를 출력하여라.

 

예제 -- 급여가 500을 초과하는 사원과 같은 부서에 근무하는 사원 구하기 

(뭔가 문제가 잘못된 느낌, 500을 초과하는 사원이 한둘이아니라서 여러 부서가 나옴)

select ename, sal, deptno from emp
where DEPTNO in ( select distinct deptno
                from emp where sal > 500 );

위 SQL문을 해석해보면

서브쿼리를 실행하면 2개의 결과가 나오며 이 두 개의 결과값이 IN 연산자를 통해 메인 쿼리에게 전달된다.

 

 

ALL 연산자

ALL 조건은 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 모든 값이 일치하면 참입니다. 찾아진 값에 대해서 AND 연산을 해서 모두 참이면 참이 되는 셈이 됩니다. > ALL 은 “모든 비교값 보다 크냐”고 묻는 것이 되므로 최대값보다 더 크면 참이 됩니다.

연산자	의미
<ALL	최소값을 반환함
>ALL	최대값을 반환함

 

예제 -- 30번 부서의 최대 급여보다 많은 급여를 받는 사원 출력하기
 

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

 

 

ANY 연산자

ANY 조건은 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 하나 이상만 일치하면 참입니다. > ANY는 찾아진 값에 대해서 하나라도 크면 참이 되는 셈이 됩니다. 그러므로 찾아진 값 중에서 가장 작은 값 즉, 최소값 보다 크면 참이 됩니다.

연산자	의미
>ANY	최소값을 반환함
<ANY	최대값을 반환함

 

예제 -- 30번 부서의 최소 급여보다 많은 급여를 받는 사원 출력하기

-- 30번 부서의 최소 급여보다 많은 급여를 받는 사원 출력하기
select ename, sal
from emp
where sal > ANY ( select sal
                    from emp
                    where deptno = 30 );

 

 

EXISTS 연산자

EXISTS 연산자는 서브 쿼리문에서 주로 사용하며 서브 쿼리의 결과 값이 참이 나오기만 하면 바로 메인 쿼리의 결과 값을 리턴합니다. 서브 쿼리의 결과 값이 존재하지 않는다면 메인 쿼리의 어떤 값도 리턴되지 않는 문장입니다.

쿼리 속도 면에서는 서브 쿼리 사용 시 IN 보다는 EXISTS가 훨씬 빠릅니다. EXISTS의 반대말로 NOT EXISTS도 사용 가능합니다.

연산자	의미
EXIST	Sub Query에 값이 있을 경우 반환함

 

 기능 살펴보기

select * from dept where EXISTS( select *
                                from emp where deptno = 10);

 

---

서브쿼리를 이용한 테이블 생성, 데이터 조작

서브 쿼리로 테이블 생성하기

CREATE TABLE 문에서 서브 쿼리를 사용하여 이미 존재하는 테이블과 동일한 구조와 내용을 갖는 새로운 테이블을 생성할 수 있습니다.

create table emp02
as
select * from emp;

원하는 행으로 구성된 복제 테이블 생성하기

create table emp05
as
select * from emp
where deptno=10;

 

테이블 구조만 복사하기

create table emp06
as
select * from emp where 1=0;

where 1=0; 조건은 항상 거짓입니다. 이를 이용하여 테이블의 데이터는 가져오지 않고 구조만 복사하게 됩니다.

 

서브 쿼리로 데이터 삽입하기

insert into dept01
select * from dept;

 

서브쿼리를 이용한 데이터 변경

테이블 내용 변경하기 

update dept01
set loc = ( select loc 
			from dept01
            where dpetno = 40 )
where deptno = 20;

 

테이블 내용 살피기

select * from dept01;

 

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

delete from emp02
where deptno = ( select deptno
				from dept
                where dname = '영업부' );