(DB)SUB QUERY (서브쿼리)

EMP 테이블의 특정 누구보다 급여를 많이 받는사람 혹은 SMITH 보다 급여를 적게 받는 사람은 누구 일까 라는 질문이 

오면 특정 누구의 급여나 SMITH의 급여를 먼저 파악한후 결과를 조회 할 수있었다. 먼저 급여를 파악하고 그값에 조건을 걸어 결과를 한번더 조회 해야 했다. 이러면 서버에 왔다갔다가를 두번이나 해서 가독성이 떨어진다.

 

A가 B에게  물건 C의 가격을 물어보면 B는 C의 판매가게에 찾아가 가격을 물어보고 A에게 알려주고 A는 가격을 알았으니 돈을 주며 물건C를 B에게 사와달라고 부탁한다.  SQL에는 이런 형태가 참많은데 이런문제를 해결해 주는 것이 서브쿼리다.

 

서브쿼리를 괄호를 감싸서 사용

단일 행 혹은 복수 행 비교 연산자와 같이 사용 가능

ORDER BY는 사용할 수 없다. 

 

문법은 

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

 

MEP 테이블에서 WARD 보다 COMM을 적게 받는 사람의 이름과 COMM을 출력하세요.

 

SELECT ENAME , COMM 
FROM EMP
WHERE COMM <(
SELECT
COMM
FROM EMP 
WHERE ENAME ='WARD'  
)

지금 3번째줄 WHERE 절 부터 괄호가 쳐져있는 부분을 SUB QUERY라고한다.

실행순서는 서브 쿼리가 먼저 실행되어 메인 쿼리에 결과값을 준다. 메인쿼리는 서브쿼리의 값을 받아 실행하여 최종값을 출력한다

 

서브쿼리 -> 메인쿼리 -> 출력

 

 

단일행 서브 쿼리

 

단일행 서브쿼리란 서브쿼리의 수행 결과 값이 하나의 행으로 나오는 서브쿼리이다.

가장많이 쓰이며 서브쿼리를 수행한 결과가 1건만 나오고 이결과를 MAIN QUERY로 전달해  MAIN QUERY가 수행한다

결과가 하나이므로 단일행 연산자를 사용한다

 

연산자	의미
=	같다
<>	같지않다
>	크다
<	작다
>=	크거나 같다
<=	작거나 같다

 

STUDENT 테이블과  DEPARTMENT 테이블을 사용하여 'Anthony Hopkins' 학생과 1전공이 동일한 학생들의 이름과

1전공이름을 출력하라.

 

 

SELECT S.NAME ,D.DNAME 
FROM STUDENT S , department D
WHERE s.deptno1 = d.deptno 
AND s.deptno1= (
SELECT deptno1 FROM STUDENT
WHERE NAME ='Anthony Hopkins'
);

이거 푸는데 머리가 아프다 . 해석해보자

우리가 생각해야할건 안토니 학생의 1 전공번호를 먼저구해야한다 (앞서 서브쿼리가 먼저 읽힌다라는 것을 설명함)

그다음 그 전공번호와 같은 학생들의 이름과 전공번호가 어느학과인지 서로의 테이블에 조인해주면된다

 

아까 서브쿼리를 읽고 메인 쿼리에 값을 주어 메인쿼리가 최종적으로 값을 뽑아낸다라고 했다

그럼 () 서브쿼리구문에는 안토니 호킨스 학생을 뽑아오는 쿼리문을 작성한다 그럼 기준은안토니 학생의 기준으로 

메인쿼리에서 두테이블을  DEPTNO 로 관계를 이어준다 두테이블의 관계가 이어졌으니 STUDENT 테이블의 학생들은

DEPARTMENT의 전공을 개개인씩 가지게되고 마지막으로 서브쿼리의 조건과 위조인과의 관계만 이어주면된다.

이문제는 서브쿼리 뿐만아니라 JOIN의 개념을 이해해야지만 풀수 있는 문제다

 

 

