SUB QUERY(서브쿼리)
|
기본 문법 |
SELECT SELECT_LIST
FROM TABLE 또는 VIEW
WHERE 조건 연산자 (SELECT SELECT_LIST
FROM TABLE
WHERE 조건); |
|
유형 |
SELECT ENAME, SAL
FROM EMP
WHERE SAL>(SELECT SAL
FROM EMP
WHERE ENAME='SCOTT'); |
■ 단일행 서브쿼리
--단일행 SUB QUERY 연습문제1
--STUDENT 테이블과 DEPARTMENT 테이블을 사용하여 이윤나 학생과 1전공(DEPTNO1)이 동일한 학생들의 이름과
--1전공 이름을 출력하세요
SELECT S.NAME, D.DNAME
FROM STUDENT S, DEPARTMENT D
WHERE S.DEPTNO1 = (SELECT DEPTNO1
FROM STUDENT
WHERE NAME = '이윤나')
AND S.DEPTNO1 = D.DEPTNO;
--단일행 SUB QUERY 연습문제2
--PROFESSOR 테이블에서 입사일이 송도권 교수보다 나중에 입사한 사람의 이름과 입사일, 학과명을 출력하세요
SELECT P.NAME, P.HIREDATE, D.DNAME
FROM PROFESSOR P, DEPARTMENT D
WHERE HIREDATE > (SELECT HIREDATE
FROM PROFESSOR
WHERE NAME = '송도권')
AND P.DEPTNO = D.DEPTNO;
--단일행 SUB QUERY 연습문제3
--STUDENT 테이블에서 1전공(DEPTNO1)이 101번 인 학과의 평균 몸무게보다 몸무게가 많은 학생들의 이름과 몸무게를
--출력하세요.
SELECT NAME,WEIGHT
FROM STUDENT
WHERE WEIGHT > (SELECT AVG(WEIGHT)
FROM STUDENT
WHERE DEPTNO1 = 101);
--단일행 SUB QUERY 연습문제4
--PROFESSOR 테이블에서 심슨 교수와 같은 입사일에 입사한 교수 중에서 조인형 교수보다 월급을 적게 받는 교수의
--이름과 급여, 입사일을 출력하세요
SELECT NAME,PAY,HIREDATE
FROM PROFESSOR
WHERE PAY < (SELECT PAY
FROM PROFESSOR
WHERE NAME = '조인형')
AND HIREDATE = (SELECT HIREDATE
FROM PROFESSOR
WHERE NAME = '심슨');
■ 다중 행 SUB QUERY
SUB QUERY의 결과과 2건 이상 출력되는 것을 말한다. 다중 행 SUB QUERY는 SUB QUERY의 결과가 여러건 출력되기 때문에 단일 행 연산자를 사용할 수 없다. 그래서 다중 행 SUB QUERY의 경우 아래와 같이 별도의 연산자가 존재함
|
연산자 |
의미 |
|
IN |
같은 값을 찾음 |
|
>ANY |
최소 값을 반환함 |
|
<ANY |
최대 값을 반환함 |
|
<ALL |
최소 값을 반환함 |
|
>ALL |
최대 값을 반환함 |
|
EXISTS |
SUB QUERY의 값이 있을 경우 반환함 |
SAL > ANY (100,200,300) 으로 되면 ANY 자리에 최소값인 100이 반환
SAL > 100이라는 식으로 표현
SAL < ALL (100,200,300) 이 되면 SUB QUERY의 최소 값인 100이 반환되긴 하지만 식은
SAL < 100이 되 버림
--EMP 테이블의 부서 별로 가장 급여를 많이 받는 사원의 사원번호 , 이름, 급여 , 부서번호를 조회하여라
SELECT EMPNO,ENAME,SAL,DEPTNO
FROM EMP
WHERE SAL IN (SELECT MAX(SAL)
FROM EMP
GROUP BY DEPTNO);
--다중 행 SUB QUERY
--EMP2 테이블과 DEPT2 테이블을 참조하여 근무지역(DEPT2 테이블의 AREA 컬럼)이 서울 지사인 모든 사원들의
--사번과 이름, 부서번호를 출력
SELECT EMPNO, NAME, DEPTNO
FROM EMP2
WHERE DEPTNO IN (SELECT DCODE
FROM DEPT2
WHERE AREA = '서울지사');
-- 업무가 SALESMAN 인 최소 한 명 이상의 사원보다 급여를 많이 받는 사원의 이름, 업무를 조회하여라
SELECT ENAME, JOB, SAL
FROM EMP
WHERE SAL > ANY( SELECT SAL
FROM EMP
WHERE JOB = 'SALESMAN')
AND JOB !='SALESMAN';
-- 다중 행 SUB QUERY 예 2
-- EMP2 테이블을 사용하여 전체 직원 중 과장 직급의 최소 연봉자보다 연봉이 높은 사람의 이름과 직급,
-- 연봉을 출력하세요. 단 연봉 출력 형식은 아래와 같이 천 단위 구분기호와 원 표시를 하세요
SELECT NAME, POSITION, PAY
FROM EMP2
WHERE 1=1
AND POSITION != '과장'
AND PAY > ANY(SELECT PAY
FROM EMP2
WHERE POSITION = '과장');
-- 다중 행 SUB QUERY 예 3
-- STUDENT 테이블을 조회하여 전체 학생 중에서 체중이 4학년 학생들의 체중이 4학년 학생들의 체중에서 가장 적게
-- 나가는 학생보다 몸무게가 적은 학생의 이름과 학년과 몸무게를 출력하세요.
SELECT NAME, GRADE, WEIGHT
FROM STUDENT
WHERE WEIGHT < ALL(SELECT WEIGHT
FROM STUDENT
WHERE GRADE =4)
■ 다중 컬럼 SUB QUERY
서브쿼리로 출력되는 컬럼수가 2개 이상 출력되는 서브 쿼리
SELECT EMPNO, ENAME, SAL, DEPTNO
FROM EMP
WHERE (DEPTNO,SAL) IN (SELECT DEPTNO, MAX(SAL)
FROM EMP
GROUP BY DEPTNO);
-- 다중 컬럼 SUB QUERY 1
-- STUDENT 테이블을 조회하여 각 학년별로 최대키를 가진 학생들의 학년과 이름과 키를 출력하세요
SELECT GRADE,NAME,HEIGHT
FROM STUDENT
WHERE (GRADE,HEIGHT) IN (SELECT GRADE,MAX(HEIGHT)
FROM STUDENT
GROUP BY GRADE)
ORDER BY 1;
-- 다중 컬럼sub query 연습문제 1:
-- PROFESSOR 테이블을 조회하여 각 학과 별로 입사일이 가장 오래된 교수의 교수번호와 이름, 학과명을 출력하세요
-- (학과이름순으로 오름차순 정렬)
SELECT P.NAME, D.DNAME
FROM PROFESSOR P , DEPARTMENT D
WHERE (P.DEPTNO,P.HIREDATE) IN ( SELECT DEPTNO,MIN(HIREDATE)
FROM PROFESSOR
GROUP BY DEPTNO )
AND P.DEPTNO = D.DEPTNO
ORDER BY D.DNAME ;
-- 다중 컬럼 SUB QUERY 연습문제 2
-- EMP2 테이블을 조회하여 직급별로 해당 직급에서 최대 연봉을 받는 직원의 이름이 직급, 연봉을 출력하세요
-- 연봉순으로 오름차순 정렬하세요
SELECT NAME, PAY
FROM EMP2
WHERE (POSITION,PAY) IN ( SELECT POSITION,MAX(PAY)
FROM EMP2
GROUP BY POSITION)
ORDER BY PAY;
-- 다중 컬럼 SUB QUERY 연습문제 3 -- 다중행 컬럼 서브쿼리
-- EMP2 테이블을 조회하여 각 부서별 평균 연봉을 구하고 그 중에서 평균 연봉이 가장 적은 부서의
-- 평균 연봉보다 적게 받는 직원들의 부서명, 직원명, 연봉을 출력하세요.
-- ( 단, 연봉이 적은 순으로 나열하세요)
SELECT D.DNAME,E.NAME,E.PAY
FROM EMP2 E, DEPT2 D
WHERE PAY < ALL (SELECT AVG(PAY)
FROM EMP2
GROUP BY DEPTNO)
AND D.DCODE = E.DEPTNO
ORDER BY PAY;
--STUDENT 테이블을 이용하여 사용자 아이디가 BINGO인 학생과 같은 학년인 학생의
--학번, 이름, 학년을 출력하여라
SELECT STUDNO, NAME, GRADE
FROM STUDENT
WHERE GRADE = (SELECT GRADE
FROM STUDENT
WHERE ID = 'bingo');
--STUDENT 테이블을 이용하여 101번 학과 학생들의 평균 몸무게보다 적은 학생의 이름, 학과번호,몸무게를 출력하여라.
SELECT NAME, DEPTNO1, WEIGHT
FROM STUDENT
WHERE WEIGHT < ( SELECT AVG(WEIGHT)
FROM STUDENT
WHERE DEPTNO1 = 101 );
--STUDENT,DEPARTMENT 테이블을 이용하여 정보(PART:100)에 소속된 모든 학생의 학번, 이름, 학과번호를 출력하여라
SELECT STUDNO, NAME, DEPTNO1
FROM STUDENT
WHERE DEPTNO1 IN (SELECT DEPTNO
FROM DEPARTMENT
WHERE PART = 100);
--STUDENT 테이블을 이용하여 모든 학생 중에서 4학년 학생 중 키가 제일 작은 학생보다 키가 큰 학생의
--학번, 이름, 키를 출력하여라
SELECT STUDNO, NAME, HEIGHT
FROM STUDENT
WHERE HEIGHT > ANY( SELECT HEIGHT
FROM STUDENT
WHERE GRADE = 4);
-- STUDENT 테이블을 이용하여 학년별로 몸무게가 최소인 학생의 이름, 학년, 몸무게를 출력하여라
SELECT NAME, GRADE, WEIGHT
FROM STUDENT
WHERE (GRADE,WEIGHT) IN (SELECT GRADE,MIN(WEIGHT)
FROM STUDENT
GROUP BY GRADE);
■ 상호 연관 SUB QUERY
- 메인쿼리절과 서브쿼리간에 검색 결과를 교환하는 서브쿼리
- 메인쿼리와 서브쿼리간의 결과를 교환하기 위하여 서브쿼리의 WHERE 조건절에서 메인쿼리의 테이블과 연결
*주의*
행을 비교할 때마다 결과를 메인으로 반환하는 관계로 처리 성능이 저하될 수 있음
--상호 연관 SUB QUERY
-- EMP2 테이블을 조회해서 직원 들 중에서 자신의 직급의 평균연봉과 같거나 많이 받는 사람들의
--이름과 직급, 현재 연봉을 출력하세요
SELECT NAME, POSITION, PAY
FROM EMP2 A
WHERE PAY >= (SELECT AVG(PAY)
FROM EMP2 B
GROUP BY A.POSITION = B.POSITION);
■ 스칼라서브쿼리 : SELECT절에서 함수처럼 사용되는 쿼리문
- 하나의 레코드만 리턴이 가능( 반환값은 한 개) 두 개이상의 레코드는 리턴 X
- 일치하는 데이터가 없더라고 NULL 값을 리턴 할 수 있다.
SELECT D.DEPTNO, (SELECT MIN(EMPNO) FROM EMP WHERE DEPTNO = D.DEPTNO)
FROM DEPT D
ORDER BY D.DEPTNO;
■ 인라인뷰(상호연관 SUB QUERY 보다 성능이 좋음)
- FROM 절에서 임시 공간에 테이블을 생성하여 사용하는 뷰와 비슷한 저장 형태
- FROM 절에 있는 서브쿼리가 인라인 뷰를 생성
- 효율적인 검색 가능
- FROM 절에 있는 서브쿼리에는 자주 별칭 사용
--STUDENT 테이블을 이용하여 각 학년 별로 평균키보다 큰 학생들의 이름, 학년, 키를 출력하여라
SELECT NAME, A.GRADE, HEIGHT
FROM STUDENT A, (SELECT GRADE,AVG(HEIGHT) AS "B_HEIGHT"
FROM STUDENT
GROUP BY GRADE) B
WHERE A.HEIGHT > B_HEIGHT
AND A.GRADE = B.GRADE
ORDER BY GRADE,HEIGHT;
