[SQL 기본과 활용] Subquery(★★★)

서브쿼리

     SQL문 안에 포함된 SQL문

     예) 신장이 가장 큰 선수의 정보 조회

SELECT MAX(HEIGHT)

FROM PLAYER

+

SELECT PLAYER_NAME, POSITION, HEIGHT

FROM PLAYER

WHERE HEIGHT = 194;

=

SELECT PLAYER_NAME, POSITION, HEIGHT

FROM PLAYER

WHERE HEIGHT = (SELECT MAX(HEIGHT) FROM PLAYER);

 

서브쿼리

     SQL문 안에 포함된 SQL문

          서브쿼리 위치 : SELECT절, WHERE절, FROM절(Inline View)

          결과 칼럼/행의 수 : 단일행 서브쿼리 / 다중행서브쿼리

                                   단일칼럼 서브쿼리 / 다중칼럼서브쿼리

          메인 쿼리와의 연관성 : 연관(상관) 서브쿼리 / 비연관 서브쿼리

     서브쿼리는 메인쿼리의 칼럼 모두 사용 가능

     메인쿼리는 서브쿼리의 칼럼 사용 불가

          Inline View에 정의된 칼럼만 사용 가능

 

결과 칼럼/행의 수에 따른 구분

단일행 서브쿼리

     서브쿼리의 결과 건수가 반드시 1건 이하

     단일행 비교연산자(=, <, ≤, >, ≥, <>)와 함께 사용

          결과가 2건 이상이면 Run Time 오류 발생

          *run time 오류 : 문법적이 오류 없이 진행됐는데 진행 중 문제가 발생

 

예) '2007182'번 선수와 같은 팀에 속하는 선수의 이름, 포지션, 팀ID 출력

 

SELECT TEAM_ID

FROM PLAYER

WHERE PLAYER_ID = '2007182';

SELECT PLAYER_NAME, TEAM_ID

FROM PLAYER

WHERE TEAM_ID = (SELECT TEAM_ID FROM PLAYER WHERE PLAEYR_ID = '2007182');

 

*단일행 서브쿼리가 업데이트절에 사용된 경우?

Q) 사번이 7499인 직원의 매니저를 사번 7369인 직원의 매니저로 변경하는 질의를 완성하시오.

UPDATE EMP SET MGR = (SELECT MGR FROM EMP WHERE EMPNO = 7369)

WHERE EMPNO = 7499;

 

"단일행 비교연산자를 썼을 때는 UNIQUE한 데이터를 사용해야 바람직하다"

 

다중행 서브쿼리

     서브쿼리의 결과 건수가 2건 이상일 가능성이 있을 때

     다중행 비교 연산자와 함께 사용

          2건 이상일 가능성은 있지만 결과 건수가 우연히 1개인 경우 → 단일행 비교 연산자도 에러는 발생하지 않음

 

다중행 연산자

     IN(서브쿼리) : 임의의 결과 중 하나만 만족해도 참 ( MULTIPLE OR 조건 )

     비교연산자 ALL(서브쿼리) : 결과의 모든 값을 만족해야 하는 조건

     비교연산자 ANY/SOME (서브쿼리) : 결과의 어느 하나의 값이라도 만족하면 되는 조건 (ANY = SOME)

     EXISTS (서브쿼리) : 조건을 만족하는 값이 존재하는지 여부를 확인. 조건을 만족하는 건을 하나라도 찾으면 검색 중     지 ( 속도가 빠름)

 

IN 연산자

SELECT PLAYER_NAME, HEIGHT, BACK_NO

FROM PLAYER

WHERE HEIGHT IN (SELECT HEIGHT FROM PLAYER WHERE BACK_NO = 15);

ALL 연산자

     결과의 모든 값을 만족해야 하는 조건

     *부등호와 사용됨

     예)X > ALL (1, 2, 3, 4, 5)라면 X >5가 되어야 함