그럼 다음문제를 보자

Professor 테이블과 department 테이블을 조회하여 'Meg Ryan' 교수보다 나중에 입사한 사람의

이름과 입사일, 학과명을 출력하라.

SELECT
P.NAME, P.HIREDATE ,D.DNAME
FROM  professor P , department D
WHERE  p.deptno = d.deptno
AND p.hiredate >(
SELECT hiredate
FROM Professor 
WHERE NAME='Meg Ryan'
);

앞에 문제와 똑같다 바뀐건 단일행 연산자만 바뀌었을 뿐이다.

그리고 우리는 나중에  입사한 사람이므로 HIREDATE 로 비교를 해야한다

 

다중행 SUB QUERY 

다중행 서브쿼리란 결과가 두개이상 출력 되는것 의미하는 서브쿼리이다 

당연히 다중연산이므로 단일 연산자는 사용할 수없다.

그렇기 때문에 다중행 서브쿼리 같은경우는 별도의 연산자가 존재한다.

 

 

연산자	의미
IN	서브쿼리 결과가 같은 값을 찾는다.
EXISTS	서브쿼리의 값이 있을경우 메인 쿼리를 수행한다
>ANY	서브쿼리 결과중에서 최솟값을 반환
<ANY	서브쿼리 결과중에서 최댓값을 반환
<ALL	서브쿼리 결과중에서 최솟값을 반환
>ALL	서브쿼리 결과중에서 최댓값을 반환

 

위의 ANY와 ALL은 연산자의 방향에 따라 최댓값, 최솟값이 달라진다

 

서브쿼리에서 반환되는 값은 최솟값이거나 최댓값이지만 연산자 좌측에는 어떤 값이 오는가에 따라서 출력되는 결괏값은 다르게 나온다.

예를 들자면 SUB QUERY 결과값이 (100,200,300)으로 나왔을 경우에 

SAL >ANY (100,200,300)으로 되면 ANY 자리에 최솟값인 100을 반환한다

그러면 SAL >100 이되는것이다 

 

그런데 만약 SAL< ALL (100,200,300) 이되면 서브쿼리 의 최솟값인 100이 반환되지만 식은 SAL<100 되어버린다.

즉 , SUB QUERY의 반환 값은 둘다 최솟값인 100을 반환하지만 연산자의 방향에 따라 다른식이 되어버리므로 주

의하여야한다.

 

그럼 예제를 보며 파악해보자

 

EMP2 테이블과 DEP2 테이블을 참조하여 근무지역(DEP2 테이블의 AREA  컬럼)이 'POHANG MAIN OFFICE'인 모든사원

들의 사번과 이름 , 부서 번호를 출력하라.

SELECT E.EMPNO, E.NAME ,e.deptno 
FROM EMP2 E , DEPT2 D
WHERE  e.deptno = d.dcode
AND d.area IN(
SELECT area
FROM DEPT2 
WHERE area ='Pohang Main Office'
);
본인 쿼리문

SELECT EMPNO ,NAME ,DEPTNO
FROM EMP2 
WHERE DEPTNO IN (SELECT DCODE 
FROM DEPT2 
WHERE AREA ='Pohang Main Office'
)

예제

일단 조건이 1명이 아닌 다수기 때문에 다중행을 사용해야한다.그리고 저렇게 조인없이 IN으로 서브쿼리 조건을 찾을 수있다.

 

EXISTS 연산자 사용하기

EXISTS 는 "존재하면" 이라는 의미가 있다. 

이연산자는 SUB QUERY의 내용을 먼저 수행해서 그결과가 1건이라도 나오면 메인쿼리를 수행하고 만약 SUB QUERY의 내용이 한 건도 나오지 않으면 메인 쿼리를 아예 실행하지 않는다.

 

