Oracle SQL 강좌
Multiple-Row Subquery 20 30 99,999+

by 김정식 IN NOT IN ANY ALL EXISTS 다중 행 서브쿼리 서브쿼리 Subquery Multiple-Row Subquery [2002.08.30]


Multiple-Row Subquery란?

- 하나 이상의 행을 반환하는 Subquery이다

- 단일 행 연산자를 사용하지 못하며, 다중 행 연산자(IN, NOT IN, ANY, ALL, EXISTS)만 사용이 가능하다.

IN 연산자
  • - IN 연산자는 하나의 컬럼이 여러개의 '=' 조건을 가지는 경우에 사용
  • - OR는 IN을 포함한다. IN을 사용해 표현할 수 있는 것은 당연히 OR로 표현할 수 있다.
  • - 하지만 OR로 표한한 것은 IN으로 표현하지 못할때가 있다. (OR에서 LIKE같은 연산자를 사용한 경우)
  • - IN은 반드시 하나의 컬럼이 비교되어야 하므로 나중에 인덱스 구성에 대한 전략을 수립할 때 유리하다.
  • - 그러므로 가능한 OR보다는 IN 을 사용하는 것이 좋다.

 
-- 부서별로 가장 급여를 많이 받는 사원의 정보를 출력하는 예제
SELECT empno,ename,sal,deptno  
  FROM emp
 WHERE sal IN (SELECT MAX(sal)
                 FROM emp
                GROUP BY deptno);

EMPNO ENAME         SAL     DEPTNO
----- --------  --------- ---------
 7698 BLAKE        2850        30
 7788 SCOTT        3000        20
 7902 FORD         3000        20

ANY 연산자

- ANY 연산자는 Subquery의 여러 결과값 중 어느 하나의 값만 만족이 되면 행을 반환 한다.

 
-- SALESMAN 직업의 급여보다 많이 받는 사원의 사원명과 급여 정보를 출력하는 예제
SELECT ename, sal
  FROM emp
 WHERE deptno != 20
   AND sal > ANY (SELECT sal 
                    FROM emp 
                   WHERE job='SALESMAN');

ENAME             SAL
---------- ----------
ALLEN            1600
BLAKE            2850
CLARK            2450
...

ALL 연산자

- ALL 연산자는 Subquery의 여러 결과값 중 모든 결과 값을 만족해야 행을 반환 한다.

 
-- 모든 SALESMAN직업의 급여보다 많이받는 사원의 사원명과 급여정보를 출력하는예제
SELECT ename, sal
  FROM emp
 WHERE deptno != 20
   AND sal > ALL (SELECT sal 
                    FROM emp 
                   WHERE job='SALESMAN');

ENAME             SAL
---------- --------
CLARK            2450
BLAKE            2850
KING             5000

EXISTS 연산자
  • - EXISTS 연산자는 Subquery 데이터가 존재하는가를 체크해 존재 여부(TRUE,FALSE)를 결과로 반환한다.
  • - EXISTS절에는 반드시 메인 쿼리와 연결이 되는 조인 조건을 가지고 있어야 한다.
  • - subquery에서 결과 행을 찾으면, inner query 수행을 중단하고 TRUE를 반환한다.
 
-- 아래 예처럼 emp 테이블을 통해 사원들이 속한 부서번호의 정보만 조회하는 경우
-- 추출하고자 하는 대상은 dept 테이블이지만 emp 테이블과 조인하여 부서번호를 
-- 체크해야 한다.
-- 두 테이블의 관계가 1 : M 이므로 불필요하게 EMP 테이블을 모두 액세스하고 
-- DISTINCT로 중복 제거를 한다.
SELECT DISTINCT d.deptno, d.dname
  FROM dept d, emp e
 WHERE d.deptno = e.deptno;