SELECT PLAYER_NAME, HEIGHT, BACK_NO

FROM PLAYER

WHERE HEIGHT > ALL (SELECT HEIGHT FROM PLAYER WHERE BACK_NO = 15);

BACK_NO = 15인 사람들 중 가장 큰 사람의 HEIGHT가 190이기에 나오는 결과

ANY(=SOME) 연산자

     결과의 어느 하나의 값이라도 만족하면 되는 조건 (ANY = SOME)

     예) X > ANY (1, 2, 3, 4, 5) 라면 X > 1 이면 됨

SELECT PLAYER_NAME, HEIGHT, BACK_NO

FROM PLAYER

WHERE HEIGHT > ANY (SELECT HEIGHT FROM PLAYER WHERE BACK_NO = 15);

EXIST 연산자

     조건을 만족하는 값이 존재하는지 여부를 확인

     조건이 만족되는 1건만 찾으면 더 이상 검색하지 않음 ( 속도가 빠름 )

     주로 참/거짓의 조건 판단용으로 사용됨

SELECT PLAYER_NAME, HEIGHT, BACK_NO

FROM PLAYER

WHERE EXISTS (SELECT 1 FROM PLAYER WHERE BACK_NO = 15);

"WHERE 절이 참이면 전체 선수 명단이 나옴. 거짓이면 아무것도 안나옴" >> 조건문만 중요하므로 일반적으로 1을 씀(아무 의미 없음)

연관(Correlated) 서브쿼리

     메인쿼리의 칼럼이 서브쿼리에서 사용된 쿼리(개념적으로,,)

     Q) AVG(SAL)은 SAL의 평균을 구하는 함수이다. 다음은 무엇을 구하는 질의인가?

SELECT ENAME, SAL, DEPTNO

FROM EMP M

WHERE SAL > (SELECT AVG(S.SAL) FROM EMP S WHERE M.DEPTNO = S.DEPTNO);

연관 서브쿼리의 특징

     메인쿼리의 칼럼이 서브쿼리에서 사용된 쿼리

          cf) 비연관 서브쿼리 : 서브쿼리에서 메인쿼리의 칼럼을 사용하지 않음

     메인쿼리가 먼저 수행되고 그 후에 서브쿼리가 수행됨

          테이블의 별칭을 이용하여 메인 쿼리에서 서브쿼리로 정보 전달

          서브쿼리가 메이누커리의 값을 이용, 그 후에 서브쿼리의 결과를 메인쿼리가 이용

     서브쿼리에서 메인쿼리의 칼럼과 서브쿼리의 칼럼 간 비교가 이루어짐

          메인 쿼리에서는 서브쿼리의 칼럼 사용 불가

 

다중칼럼 서브쿼리

     서브쿼리의 결과로 여러 칼럼이 반환됨

     예) PLAYER_ID가 2007188인 선수와 키, 포지션이 같은 선수 조회

SELECT PLAYER_NAME, HEIGHT, POSITION, BACK_NO

FROM PLAYER

WHERE (HEIGHT, POSITION) =

(SELECT HEIGHT, POSITION

FROM PLAYER

WHERE PLAYER_ID = '2007188');

"비교하는 칼럼의 갯수와 순서가 동일해야 함!!"

 

다중칼럼 다중행 서브쿼리

     서브쿼리의 결과로 여러 칼럼이 반환될 때

 

     Q) 부서별로 최고 급여를 받는 사원의 사원명, 부서번호, 급여를 출력하는 질의를 완성하시오

     (단, 부서별 최고 급여를 구하는 질의는 아래 참고)

SELECT DEPTNO, MAX(SAL)

FROM EMP

GROUP BY DEPTNO;

 

SELECT ENAME, DEPTNO, SAL

FROM EMP

WHERE (DEPTNO, SAL) IN (SELECT DEPTNO, MAX(SAL) FROM EMP GROUP BY DEPTNO);