IN 과의 차이점은 IN연산자는 서브쿼리에 검색된조건만 메인에서 연산을 하지만 EXISTS는 서브쿼리의 결과 그딴거 상관없이 서브쿼리가 실행만 되면 메인이 동작한다.

 

 

SELECT *FROM DEPT WHERE EXISTS(SELECT
DEPTNO
FROM DEPT

WHERE deptno =&DNO
) ;

 

위 실행결과는다음과같다

위쿼리문은 DEPTNO가 20번 입력받은후 EXISTS연산자를 사용해 해당번호가 존재하는 검사를 한후 메인 쿼리를 수행한

화면이다.

 

SELECT *FROM DEPT WHERE DEPTNO IN(SELECT
DEPTNO
FROM DEPT

WHERE deptno =&DNO
) ;

 

위결과 값은 DEPTNO가 20번 입력받은후 DEPTNO가 20번인 부서만 출력했다.

 

차이가 무엇인가???

 

공항검색대를 빗대어 표현하겠다.

만식이는 친구들과 같이 제주도여행을 가게되었다. 공항검색대앞에 서서 검문을 받게 되었다. 최초로 만식이가 검문을 받게 되었다. 당연히 만식이는 불필요한 물품을 가지고 있지않아 공항검색대에 통과했다. 검색대원들은  만식이 그룹이

안전하다고 판단했는지 친구들을 검문하지않고 검색대에 통과시켰다. 

자 이게 EXISTS다. 만식이= 안전 이라는 조건이 있으면 그에관한 그룹을 전부 공항요원들이(MAIN) 통과(출력)시킨다.

 

그럼IN의 조건이다

위조건과 같다 만식이는 친구들과여행을 가서 검문을 받게 되었다 만식이는 통과  어라?? 근데 친구놈들이 공항대에서 

화기물품이나 위험한물건들을 들고와버렸다. 이런 만식이 친구들은 공항대에 걸려서 여행을 갈수 없게 되었다.

하는 수없이 만식이만 혼자서 제주도 여행을 재밌게 다녀오게 되었다.

이게 IN이다 차이가 이제 확 느껴지지않나?? 

IN은 만식이가 검문을 받고 (서브쿼리가 동작을하고) 친구그룹이 안전하지않다면(서브쿼리 조건에 맞지않는 것들은 출력하지않고) 만식이만 여행을 가게 된다(MAIN에서 서브쿼리조건을 받아 실행한다)

 

 

다중 컬럼 서브쿼리 (MULTI COLUMN SUB QUERY) 

다중 컬럼 서브쿼리란 서브쿼리의 결과가 여러 컬럼인 경우를 말한다. 주로

PRIMARY KEY를  여러컬럼을 합쳐서 만들었을 경우 한꺼번에 비교하기 위해 자주 사용한다.

 

STUDENT 테이블을 조회하여 각학년별로 최대 몸무게를 가진 학생들의 학년과 이름과 몸무게를 출력하라.

 

SELECT NAME ,GRADE ,WEIGHT
FROM STUDENT
WHERE (GRADE ,WEIGHT) IN (SELECT GRADE , MAX(WEIGHT) FROM STUDENT GROUP BY GRADE);

위 결과를 보면 서브쿼리 부분에서 학년별로 최대 몸무게를 구한후 한핵씩 메인 쿼리로 보내줘 메인쿼리를 수행한후

그조건에 맞는 행을 출력한것이다.

여기서 설명하고자하는것은 서브쿼리가 두개의 컬럼을 동시에 메인으로 넘겨서 비교한다는것이다.

 

상호연관 SUB QUERY 

상호연관 서브쿼리란 메인 서브쿼리값을 서브쿼리에 주고 서브쿼리를 수행한후 그결를 다시 메인으로 반한해서 수행하는 서브쿼리이다. 즉 서브쿼리와 메인가 데이터를 주고 받는 형태인데 가독성이 아주떨어지는 형태로써 주의하여야한다.

 

 

 