-- EXISTS를 사용하는 Subquery로 변경
-- 추출하고자 하는 대상만을 FROM절에 놓고 emp테이블은 체크만 하기위해 
-- EXISTS절에 위치시켰으며 이로 인해 수행속도가 대폭 감소하게 된다.
SELECT d.deptno, d.dname
  FROM dept d
 WHERE EXISTS 
      (SELECT 1
         FROM emp e
        WHERE e.deptno = d.deptno);

- 강좌 URL : http://www.gurubee.net/lecture/1503

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by goguy [2004.05.08 22:14:19]
SELECT empno,ename,sal,deptno
FROM emp
WHERE sal IN (SELECT MAX(sal)
FROM emp
GROUP BY deptno);
분석입니다.
1.SELECT
2.FROM
3.WHERE IN 3-1.SELECT
3-2.FROM
3-3 WHERE
서브퀴리입니다.
실행이 먼저 어디서 시작될까여 서브쿼리 부터 일까여 아니면 메인쿼리부터 일까여

>> 서브퀴리가 먼저라면
3-2. 의 테이블이 메모리에 적재되고요
3-3. GROUP BY절에 의해서 테이블이 재구축되고여
3-1. 의 그룹함수에 의해서 각 그룹의 값이 표시 되여 값(테이블)을 만들고여
2.에 의해 테이블이 메모리에 적재되고여
3.에 의해 IN 연산자가 3-1에 테이블의 값과 계산되서
1.에 의해 데이터들이 뿌려집니다.


>> 메인퀴리가 먼저라면
2.에 의해 테이블이 메모리에 적재되고여
3.에 IN 연산자에 의해 조건이 수행되고
3-2 테이블이 메모리 적재되고
3-3 GROUP BY절에 의해
3-1에 테이블(값)이 만들어 지고
1.의해 값들이 수행됩니다.

두개를 비교해보세요

SQL 문장만들기
부서별로 가장 급여를 많이 받는 사원의 정보를 출력하라 (하나의 테이블)

키포인트'"부서별로 가장 급여"를 많이 받는 사원'
부서별로 가장 많은 급여: GROUP BY절 사용을 사용해서 "SELECT MAX(봉급) FROM emp GROUP BY 부서;--->>SUB"
그 다음은 사원의 정보입니다
WHERE 절에 IN 연산자를 사용해서 정보를 출력하면 되겠습니다.
"SELECT 사원번호, 봉급, 부서 FORM emp WHERE 봉급 IN(SUB);

by 궁금이 [2004.07.20 18:03:10]
말씀하신 서브쿼리, 메인쿼리의 순서가 다를때 결과값이 차이도 생기는건가요.
이해가 잘 안돼서리.. 설명 부탁드립니다.

by goguy [2004.07.21 11:29:20]
테이블을 하나의 집합이라고 생각하시면 됩니다.
우선 from 절 테이블 emp 를 집합 a 이라고 생각하고요..
집합a는 emp의 데이타들이겠죠..
where 절에 in 연산자에 서브쿼리를 집합 b 이고요..
집합b는 부서별로 최대값의 sal값들이 원소로 존재하고요..


결론적으로 where 절에 in연산자를 사용해서 집합 b에 존재하는 값들을 집합 a와 비교해서
select 합니다.

집합 a 와 집합 b 의 관계를 조건(where)의 연산자(in)를 이용해서 관계를 정립되여..
화면(select)에 보여줍니다.

by EXISTS [2004.08.12 20:37:07]
EXISTS 연산자 이해 불가함다....
점더 쉬운 예 옵나용.
부탁함다.

by goguy [2004.10.29 14:39:12]
말그대로 exists의 서브쿼리에 데이타가 존재하면 그 값(조건)에 해당하는 테이블의 데이타를 출력하라는 뜻입니다.
복잡한 느낌을 간단하게 생각하면 될것 같습니다.

by 행인 [2005.01.13 16:39:35]
운영자님..위 IN 연산자 사용 예제는
부서별 최고 급여를 받는 사원 정보가 아니라
부서별 최고 급여와 같은 급여를 받는 사원(부서는 같든 틀리든 상관없음) 정보라고
생각되어지는군요..

