서브 쿼리

한준수·2023년 6월 2일
SQLD
0

SQLD

목록 보기
27/31

서브쿼리란

하나의 SQL문 안에 포함되어 있는 다른 SQL문을 뜻한다.
알려지지 않은 기준을 이용한 검색에 사용함.

서브쿼리 주의사항

  • 서브쿼리는 반드시 괄호로 둘러 쌓아햐 한다.
  • 서브쿼리는 메인쿼리가 실행되기 이전에 한번만 실행된다.
  • 서브쿼리는 단일 행 또는 복수 행 비교 연산자와 함께 사용 가능하다. 단일 행 비교 연산자는 서브쿼리의 결과가 반드시 1건 이하여야 하고 복수 행 비교 연산자는 결과 건수와 상관 없다.
  • 서브 쿼리에서는 OREDR BY를 사용하지 못한다.
  • SELECT, FROM, WHERE, HAVING, ORDER BY,
    INSERT-VALUES, UPDATE-SET 절에 사용 가능하다.
  • 서브 쿼리는 서브쿼리 레벨과 상관 없이 항상 메인쿼리 레벨로 결과 집합이 생성된다.
  • 서브쿼리는 메인쿼리의 칼럼을 모두 이용할 수 있지만, 메인쿼리는 서브쿼리의 칼럼을 이용할 수 없다.

비교 연산자

단일행 비교 연산자 : =,<,,<=,>=,>,<> 등
다중 행 비교 연산자: IN, ANY, ALL, SOME 등
스칼라 비교 연산자 : 한 행, 한 칼럼만을 반환하는 서브쿼리

단일 행 서브쿼리 (SINGLE ROW)

서브 쿼리의 실행 결과가 항상 1건 이하인 서브 쿼리를 의미하며, 단일 행 비교 연산자와 함께 사용한다.

  • 평균 급여보다 많은 급여를 받는 사원
SELECT ENAME, SAL
FROM EMP
WHERE SAL > (SELECT AVG(SAL)
			 FROM EMP);

다중 행 서브쿼리(MULTI ROW)

서브쿼리의 실행 결과가 여러 건인 서브쿼리를 의미한다.
다중 행 비교 연산자와 함께 사용한다.

  • 다중 행 비교 연산자
    다중 행 연산자 설명
    IN(서브쿼리) 서브쿼리의 결과에 존재하는 임의의 값과 동일한 조건을 의미
    비교연산자 ALL(서브 쿼리) 서브쿼리의 결과에 존재하는 모든 값을 만족하는 조건을 의미
    비교 연산자로 > 를 사용 했다면 메인 쿼리는 서브 쿼리의 모든 결과를 만족해야 하므로, 서브쿼리 결과의 최대값 보다 큰 모든 건이 조건을 만족함.(즉, 서브쿼리의 최대값 보다 큰 값은 모두 참)
    비교연산자 ANY/SOME(서브쿼리) 서브쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건을 의미
    비교 연산자로 > 를 사용했다면 메인쿼리는 서브쿼리의 값들 중 어떤 값이라도 만족하면 되므로, 서브쿼리의 결과가 최소값보다 큰 모든 건이 조건을 만족함(즉, 서브쿼리의 최소값보다 큰 값은 모두 참)
    EXIXT 메인 쿼리의 비교 조건이 서브 쿼리의 결과 중에서 만족하는 값이 하나라도 존재하면 참.
    조건을 만족하는 건이 여러 건이더라도 1건만 찾으면 더 이상 검색하지 않음.

ALL 연산자를 이용한 다중 행 서브 쿼리

  • 30번 소속 사원들 중에서 급여를 가장 많이 받는 사원보다 더 많은 급여를 받는 사원의 이름, 급여를 출력
SELECT ENAME,SAL
FROM EMP
WHERE SAL > ALL(SELECT SAL
			FROM EMP
            WHERE DEPTNO = 30);

ANY 연산자를 이용한 다중 행 서브 쿼리

  • 부서 번호가 30번인 사원들의 급여중 가장 작은 값 보다 많은 급여를 받는 사원의 이름, 급여를 출력
SELECT ENAME,SAL
FROM
WHERE SAL > ANY(SELECT SAL
				FROM EMP
                WHERE DEPTNO = 30);

다중 칼럼 서브 쿼리(MULTI COLUMN)

서브쿼리의 실행 결과로 여러 칼럼을 반환한다. 메인쿼리의 조건절에 여러 칼럼을 동시에 비교할 수 있다.
서브쿼리와 메인 쿼리에서 비교하고자 하는 칼럼 개수와 칼럼의 위치가 동일해야 한다.

