-
INSERT , UPDATE, DELETE 에서 서브쿼리
서브쿼리는 메인 쿼리의 컬럼을 모두 사용할 수 있다.
메인 쿼리는 서브 쿼리의 컬럼을 사용할 수 없다.
질의 결과에 서브쿼리 컬럼을 표시해야 한다면 조인 방식으로 변환하거나, 함수, 스칼라 서브 쿼리 등을 사용해야한다.
서브 쿼리는 서브 쿼리 레벨과는 상관없이 항상 메인 쿼리 레벨로 결과 집합이 생성된다.
만약 메인 쿼리로 조직(1) , 사원(M) 을 사용하면 결과 집합은 조직(1) 레벨이다.
하지만 조인 같은 경우 조직(1) * 사원(M)을 해 결과 집합은 사원(M) 집합이 생성된다.
만약 결과가 조직 레벨이고 사원 테이블에서 체크해야 할 조건이 존재한다면,
서브 쿼리 방식을 사용해야한다.
만약 이때 조인쿼리로 잘못 선택한다면 결과가 사원집합이니, DISTICT 를 써서 조직 레벨로 만들 수는 있을 것이다.
서브쿼리 사용 시 주의점
1. 괄호로 감싸서 기술한다.
2. 단일 행 혹은 복수 행을 반환한다.
3. 중첩 서브 쿼리 및 스칼라 서브 쿼리에서는 ORDER BY 를 사용할 수 없다.
동작 방식에 따른 서브쿼리 분류
-비연관 서브 쿼리 : 서브 쿼리가 메인 쿼리 컬럼을 갖고 있지 않는다. 메인 쿼리에 값을 제공하기 위해 주로 사용
-연관 서브 쿼리 : 서브쿼리가 메인 쿼리 컬럼을 갖고 있는 형태이다. 보통 메인 쿼리가 먼저 수행된 데이터를 서브쿼리에서 조건이 맞는지 확인할 때 사용한다.
서브쿼리는 메인 쿼리에 종속적인 관계이다. 따라서 논리 실행 순서는 항상 메인 쿼리 데이터가 나오고 서브 쿼리에서 그를 확인하는데, 실제 서브쿼리 실행 순서는 상황에 따라 달라질 수 있다.
반환되는 형태에 따른 서브쿼리 분류
- 단일 행 서브 쿼리
1건만 반환한다.단일 행 비교 연산자, =,<,>,>= 등과 사용한다.
예) 정현수 선수가 소속한 팀에 있는 선수들을 찾는다.
SELECT PLAYER_NAME FROM PLAYER WHERE TEAM_ID = (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME='정현수');- 다중 행 서브 쿼리
여러 건을 반환한다. 다중 행 비교 연산자, IN , ALL, ANY, SOME, EXISTS 등과 사용한다.
예) 정현수 선수가 소속된 팀 정보를 출력한다.
SELECT TEAM_NAME,REGION_NAME FROM TEAM WHERE TEAM_ID IN (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME='정현수');- 다중 컬럼 서브 쿼리
여러 컬럼을 반환한다. 메인 쿼리의 조건절에 여러 컬럼을 비교할 수 있다.
비교하고자 하는 컬럼 개수와 컬럼 위치가 동일해야 한다. SQL SERVER 에서는 지원되지 않는다.
예) 소속팀별 키가 가장 작은 사람을 출력한다.
SELECT PLAYER_NAME,TEAM_ID, HEIGHT FROM PLAYER WHERE (TEAM_ID,HEIGHT) IN (SELECT TEAM_ID, MIN(HEIGHT) FROM PLAYER GROUP BY TEAM_ID);- 연관서브쿼리(correlated)
서브 쿼리 내에서 메인 쿼리 컬럼이 사용된 서브 쿼리이다.
위에 단일행, 다중 행서브쿼리는 서브쿼리의 결과를 메인쿼리가 이용한다.
그러나 연관서브쿼리는 서브쿼리가 메인쿼리의 값을 이용하고 (A.TEAM_ID), 구해진 서브쿼리의 값을 다시 메인쿼리가 다시 이용하게 된다.
예) 선수 자신이 속한 팀의 평균키보다 작은 선수들의 정보를 출력한다.
SELECT A.PLAYER_NAME, B.TEAM_NAME FROM PLAYER A, TEAM B WHERE A.HEIGHT < ( SELECT AVG(HEIGHT) FROM PLAYER X WHERE X.TEAM_ID=A.TEAM_ID GROUP BY X.TEAM_ID ) AND B.TEAM_ID=A.TEAM_ID ORDER BY PLAYER_NAME;EXISTS 서브쿼리는 항상 연관 서브 쿼리로 사용된다.
EXISTS 서브쿼리는 아무리 조건을 만족하는 건이 여러 건이더라도 만족하는 1건을 찾으면 추가 검색을 멈춘다.
SELECT A.STADIUM_ID , A.STADIUM_NAME FROM STADIUM A WHERE EXISTS ( SELECT 1 FROM SHEDULE X WHERE X.STADIUM_ID = A.STADIUM_ID AND X.SCHEDULE_DATE BETWEEN '2020501' AND '20200601' );*IN 과 EXISTS 동작 방식의 차이
IN 실제 존재(일치)하는 데이터들의 모든 값까지 확인
1. 서브쿼리를 먼저 실행해 생성되는 모든 ROW 를 가져옴
2. 메인쿼리에서 하나의 ROW 를 가져옴
3. 메인 쿼리의 ROW 값이 서브쿼리 IN 이하의 요소에 포함되는지 확인하는데, 요소 중 하나라도 일치한다면 ROW 출력
4. 2~3 번 반복
EXISTS 해당 ROW 가 존재하는지만 확인하고 더 수행하지 않음
1. 메인 쿼리를 싱행해 상위 ROW 가져옴
2. 1번 ROW 에 대해 서브쿼리를 실행한다. 서브쿼리에 대한 결과가 존재하기만 하면 TRUE 반환하면 1번 ROW 를 출력한다.
3. 1~2 번 반복
SELECT 절에 서브 쿼리 사용하기
스칼라 서브쿼리는 한 행, 한 컬럼만을 반환하는 서브 쿼리를 말한다.
스칼라 서브쿼리는 컬럼을 쓸 수 있는 대부분의 곳에서 사용할 수 있다.
예) 선수 한 명의 정보와 함께 선수 소속팀의 평균키를 함께 출력
SELECT A.PLAYER_NAME , A.HEIGHT , ROUND((SELECT AVG(X.HEIGHT) FROM PLAYER X WHERE X.TEAM_ID=A.TEAM_ID),3) AS AVG_HEIGHT FROM PLAYER A;
From 절에 서브 쿼리 사용하기
FROM 절에서 사용되는 서브쿼리를 인라인 뷰라고 한다.
인라인 뷰를 사용하면 서브쿼리의 결과를 마치 테이블처럼 사용할 수 있다.
뷰와의 차이점은 뷰는 SELECT 문을 객체로 저장해 테이블처럼 사용하나,
인라인 뷰는 쿼리내에서 즉시 처리된다는 것이다.
예) 케이리그 선수 중 포지션이 MF 인 선수들의 소속 팀과 선수 정보를 출력한다.
SELECT B.TEAM_NAME ,A.PLAYER_NAME ,A.BACK_NO FROM ( SELECT TEAM_ID,PLAYER_NAME,BACK_NO FROM PLAYER WHERE POSITION='MF' ) A ,TEAM B WHERE B.TEAM_ID = A.TEAM_ID ORDER BY PLAYER_NAME;
HAVING 절에서 서브쿼리 사용하기
HAVING 절은 집계함수와 함께 사용될 때 그루핑된 결과에 대해 부가적인 조건을 주기 위해 사용한다.
예) 평균키가 삼성팀 평균팀보다 작은 팀의 팀 이름과 평균키를 출력한다.
SELECT A.TEAM_ID ,B.TEAM_NAME ,ROUND(AVG(A.HEIGHT),3) AS AVG_HEIGHT FROM PLAYER A, TEAM B GROUP BY A.TEAM_ID,B.TEAM_NAME HAVING AVG(A.HEIGHT) < ( SELECT AVG(X.HEIGHT) FROM PLAYER X WHERE X.TEAM_ID IN (SELECT TEAM_ID FROM TEAM WHERE TEAM_NAME='삼성') );
서브쿼리를 이용한 다중 행 INSERT 문
INSERT 문에 서브쿼리를 사용하면 서브 쿼리의 결과를 테이블에 입력할 수 있다.
서브 쿼리의 결과가 다중 행이면, 한 번에 여러 값이 입력된다.
단, INTO 절의 컬럼명 개수와 서브 쿼리의 SELECT 절 컬럼 개수가 일치해야한다.
INSERT INTO TEAM (TEAM_ID, REGION_NAME, TEAM_NAME, ORIG_YYYY, STADIUM_ID) SELECT REPLACE(TEAM_ID, 'K', 'A') AS TEAM_ID ,REGION_NAME ,REGION_NAME || '올스타' AS TEAM_NAME ,2019 AS ORIG_YYYY ,STADIUM_ID FROM TEAM WHRE REGION_NAME IN ('성남','인천');UPDATE 문의 SET 절에 서브쿼리
UPDATE 문의 SET절에 서브 쿼리를 사용하면, 서브쿼리의 결과로 값이 수정된다.
이렇게 할 때, 서브쿼리 결과가 NULL 이면, 해당 컬럼의 값이 NULL 로 될 수 있어 주의해야 한다!
UPDATE TEAM A SET A.ADDRESS = ( SELECT X.ADDRESS FROM STADIUM X WHERE X.HOMETEAM_ID=A.TEAM_ID) WHERE A.ORIG_YYYY > 2000;UPDATE 문 WHERE 절에 서브쿼리를 사용해, 수정될 행을 식별할 수도 있다.
UPDATE STADIUM A SET ( A.DDD, A.TEL ) = ( SELECT X.DDD,X.TEL FROM TEAM X WHERE X.TEAM_ID=A.HOMETEAM_ID) WHERE EXISTS ( SELECT 1 FROM TEAM X WHERE X.TEAM_ID = A.HOMETEAM_ID );DELETE 문에서 서브쿼리
DELETE 문에서 WHERE 절에서 서브쿼리를 이용하면 , 다른 테이블을 참조해 삭제할 행을 식별할 수 있다.
DELETE PLAYER A WHERE EXISTS ( SELECT 1 FROM TEAM X WHERE X.TEAM_ID=A.TEAM_ID AND X.ORIG_YYYY < 1990);
출처
SQL 전문가 가이드 - 한국데이터산업진흥원
반응형