by 헤깔려 [2005.05.08 19:38:19]
행인님이 올린 글때문에 더 헤깔려요~~

by 가로등 [2005.10.27 12:40:46]
음...그렇네요...부서별 최고 급여자 리스트를 뽑아내는 쿼리가 아니라 부서별 최고 급여자와 같은 급여를 받는 직원들의 리스트이네요...
머 예제를 위한 쿼리 이지만, 실제로는 저런 쿼리는 안쓰이겠죠..^^;;;

by smile [2005.11.09 20:22:54]
퍼갑니다. ^^

by 갸우뚱 [2005.11.17 11:41:46]
exists 와 in 의 차이를 잘 모르겠어요. exists 조건만족시 조건에 해당하는 것들을 출력, in의 조건에 해당하는 값들을 출력,, 같은 의미 아닌가요?

by 허륜 [2006.03.17 17:05:49]
exists 는 해당하는 값이 하나라도 존재하면 바로 만족합니다..그러나, in 은 주어진 값들을 모두 찾아내지요...

by 시근땀 [2006.03.20 15:34:00]
오히려 위 구문에서 MAX(), MIN() 을 쓸 수 있게 되었더라면

덜 헷갈렸을 것 같은데요.

sal > ANY(SELECT sal FROM emp WHERE job='SALESMAN') 이 구문은

'최소 어떤 한놈의 셀즈맨 월급보다 많기만 해봐라~' 이런 뜻이잖아요.

그러니까 제일 적게 받는 셀즈맨 월급보다 많기만 하면 되는거죠. 그쵸?

그래서 sal > MIN(SELECT sal FROM emp WHERE job='SALESMAN') 이
렇게 하면

될 줄 알았는데 안되더라구요.

AND 대신 MAX 를 써봤는데 그것도 안되고...

너무 서운했어요.

실망이에요. OTL JTO

by vic [2006.05.04 17:23:26]
sal > ANY(SELECT sal FROM emp WHERE job='SALESMAN')
== sal > (SELECT min(sal) FROM emp WHERE job='SALESMAN')

sal > ALL(SELECT sal FROM emp WHERE job='SALESMAN')
== sal > (SELECT max(sal) FROM emp WHERE job='SALESMAN')

by 파토파 [2006.07.12 17:40:38]
퍼갑니다...ㅋ

by 왕초보 [2006.11.20 11:14:35]
ㅠ,.ㅠ;
이번강의는 이해 못하고 넘어가네용...
너무 어렵당...
내만 어려운가...
IN, ANY, ALL, EXIST 고수님들 점더 쉽게 정리점 해주3... 플리즈

by 쥰 [2006.11.24 15:50:18]
헉..하루 고민했는데..이 강좌보고..update완료했습니다.
EXISTS.. 까맣게 잊고 있었네요. 오라클 십년넘었어도..거의 쓸일이 없어서..
정말 감사합니다.

by 지나가는놈 [2007.01.04 21:14:34]
in 이나 not in 은 함부로 쓰지 마시길.. 대략 디비에 3000건만 있어도 이거 까닥
잘못 썼다가는 애플리케이션 바로 얼어버리는군요.. 이런 건 테스트용으로 디비
10개쯤 넣고 돌릴 때나 쓰는 것이구요..

by 카투사 [2007.04.11 12:49:50]
위에 지나가는놈님의 말씀 공감합니다. 1만건 이상 데이터가 있으면 10분가량 걸립니다. 가급적이면 in 사용하지 마시고, 서브쿼리를 몇개 더 날려서 단일행연산자 (=)를 사용하시는 것이 빠릅니다...수고~

by 웅 [2007.04.27 09:42:32]
not in은 당연히 풀스캔 느리지만 in은 인덱스를 이용한 부분범위처리가 가능하지 않나요? 옵티마이징 팩터를 잘 부여하면 그렇게 나쁘진 않을 것 같습니다.