IN 연산자와 AND != 혹은 AND <> 가 거의 모든 예제에 들어간다.

  • 형식
SELECT[DISTINCT|ALL] 칼럼, 칼럼 ....
FROM 테이블
WHERE(칼럼1, 칼럼2,...) IN (SELECT 문장 : SUB QUERY문)
  • 예제 
SELECT EMPNO 사번, ENAME 이름, DEPTNO 부서번호, JOB 업무
FROM EMP
WHERE (DEPTNO,JOB) IN (SELECT DEPTNO,JOB
					   FROM EMP
                        WHERE ENAME = 'SMITH')
 AND ENAME!= 'SMITH';

연관 서브쿼리 / 비연관 서브쿼리

비연관 서브쿼리

서브쿼리가 메인쿼리의 칼럼을 가지고 있지 않은 형태의 서브쿼리이다. 메인 쿼리에 값(서브쿼리가 실행된 결과)을 제공하기 위한 목적으로 주로 사용함

위에서 한것들은 모두 비연관 서브 쿼리이다.

연관서브쿼리 (Correlated Subquery)

연관 서브쿼리는 서브쿼리가 메인쿼리의 칼럼을 가지고 있는 형태의 서브쿼리다.

correlated subquery는 sub query가 main query의 값을 이용하고, 그렇게 구해진 sub query의 값을 다시 query가 다시 이용하게 된다.
연관 서브쿼리는 한개의 행을 비교할 때마다 결과가 메인쿼리로 리턴된다.

  • 예제
SELECT DEPTNO, ENAME, SAL
FROM EMP T1
WHERE SAL > (SELECT AVG(SAL)
			FROM EMP T2
            WHERE T2.DEPTNO=T1.DEPTNO);

위 예제에서
1. 메인쿼리에서 EMP T1 이라는 T1 별명을 서브쿼리로 전달
2. 메인쿼리에서 전달 받은 DEPTNO로 평균을 계산
3. 메인쿼리에서 평균 값보다 큰 것만 출력

WHERE절이 아닌 곳에서 사용하는 서브쿼리

SELECT 절에서의 서브쿼리 - 스칼라

SELECT 절에서 사용되는 서브쿼리는 스칼라 서브쿼리라고한다.

스칼라 서브쿼리란
스칼라 서브쿼리는 한 행, 한 칼럼(1ROW, 1COLUMN)만을 반환하는 서브쿼리를 말한다.

스칼라 서브쿼리는 칼럼을 쓸 수 있는 대부분의 곳에서 사용할 수 있다.

  • 예제
SELECT PLAYER_NAME 선수명, HEIGHT 키,
(SELECT AVG(HEIGHT) 
FROM PLAYER X
WHERE X.TEAM_ID=P.TEAM_ID)팀 평균키
FROM PLAYER P;

FROM 절에서의 서브쿼리 - 인라인 뷰

FROM 절에서 사용되는 서브쿼리를 Inline View 라고 한다.

FROM절에는 테이블 이름이 오게 되어있지만, 서브쿼리를 FROM절에 넣게 되면 서브쿼리의 결과가 마치 테이블인 것 처럼 사용할 수 있다.

인라인 뷰는 동적으로 생성된 테이블이기 때문에 인라인 뷰를 사용하는 것은 조인 방식을 사용하는 것과 같다.
그렇기 때문에 인라인 뷰의 칼럼은 SQL문을 자유롭게 참조할 수 있다.

  • 예제
SELECT T.TEAM_NAME 팀명, P.PLAYER_NAME 선수명,P.BACK_NO 백넘버
FROM (SELECT TEAM_ID,PLAYER_NAME,BACK_NO
		FROM PLAYER
        WHERE POSITION='MF') P,
TEAM T
WHERE P.TEAM_ID = T.TEAM_ID 
ORDER BY 선수명;

HAVING 절에서의 서브쿼리

HAVING 절은 그룹함수와 함께 사용될 때 그룹핑된 결과에 대해 부가적인 조건을 주기 위해서 사용한다.

  • 예제
SELECT P.TEAM_ID 팀코드, T.TEAM_NAME 팀명, AVG(P.HEIGHT) 평균키
FROM PLAYER P,TEAM T
WHERE P.TEAM_ID=T.TEAM_ID
GROUP BY P.TEAM_ID, T.TEAM_NAME
HAVING AVG(P.GEIGHT) < (SELECT AVG(HEIGHT)
						FROM PLAYER
                        WHERE TEAM_ID='K02');