스칼라 서브쿼리(Scalar Subquery)

     하나의 값을 반환하는 서브쿼리

          단일행, 단일칼럼

          하나의 값을 반환한다는 점에서 함수(function)의 특성을 가짐

          공집합을 반환하는 경우 NULL

 

     칼럼이 올 수 있는 대부분의 곳에서 사용 가능

          SELECT 절, WHERE절, 함수의 인자, ORDER BY 절, CASE 절, HAVING 절

          예) SELECT, WHERE 절에서 스칼라 서브쿼리를 사용

SELECT EMPNO, ENAME

, (SELECT DNAME FROM DEPT WHERE DEPTNO = A.DEPTNO) AS DNAME

FROM EMP A;

SELECT EMPNO, ENAME, DEPTNO

FROM EMP A

WHERE (SELECT DNAME FROM DEPT WHERE DEPTNO = A.DEPTNO) = 'SALES';

     스칼라 서브쿼리는 함수의 인자로도 사용 가능

     Q) 부서명을 3글자만 출력하는 질의를 작성하시오 (SUBSTR 함수 사용)

SELECT EMPNO, ENAME, SUBSTR( (SELECT DNAME FROM DEPT WHERE DEPTNO = A.DEPTNO), 1, 3) AS DNAME FROM EMP A;

뷰(View)

     테이블은 실제로 데이터를 갖고 있지만, 뷰는 실제 데이터를 갖지 않음

          뷰 정의(View Definition, SQL 텍스트 파일)만 갖고 있음

          쿼리에서 뷰가 사용되면 DBMS 내부적으로 질의를 재작성(rewrite)

 

     실제 데이터를 가지고 있지 않지만 테이블의 역할 수행

          **가상 테이블(Virtual Table)**이라고도 함

     

     CREATE VIEW 문을 통해 VIEW 생성

CREATE VIEW V_PLAYER_TEAM AS

SELECT P.PLAYER_NAME, P.BACK_NO, P.TEAM_ID, T.TEAM_NAME

FROM PLAYER P INNERJOIN TEAM T

ON P.TEAM_ID = T.TEAM_ID;

 

     VIEW 확인

SELECT * FROM USER_VIEWS;

 

     계층적 뷰 생성 "뷰로부터 뷰를 만드는 것"

Q) 사원과 부서 테이블로부터 사원번호, 사원명, 부서번호, 부서명을 추출한 뷰 V_EMP_DEPT를 작성하시오. 또한 이 뷰로부터 사원명과 부서명 만들 다시 추출한 V_EMP_DEPT2를 작성하시오

CREATE VIEW V_EMP_DEPT AS

SELECT EMPNO, ENAME, DEPTNO, DNAME

FROM EMP JOIN DEPT

USING (DEPTNO);

 

CREATE VIEW V_EMP_DEPT2 AS

SELECT EMPNO, DEPTNO

FROM v_emp_dept;

 

뷰의 장점

     독립성 : 테이블 구조가 변경시, 뷰만 변경되고 뷰를 사용하는 응용 프로그램은 변경될 필요가 없음.

     편리성 : 복잡한 질의를 뷰로 생성하여 질의의 가독성을 높임

     보안성 : 민감한 정보(급여 정보 등)를 제외하고 뷰를 생성하여 사용자로부터 정보를 보호할 수 있음.

 

인라인 뷰(Inline View)

     FROM 절에서 사용되는 서브쿼리

     실행 순간에만 임시적으로 생성되며 DB에 저장되지 않음

          인라인 뷰(Inline View) = 동적 뷰(Dynamic View)

          일반 뷰 = 정적 뷰(Static View) → 우리가 직접 정의한 View

 

     인라인 뷰의 SELECT 문에서 정의된 칼럼만! 메인 쿼리에서 사용 가능

          cf) 일반적으로 서브쿼리에서 정의된 칼럼은 메인 쿼리에서 사용 불가