by Piona [2007.11.28 12:05:45]
비숫한 예제를 함 올려봅니다. 참고가 됐으면 하네요....
부서별로 최소 급여를 받는 사원의 정보출력

1)pairwise subquery로 구현
(부서이면서 최소 sal인것) 쌍으로 비교

SELECT deptno,ename,sal
FROM EMP
WHERE (deptno,sal) in (SELECT DEPTNO,MIN(sal) FROM EMP GROUP BY deptno);

2)상관서브쿼리로 구현

SELECT deptno, ename, sal
FROM EMP e
WHERE sal = (SELECT MIN(sal) FROM EMP WHERE deptno=e.deptno) --상관subquery :
;

by 미노스 [2008.10.05 23:16:51]
예제가 잘못된 것이 아닌가요?
부서별로 가장 급여를 많이 받는 사원의 정보를 출력하는 예제라고 되어 있는데.
위 예제는 어떤 사람이 타 부서 최고급여만큼 받아도 SELECT 가 되는게 아닌지요?
제 생각으로는, 어떤 사람이 속한 부서에서만 유효해야 하기 때문에 IN 예제로는 적합하지 않은 것 같습니다.
이게 그 문제에 적합한 솔루션일 거라 생각이 드는데..
SELECT e.empno, e.ename, e.sal, e.deptno
FROM emp e
WHERE e.sal = (SELECT MAX(ee.sal) FROM emp ee WHERE e.deptno = ee.deptno GROUP BY ee.deptno);
위 예제에서는
WHERE e.deptno = ee.deptno
이 부분이 없기 때문에, 올바른 결과를 얻어올 수 없다고 생각이 듭니다.
제가 잘못 이해한건가요? 만약 틀렸다면 지적해주세요;;

by IN예제 [2008.10.14 18:17:00]
위에 행인님 가로등님 IN예제 이상업는데요.

SELECT empno,ename,sal,deptno FROM emp
WHERE sal IN(SELECT MAX(sal) FROM emp
GROUP BY deptno);

sal 급여 , deptno 부서 인데
부서별로(10 20 30 부서코드겟죠)
최고 급여 뽑아낸 다음 (SELECT MAX(sal) FROM emp
GROUP BY deptno) <<이부분이겠죠.

그 최고급여에 해당하는거만 뽑는거네요
3000이 두개라서 헤갈리시는거 같은데 20이란 부서코드에 최고급여가
3000으로 두분이 있기때문에 결과가 20부서에만 2분 출력 된거라
생각되네요 ^^

by 첫걸음 [2009.01.01 18:06:02]
IN 예제 부서별로 가장 급여를 많이 받는 사원의 정보를 출력하는 쿼리 이거 IN
미노스님의 말이 많는거 같은데요 부서별로 가장 많은 SAL값을 가져와서 비교를 하지만 부서번호를 비교하는 부분이 없기 때문에 다른부서의 최대값과 같다면 같이 출력 된다는 ..

by ㅋㅋ [2009.05.11 13:42:41]
와~ 좋네요

by SHONG [2009.05.12 14:55:58]
가끔 where 절에 (a,b,c) in (select aa,bb,cc from abc ) 식으로 썼었는데,
앞으로는 쓰면서 한번 더 고려를 해봐야겠군요. ㄷㄷㄷ
그리고 EXISTS 좋네요. 써보니까. ㄷㄷㄷㄷ

by 빈이 [2009.12.30 14:23:07]
두번보니깐 이해대네 ㅎㅎ ALL, ANY 가 헷갈렸는데 ;

by 양한마리 [2010.02.03 10:46:55]
좋은 강좌입니다. ^ ^

by piki [2010.02.09 11:16:42]

by 정존 [2013.07.12 11:39:40]
EXISTS 어렵다..

by 주정래 [2014.11.14 13:11:56]

미노스님 말이 맞는거 같음요.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입