평균키가 삼성 블루윙즈의 평균키보다 작은 팀의 이름과 해당 팀의 평균키를 구하는 예제이다.

UPDAT문에서의 서브쿼리

다른 테이블에서 데이터를 조회하여 UPDATE를 해야하는 경우가 있다.
UPDATE문에서 조인은 제약사항이 많기 때문에 서브쿼리를 활용하면 쉽게 해결할 수 있다.

  • 예제
UPDATE TEAM A
	SET A.STADIUM_NAME =
    (SELECT X.STADIUM_NAME
    FROM STADIUM X
    WHERE X.STADIUM_ID = A.STADIUM_ID);

팀 테이블과 스타디움 테이블의 스타디움ID 를 비교하여
스타디움 테이블에서 스타디움의 이름을 가져와
팀 테이블에 스타디움 이름을 추가하는 예제이다.

INSERT문의 VALUES절에서 사용하기

PLAYER 테이블에 '홍길동'이라는 선수를 삽입하고자 한다. 이때 PLAYER_ID의 값을 현재 사용중인 PLAYER_ID에 1을 더한 값으로 넣고자 한다. 다음과 같이 SQL문을 SQL문을 작성할 수 있다.

INSERT INTO PLAYER(PLAYER_ID,PLAYER_NAME,TEAM_ID)
VALUES((SELECT TO_CHAR(MAX(TO_NUMBER(PLAYER_ID))+1)
FROM PLAYER),'홍길동','K06');

뷰(VIEW)

테이블은 실제로 데이터를 가지고 있는 반면, 뷰(VIEW)는 실제 데이터를 가지고 있지 않다.
뷰는 실제 데이터를 가지고 있지 않지만 테이블이 수행하는 역할을 하기 때문에 가상 테이블이라고도 부른다

뷰는 한마디로 물리적인 테이블을 근거한 논리적인 가상 테이블이다.

  • 뷰 사용의 장점
    뷰의 장점 설명
    독립성 테이블의 구조가 변경되어도 뷰를 사용하는 응용 프로그램은 병경하지 않아도 된다.
    편의성 복잡한 질의를 뷰로 생성함으로써 관련 질의를 단순하게 작성할 수 있다.
    보안성 직원의 급여정보와 같이 숨기고 싶은 정보가 존재한다면, 뷰를 생성할 때 해당 칼럼을 배고 생성함으로써 사용자에게 정보를 감출 수 있다.

뷰 생성 - CREATE VIEW

  • 형식
CREATE[OR RELACE] [FORCE | NOFORCE] VIEW 뷰 이름
[(ALIAS)]
AS subquery
[WITH CHECK OPTION]
[WITH READ ONLY];

  • REPLACE
    REPLACE 옵션을 사용하면 새로운 뷰를 만들 수 있을 뿐 아니라 기존에 뷰가 존재하더라도 삭제하지 않고 새로운 구조의 뷰로 변경(REPLACE)할 수 있다.

  • FORCE
    FORCE를 사용하면, 기본 테이블의 존재 여부에 상관 없이 뷰를 생성한다.

  • WITH CHECK OPTION
    WITH CHECK OPTION을 사용하면, 해당 뷰를 통해서 볼 수 있는 범위 내에서만 UPDATE 또는 INSERT가 가능하다.

  • WITH READ ONLY
    WITH READ ONLY를 사용하면 해당 뷰를 통해서는 SELECT만 가능하며
    SELECT이외의 데이터 조작어(DML)인 INSERT/UPDATE/DELETE를 할수 없다.

  • 예제

CREATE VIEW EMP_VIEW30
AS
SELECT EMPNO,ENAME,DEPTNO
FROM EMP_COPY
WHERE DEPTNO=30;

위 예제는 WITH READ ONLY 옵션을 사용하지 않았기 때문에 EMP테이블을 카피한 EMP_COPY 테이블로 부터 EMPNO, ENAME, DEPTNO를 보여주는 뷰를 생성하는 예제이다.

뷰 삭제 - DROP VIEW

뷰는 실체가 없는 가상 테이블 이기 때문에 뷰를 삭제한다는 것은 USER_VIEES 데이터에 딕셔너리에 저장되어 있는 뷰의 정의를 삭제하는 것을 의미한다
따라서 뷰를 삭제해도 뷰를 정의한 기본 테이블의 구조나 데이터에는 전혀 영향을 주지 않는다.

  • 예제
DROP VIEW EMP_VIEW30;
profile
한준수
응애에요
이전 포스트

셀프조인

다음 포스트

그룹 함수

0개의 댓글