EMP2 테이블을 조회해서 직원들중 자신의 직급의 평균연봉과 같거나 많이 받는 사람들의 이름과 직급 현재 연봉을 출력하라

 

SELECT NAME , POSITION ,PAY
FROM EMP2 A
WHERE PAY>= (SELECT AVG(PAY) FROM EMP2 B 
WHERE A.POSITION =B.POSITION);

위쿼리는 메인 쿼리를 먼저 수행해서 직급을 구한다음 서브쿼리에 전달해주고 

그값을 받은 서브쿼리가 수행되어 결과를 다시 메인쿼리로 전달해준다

그후 다시 받은값을 가지고 메인쿼리가 최종적으로 수행되는 형태이다

 

 

스칼라 서브쿼리 SCALAR SUB QUERY 

SELECT 절에 오는 서브쿼리로 한번에 결과를 1행씩반환한다. 이번에 보는 스칼라 서브쿼리는 내용이 어렵지만 실전에 아주많이 사용되고 SQL 튜닝에도 강조되는 부분이니 확실하게 이해하고 가면 좋을것이다.

 

emp2 테이블과 dept2 테이블을 조회하여 사원들의 이름과 부서 이름을 출력하라

SELECT NAME,( SELECT dname 
FROM  dept2 d 
where  e.deptno =d.dcode

) from emp2 e;

위와 같이 출력을 하려면 원래는 조인을 사용하여야한다 하지만 이렇게 스칼라서브쿼리를 이용하여 출력을 할수도 있다

스칼라 서브쿼리는 서브쿼리의 결과가 없을 경우 null을 돌려준다.

그래서 스칼라 서브쿼리는 outter join과 동일하다.

 

스칼라 서브쿼리의 주의점은 

두건이상의 데이터반환을 요청한경우 에러가 발생한다.

두개이상의 컬럼을 조회할경우에도 에러가 발생한다.

스칼라 서브쿼리는 일반적으로 데이터의 종류나 양이적은 코드성격의 테이블에서 데이터를 가져올경우 join 보다 좋지만 많은 데이터를 가져올때는 join이 더 용이하다.

(이부분의 대해서는 추가적인 설명으로 글을 갱신하겠다.)

 

WITH 절을 이용한 서브쿼리

이번내용은 오라클 9I 버전부터 지원되는 방법으로 WITH절을 사용하여 원하는 테이블을 VIEW 처럼 가상의 테이블로

생성시킨후 데이터를 가져오는 기법이다.

성능이 좋아 현업에서 아주 많이 사용되지만 사용법이 어렵다는 단점이 있다.

일단 이부분도 개념을 파악하고 기본을 이해한뒤 추가적으로 내용을 갱신하려고한다.

 

기본문법은

 

// 단일 가상 테이블 생성
WITH A AS
(

SELECT QUERY

)
SELECT *FROM A;


// 다중 가상테이블 생성 
WITH A AS
(
SELECT QUERY

),
B AS
(
SELECT QUERY

)
SELECT *FROM A
UNION ALL
SELECT *FROM B;

이렇게 된다.

이방법을 사용할때는 몇 가지 주의사항이 있다.

WITH 절안에는 SELECT 문장만 쓸수 있다.

WITH 절안에는WITH 절을 사용할 수없다.

 

 

FROM (SELECT
    *
FROM EMP WHERE DEPTNO= 20) E,
(SELECT
    *
FROM DEPT)D
WHERE E.DEPTNO =D.DEPTNO;
// 이 서브쿼리를

with 
e as(SELECT *from emp where deptno=20),
d as (SELECT *from dept)
SELECT d.dname ,e.empno,e.ename,e.deptno,d.loc
from e,d
WHERE e.deptno = d.deptno;
// WITH 절을 이용하여 가독성을 높임

 

자세한 비교를 위해선 많은 데이터를 한꺼번에 처리해야함 (이번엔 개념설명만 하기 위해 )