9. 서브 쿼리 : 단일 행 서브 쿼리
- 서브 쿼리는 두 개의 쿼리(질의 또는 SELECT 문)를 결합하여 하나의 문장으로 표현 하는 것. 그렇다고 새로운 문장의 형식이나 구문이 나오는 것은 아님. 이제까지의 SELECT 문을 기반으로 서브 쿼리가 구성. 서브 쿼리는 메인 쿼리 내에서 리터럴(문자)을 대신하는 것이므로 사용자가 직접 쿼리를 두 번 실행하는 것과 같이 응용으로 대체가 가능하지만 응용에 비해서 성능과 비용이 월등히 우수 또한 서브 쿼리는 조인과 더불어 여러 개의 테이블을 연결하는 등의 작업에 사용
서브 쿼리란 ?
서브 쿼리는 둘 이상의 SQL 문이 합쳐져서 만들어진 문장으로 보통은 서브 쿼리(Sub Query : 내부 질의)에 의해 검색된 결과 값이 SQL문에 필요한 경우 사용. 즉 서브 쿼리란 SQL문 내에 쓰인 SELECT 문을 의미, 이 때 SQL문은 메인 쿼리(Main Query : 외부 질의)라 불림. 서브 쿼리는 검색되는 결과의 유형에 따라 다음과 같이 나타남
* 단일 행 서브 쿼리
- 서브 쿼리가 하나의 컬럼에서 하나의 행을 검색
* 다중 행 서브 쿼리
- 서브 쿼리가 하나의 컬럼에서 여러 개의 행을 검색
* 다중 열 서브 쿼리
- 서브 쿼리가 여러 개의 컬럼을 검색
서브 쿼리는 WHERE절, HAVING절과 같이 조건 절에 주로 쓰이고 FROM 절에 쓰이는 경우도 있음. 특히 FROM절에 쓰인 서브 쿼리는 인라인 뷰(Inline View)라고 부름
> 단일 행 서브 쿼리
- 서브 쿼리에서 단 하나의 행과 열을 검색하는 SQL문. 검색된 결과 값을 조건문에 이용하는 경우 많이 사용
SQL> SELECT [DISTINCT | ALL] 컬럼, 컬럼..
2 FROM 테이블
3 WHERE 컬럼 단일_행_연산자 (SELECT 문장 : Sub query 문)
* 단일 행 연산자가 사용됨으로 반드시 서브 쿼리의 결과 값은 한 개만 검색되어야 함
* 서브 쿼리는 반드시 괄호로 묶음
* 서브 쿼리는 메인 쿼리 실행 전에 실행
* 서브 쿼리의 검색된 결과 값은 메인 쿼리에 사용
* 단일 행 연산자 오른쪽에 기술 (=, <. >. <=, >=, !=)
* WHERE 절에 기술된 열의 숫자와 타입은 SELECT 절과 1:1 대응 관계가 되어야 함
-예제1. 남궁연호보다 급여를 많이 받는 사원 검색
SQL>SELECT eno, ename
2 FROM emp
3 WHERE sal > (SELECT sal
4 FROM emp
5 WHERE ename = '남궁연호');
SQL 문에서 값이 들어갈 부분에 검색된 결과 값이 필요하다면 이를 서브 쿼리 문으로 해결 할 수 있음. 물론 이런 문제는 두 개의 SQL문을 따로 실행해도 됨.
서브 쿼리는 비록 단일 SQL문에 비해서 많이 느리겠지만 최종적인 성능에서는 응용 프로그램을 같이 사용하는 것보다 훨씬 효율적, 프로그램의 유지보수 측면에서도 훨씬 유리
-예제2. 김선유보다 급여를 많이 받는 사원을 검색
SQL>SELECT eno, ename
2 FROM emp
3 WHERE sal > (SELECT sal FROM emp WHERE ename = '김선유');
장애가 발생한 위의 쿼리는 기술 내용이나 구조에 틀린 부분이 없음. 그러나 문제는 서브 쿼리의 결과 값이 여러 개라는 데 있음. 사원 중에 김선유 라는 이름의 사원이 2명 존재하며, 이런 경우 서브 쿼리에 반환된 결과가 2개이기 때문에 단일 행 연산자를 이용한 WHERE 절은 에러를 발생 시킬 수 밖에 없음
서브쿼리를 작성하는 경우 이와 같이 서브 쿼리 문의 결과 값이 예측하기 어렵다면 단일 행서브 쿼리문 보다는 여러 결과 값을 수용 할 수 있는 다중 행 서브 쿼리 문으로 문장을 바꾸거나 또는 가능하다면 뒤에 나올 그룹 함수를 이용하는 것이 좋음
- 예측하기 힘든 단일 행 서브 쿼리를 수정하는 방법
1. '=' 연산자는 'IN' 연산자로 바꿈
2. 부등호 ('<', '>', '<=', '>=')는 any, all 연산자를 추가
3. Max(), Min()과 같은 그룹 함수를 사용
1,2 다중 행 서브 쿼리로 수정하는 방법 3은 최대 값이나 최소 값을 취해서 서브 쿼리의 결과 중 하나만 추출하는 방법, 그룹함수부분 참고
-예제3. 문시현과 부서가 동일한 업무를 하는 사원의 정보를 검색
SQL>SELECT eno, ename, dno, job
2 FROM emp
3 WHERE dno != (SELECT dno FROM emp WHERE ename = '문시현')
4 AND job = (SELECT job FROM emp WHERE ename = '문시현');
서브 쿼리는 문장 내에 필요하다면 여러 번 사용 할 수 있음. 메인 쿼리 내에 어디든 검색된 값이 필요하다면 사용 가능하기 때문서브 쿼리가 가장 많이 사용되는 곳이 WHERE절과 HAVING 절 HAVING 절에 대해서는 그룹 함수 부분을 참고
-예제4. 부산에서 근무하는 사원의 정보 검색
SQL>SELECT eno, ename
2 FROM emp
3 WHERE dno = (SELECT dno FROM dept WHERE loc = '부산');
SELECT 문을 작성하는 사람에 따라 조인 문장으로 작성하거나 서브 쿼리 문장으로 작성 가
능
SQL>SELECT eno, ename
2 FROM emp e, dept d
3 WHERE e.dno = d.dno
4 AND loc = '부산';
상당수의 SQL 예에서 조인과 서브 쿼리는 양 쪽 모두 기술하는 것이 가능한 경우가 많음정확히 경우를 나누어 어떤 경우는 서브 쿼리를 쓰고 어떤 경우는 조인을 쓰라고 결정해주기는 어려움. 대체로 SELECT 절에 출력 대상 컬럼이 모두 하나의 테이블로부터 검색된다면 가능한 서브 쿼리로 쓸 것을 권유
<문제>
1. 제갈민 보다 나중에 부임한 교수의 명단을 검색
SQL>select pname , hiredate
2 from professor
3 where hiredate >
4 (select hdate 부임일 from emp where ename in ('제갈민'))
2. 관우(값이 출력되지 않아 '소교'로 수정)보다 일반 화학과목의 학점이 더 낮은 학생의 명단을 학점과 검색
SQL>select s.sname, sc.result, c.cname, sc.grade
2 from student s, course c, score sc, scgrade sc
3 where sc.cno = c.cno and s.sno = sc.sno and
4 cname in ('일반화학') and sc.result <
5 (select sc.result from student s, score sc, course co
6 where s.sno = sc.sno and co.cno = sc.cno and s.sname in ('소교') and
7 co.cname in ('일반화학'))
8 and sc.result between sc.loscore and sc.hiscore;
10. 서브쿼리 : 다중 행, 다중 열 서브 쿼리
>다중 행 서브 쿼리
서브 쿼리에서 여러 행이 검색되는 것으로 다양한 다중 행 연산자를 이용함, 연산자를 제외하면 단일 행 서브 쿼리와 동일
* 서브 쿼리에 여러 개의 행이 검색되는 쿼리문이 다중 행 서브 쿼리임
* 다중 행 서브 쿼리는 다중 행 연산자를 이용
* 다중 행 연산자의 종류
- IN : 나열된(검색된) 값 중에 하나만 일치하면 참
-ANY : 나열된(검색된) 값 중에 조건에 맞는 것이 하나 이상 있으면 참
-ALL : 모든 나열된(검색된) 값과 조건에 맞아야 함
-예제1. 20번 부서원들과 동일한 관리자로부터 관리 받는 사원을 검색
SQL>SELECT eno, ename
2 FROM emp
3 WHERE mgr IN (SELECT mgr FROM emp WHERE dno = '20' )
4 AND dno != '20';
서브 쿼리의 결과가 몇 개일지 예측할 수 없는 경우 다중 행 연산자를 이용하는 다중 행 서브 쿼리 문을 이용. 특히 서브 쿼리의 검색된 결과가 현재는 한 개 일지라도, 위와 같이 emp 테이블의 내용이 변해 감에 따라 검색 결과가 바뀔 가능성이 있다면 단일 행 연산자를 사용하지 않아야 함. 만일 20번 부서의 인원이 단 1명이라면 당장은 '=' 연산자를 사용 할 수 있겠지만 부서의 인원은 조직의 상황에 따라 인원이 변경됨. 인원이 늘어날 경우 이 때 IN 연산자를 사용하여 문제를 예방함. 이것은 많은 수의 단일 행 서브 쿼리에서 고려되어야할 사항임. 서브 쿼리의 검색 결과가 언제나 한 개의 결과 값만을 검색한다는 보장이 없을 때는 단일 행 연산자 보다는 다중 행 연산자를 이용하는 것이 미래의 에러를 방지 할 수 있음.
▲ TIP
만일 여러분의 담당 상급자가 '우리 부서에 나랑 같은 업무 하는 사람이 누가 있지' 라고 물었을 때 '너'라고 대답하면 어떻게 되나? 서브 쿼리에서는 조건을 위해 검색한 내용을 최종 검색에 포함 시키는 것도 이와 같음. 조건을 위해 검색한 내용을 최종 검색에 포함 시키는 것이 절대 안되는 것은 아니지만 거의 대부분 필요치 않음.
-예제2. 10번 부서원들보다 급여가 낮은 사원을 검색
SQL>SELECT eno, ename, dno
2 FROM emp
3 WHERE sal < ALL (SELECT sal FROM emp WHERE dno = '10')
4 AND dno != '10';
10번 부서원들 보다 급여가 낮다는 것은 10번 부서원 중에 가장 낮은 급여를 받는 사람보다 급여가 작다는 것으로 해석이 가능. 즉 여러 명의 다양한 10번 부서원의 급여들 중에 가장 낮은 값보다 적다라는 의미이므로 모든 급여들보다 낮다고 해석가능. 그러므로 ALL을 이용 검색된 모든 값보다 낮은 값을 검색하도록 함. 이와 같이 부등호를 이용하는 다중 행 서브 쿼리문은 MIN이나 MAX함수를 이용한 문으로 수정 가능
위 문장을 수정하면
SQL>SELECT eno, ename, dno
2 FROM emp
3 WHERE sal < ALL (SELECT MIN(sal) FROM emp WHERE dno = '10')
4 AND dno != '10';
보통 부등호에 ALL이나 ANY 키워드를 쓰는 것 보다는 그룹함수를 쓰는 것이 가독성이 좋음. 서브 쿼리에 의해 검색된 값이 여러 개인 경우 ALL 보다 크거나 작다는 의미는 가장 큰 값보다 크거나 가장 작은 값보다 작다는 의미이고 ANY 보다 크거나 작다는 것은 작은 값보다 크거나 큰 값보다 작다는 의미.
다중 행 연산자와 그룹함수
- 다중 행 연산자인 ALL이나 ANY는 다음과 같이 그룹 함수를 이용 표현 할 수 있음
* 컬럼 > ALL -> 컬럼 > MAX() : 가장 큰 값보다 크다
* 컬럼 < ALL -> 컬럼 < MIN() : 가장 작은 값보다 작다
* 컬럼 > ANY -> 컬럼 > MIN() : 가장 작은 값보다 크다
* 컬럼 < ANY -> 컬럼 < MAX() : 가장 큰 값보다 작다
-예제3. 20번 부서원들과 보너스가 같은 사원을 검색
SQL>SELECT eno, ename, comm, dno
2 FROM emp
3 WHERE comm IN
4 (SELECT comm FROM emp WHERE dno = '20') AND dno != '20';
SQL>SELECT eno, comm
2 FROM emp
3 WHERE dno = '20';
SQL>SELECT eno, ename, comm, dno
2 FROM emp
3 WHERE comm IN (SELECT NVL(comm,0) FROM emp WHERE dno = '20')
4 AND dno != '20';
다중 행 서브 쿼리 결과에 널이 포함된 경우 검색 결과는 실패함. 문제는 테이블이 매우 큰 경우 어떤 컬럼에 어느 만큼의 널 값이 저장되어있는지 매우 시간이 많이 걸리는 확인 과정 이전에는 알 수 없다는 것. 가장 좋은 방법은 널이 반드시 필요한 경우를 제외하고 널 값자체를 허용하지 않는 것.
> 다중 열 서브 쿼리
- 단일, 다중 행 서브 쿼리와는 달리 여러 개의 컬럼을 검색하는 서브 쿼리를 다중 열 서브 쿼리라 함. 다중 열 서브 쿼리는 주로 'IN'을 사용하지만 검색 결과가 분명히 한 개의 행이 보장 된다면 '='도 사용 가능
SQL>SELECT [DISTINCT | ALL] 컬럼, 컬럼, ...
2 FROM 테이블
3 WHERE (컬럼1, 컬럼2, ...) IN (SELECT 문장 : Sub query 문)
* 서브 쿼리의 SELECT 문에 여러 개의 컬럼을 검색
* 여러 개의 컬럼을 검색하는 서브 쿼리 문을 이용할 때 반드시 비교 대상 컬럼과 1:1 대응되어야 함
* 다중 열 서브 쿼리에서 서브 쿼리의 검색 결과가 단지 하나의 행이라면 '=' 연산자 사용이 가능하지만 되도록 'IN' 연산자를 이용
-예제4. 손하늘과 동일한 관리자의 관리를 받으면서 업무도 같은 사원 검색
SQL>SELECT eno, ename, mgr, job
2 FROM emp
3 WHERE (mgr, job) IN
4 (SELECT mgr, job FROM emp WHERE ename = '손하늘') AND ename != '손하늘';
-예제5. 김선유와 부서 및 업무가 동일한 사원을 검색
SQL>SELECT eno, ename, dno, job
2 FROM emp
3 WHERE (dno, job) IN
4 (SELECT dno, job FROM emp WHERE ename = '김선유')
5 AND ename != '김선유';
SQL>SELECT eno, ename, dno, job
2 FROM emp
3 WHERE dno in (SELECT dno FROM emp WHERE ename = '김선유')
4 AND job in (SELECT job FROM emp WHERE ename = '김선유')
5 AND ename != '김선유';
SQL>SELECT eno, ename, dno, job
2 FROM emp
3 WHERE ename != '김선유';
이렇게 서브 쿼리에 검색 결과가 두 행 이상인 경우 이를 단일 열 서브 쿼리로 수정한 결과는 신뢰성을 가질 수 없음.
<문제>
(서브 쿼리가 하나이상 포함되도록 함.)
1. 10번 부서 사원들과 연봉이 동일한 사원을 검색
SQL>SELECT ename, sal*12+NVL(comm,0)
2 FROM emp
3 WHERE sal*12+NVL(comm,0) IN (SELECT sal*12+NVL(comm,0) FROM emp
4 WHERE dno = '10' )
5 AND dno !='10';
2. 10번 부서 사원들과 급여 및 연봉이 동일한 사원을 검색
SQL>SELECT ename, sal*12+NVL(comm,0)
2 FROM emp
3 WHERE (sal, sal*12+NVL(comm,0)) IN
4 (SELECT sal, sal*12+nvl(comm,0) FROM emp) AND dno != '10';
11. 집합 연산자
집합 연산자는 합집합, 교집합, 차집합을 의미하는 연산자로 SELECT 결과에 대해서 집합 연산을 수행.
집합 연산자의 종류
> 집합 연산
SQL>SELECT ...
2 [UNION ALL | UNION | INTERSECT | MINUS]
3 SELECT ...
* 두 SELECT 문의 결과를 집합 연산
* 검색 결과의 헤더는 앞쪽 SELECT 문에 의해 결정
* 두 SELECT 문의 컬럼 개수와 데이터 타입이 일치해야 함
* ALL : 중복을 포함
-예제1. 2000년 이후에 입사한 사원과 부임한 교수의 명단을 검색
SQL>SELECT eno, ename, hdate
2 FROM emp
3 WHERE hdate > '2000/01/01'
4 UNION
5 SELECT pno, pname, hiredate
6 FROM professor
7 WHERE hiredate > '2000/01/01';
집합 연산에 사용되는 두 개의 SELECT 문은 컬럼의 수와 타입이 동일해야 함
UNION을 사용한 검색 결과는 중복의 제거를 위해 정렬되지만 UNION ALL은 중복을 제거하지 않음으로 정렬을 수행하지 않음
<문제>
1. 정교수의 명단과 모델링이 업무인 직원의 이름, 입사일(부임일)을 검색
SQL>SELECT eno, ename, hdate
2 FROM emp
3 WHERE job = '모델링'
4 UNION
5 SELECT pno, pname, hiredate
6 FROM professor
7 WHERE orders = '정교수'
댓글 없음:
댓글 쓰기