SQL-집합, 서브쿼리, 아우터
태그 :
- 개념
- 서브 쿼리의 정의 하나의 SQL문 안에 포함되어 있는 또 다른 SQL문. 메인 쿼리에 포함되는 종속적인 관계의 쿼리
1. 서브 쿼리(Subquery)의 개요
가. 서브 쿼리의 정의
하나의 SQL문 안에 포함되어 있는 또 다른 SQL문. 메인 쿼리에 포함되는 종속적인 관계의 쿼리
나. 서브 쿼리의 특징
SQL 문이 괄호(~~)로 묶여져 있으면 서브쿼리. 큰 개념의 조인에 포함시킬 수도 있으나, 보통은 조인과 구분함
2. 서브 쿼리의 종류
구분 |
종류 |
설명 |
위치별 |
Nested Subquery |
Where절에 위치하고, 가장 먼저 개발됨 |
Inline View |
FROM절에 위치하고, SQL 내 절차성 효과 |
|
Scalar Subquery |
SELECT절에 위치하고, 가장 최근에 개발됨 |
|
반환데이터 형태별 |
단일행 서브쿼리 |
서브쿼리의 실행결과가 항상 1건 이하인 서브쿼리. 단일행 비교연산자 (=, <, <=, >, >=, <>)와 함께 사용된다. |
다중행 서브쿼리 |
서브쿼리의 실행결과가 여러 건인 서브쿼리. 다중행 비교연산자 (IN, ALL, ANY, SOME, EXISTS)와 함께 사용된다 |
|
다중칼럼 서브쿼리 |
서브쿼리의 실행결과로 여러 칼럼을 반환한다. 메인쿼리의 조건절에 여러 칼럼을 동시에 비교할 수 있다. 서브쿼리와 메인쿼리에서 비교하고자 하는 칼럼개수와 위치가 동일함 |
|
동작방식별 |
비연관 서브쿼리 |
서브쿼리가 메인쿼리 칼럼을 가지고 있지 않는 형태의 서브쿼리. 메인쿼리에 값(서브쿼리가 실행된 결과)을 제공하기 위한 목적으로 주로 사용됨 |
연관 서브쿼리 |
서브쿼리가 메인쿼리의 칼럼을 사용하는 형태의 서브쿼리. 메인쿼리가 먼저 수행되어 읽혀진 데이터를 서브쿼리에서 조건이 맞는지 확인하고자 할 때 사용된다. |
3. 서브 쿼리의 상세 설명 및 예시
가. 단일행 서브쿼리
- 서브쿼리가 단일행 비교 연산자((=, <, <=, >, >=, <>)와 함께 사용할 때는 서브쿼리의 결과 건수가 반드시 1건 이하여야 한다. 만약, 서브쿼리의 결과 건수가 2건 이상을 반환하면 SQL문은 실행시간(Run Time)오류가 발생한다. 이런 종류의 오류는 컴파일 할 때(Compile Time)는 알 수 없는 오류이다.
SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버 FROM PLAYER_T WHERE TEAM_ID = (SELECT TEAM_ID FROM PLAYER_T WHERE PLAYER_NAME = ‘김남일’) ORDER BY PLAYER_NAME; |
-김남일 선수가 동명이인이 없다면 정상적인 수행이 되지만, 동명이인이 다른 팀에 있다면 두 개 이상의 TEAM_ID가 반환되므로 런타임 오류가 발생한다.
서브쿼리의 결과가 2건 이상 반환될 수 있다면, 반드시 다중행 비교 연산자 (IN, ALL, ANY, SOME, EXISTS)와 함께 사용해야 한다.
나. 다중행 서브쿼리
다중행 연산자 |
설명 |
IN (서브쿼리) |
서브쿼리의 결과에 존재하는 임의의 값과 동일한 조건을 의미한다. (Multiple OR조건) |
비교연산자 ALL (서브쿼리) |
서브쿼리의 결과에 존재하는 모든 값을 만족하는 조건을 의미한다. 비교 연산자로 “>”를 사용했다면 메인쿼리는 서브쿼리의 모든 결과값을 만족해야 하므로, 서브쿼리 결과의 최대값보다 큰 모든 건이 조건을 만족한다. |
비교연산자 ANY (서브쿼리), SOME |
서브쿼리의 결과에 존재하는 어느 하나의 값이라도 만족하는 조건을 의미한다. 비교 연산자로 “>”를 사용했다면 메인쿼리는 서브쿼리의 값들 중 어떤 값이라도 만족하면 되므로, 서브쿼리 결과의 최소값보다 큰 모든 건이 조건을 만족한다. (SOME은 ANY와 동일함) |
EXISTS (서브쿼리) |
서브쿼리의 결과를 만족하는 값이 존재하는지 여부를 확인하는 조건을 의미한다. 조건을 만족하는 건이 여러 건이더라도 1건만 찾으면 더 이상 검색하지 않는다. |
[예제] 선수들 중에서 ‘정현수’라는 선수가 소속되어 있는 팀 정보를 출력하는 서브쿼리를 작성하면 다음과 같다.
SELECT REGION_NAME 연고지명, TEAM_NAME 팀명, E_TEAM_NAME 영문팀명 FROM TEAM WHERE TEAM_ID = (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = ‘정현수’) ORDER BY PLAYER_NAME;
ORA-01427: 단일행 하위 질의에 2개 이상의 행이 리턴되었습니다. |
[예제] 선수들 중에서 ‘정현수’라는 선수가 소속되어 있는 팀 정보를 출력하는 서브쿼리를 다중행 비교 연산자 in으로 바꾸어서 SQL문을 작성하면 다음과 같다. (동명이인을 확인할 수 있음)
SELECT REGION_NAME 연고지명, TEAM_NAME 팀명, E_TEAM_NAME 영문팀명 FROM TEAM WHERE TEAM_ID IN (SELECT TEAM_ID FROM PLAYER WHERE PLAYER_NAME = ‘정현수’) ORDER BY PLAYER_NAME; |
연고지명 팀명 영문팀명 ---------- ----------------- -------------------------------- 전남 드래곤즈 CHUNNAM DRAGONS FC 성남 일화천마 SEONGNAM ILHWA CHUNMA FC
2개의 행이 선택되었다. |
다. 다중컬럼 서브쿼리
- 다중 컬럼 서브쿼리는 서브쿼리의 결과로 여러 개의 컬럼이 반환되어 메인쿼리의 조건과 동시에 비교되는 것을 의미한다.
[예제] 소속팀별 키가 가장 작은 사람들의 정보를 해본다.
SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE (TEAM_ID, HEIGHT) IN (SELECT TEAM_ID, MIN(HEIGHT) FROM PLAYER GROUP BY TEAM_ID) ORDER BY TEAM_ID, PLAYER_NAME; |
팀코드 선수명 포지션 백넘버 키 ------- -------------- --------- ---------- ------------ K01 마르코스 FW 44 170 K02 박정수 MF 8 170
… |
라. 연관 서브쿼리
-연관 서브쿼리(Correlated Subquery)는 서브쿠리 내에 메인쿼리 칼럼이 사용된 서브쿼리이다.
-[예제] 가비 선수는 삼성블루윙즈팀 소속이므로 삼성블루윙즈팀 소속의 평균키를 구하고 그 평균키와 가비 선수의 키를 비교하여 적을 경우에 선수에 대한 정보를 출력한다. 만약, 평균키보다 선수의 키가 크거나 같으면 조건에 맞지 않기 때문에 해당 데이터는 출력되지 않는다.
-이와 같은 작업을 메인쿼리에 존재하는 모든 행에 대해서 반복 수행한다.
SELECT T.TEAM_NAME 팀명, M.PLAYER_NAME 선수명, M.POSITION 포지션, M.BACK_NO 백넘버, M.HEIGHT 키 FROM PLAYER M, TEAM T WHERE M.TEAM_ID = T.TEAM_ID AND M.HEIGHT < (SELECT AVG(S.HEIGHT) FROM PLAYER S WHERE S.TEAM_ID = M.TEAM_ID AND S.HEIGHT IS NOT NULL GROUP BY S.TEAM_ID) ORDER BY 선수명; |
마. EXISTS 서브쿼리
-EXISTS 서브쿼리는 항상 연관 서브쿼리로 사용된다.
-EXISTS 서브쿼리의 특징은 아무리 조건을 만족하는 건이 여러 건이더라도 조건을 만족하는 1건만 찾으면 추가적인 검색을 진행하지 않는다.
-현업에서 조건을 만족하는지 여부를 묻는 로직이 많이 사용되는데, 성능에 부담이 적은 EXISTS 절을 우선적으로 검토할 필요가 높다.
-EXISTS 연산자의 왼쪽에는 컬럼명이나 상수가 표시되지 않는다. 서브쿼리의 데이터가 필요한 경우가 아니라면, 1, ‘X’ 같은 업무적으로 의미없는 상수값을 선택하는 것이 좋다.
-[예제] EXISTS 서브쿼리를 사용하여 ‘20120501’부터 ‘20120521’사이에 경기가 있는 경기장을 조회한다.
SELECT STADIUM_ID, STADIUM_NAME FROM STADIUM WHERE EXISTS (SELECT 1 FROM SCHEDULE WHERE SCHE_DATE BETWEEN ‘20120501’ AND ‘20120521’) |
바. INLINE VIEW
FROM 절에서 사용되는 서브쿼리를 인라인 뷰(Inline View)라고 한다. FROM절에는 테이블 명이 오도록 되어 있다. FROM절에 사용된 서브쿼리의 결과가 마치 실행 시에 동적으로 생성된 테이블인 것처럼 사용할 수 있다.
인라인 뷰는 SQL문이 실행될 때만 임시적으로 생성되는 동적인 뷰이기 때문에 데이터베이스에 해당 정보가 저장되지 않는다. 그래서 일반적인 뷰를 정적 뷰(Static View) 라고 하고, 인라인 뷰를 동적 뷰(Dynamic View)라고도 한다.
중첩되어 사용될 수 있으며, 일반적으로 가장 안의 인라인 뷰부터 수행이 된다. (SQL 문장 내 절차적 프로그래밍 효과 가짐, 특별한 경우 뷰머지가 발생할 수도 있음)
[예제] 인라인 뷰에 정의된 SELECT 칼럼을 메인쿼리에서 사용할 수 있다.
SELECT EMPNO FROM (SELECT EMPNO, ENAME FROM EMP ORDER BY MGR); 14개의 행이 선택되었다 |
[예제] 인라인 뷰에 미정의된 컬럼은 메인쿼리에서 사용할 수 없다.
SELECT MGR FROM (SELECT EMPNO, ENAME FROM EMP ORDER BY MGR);
SELECT MGR FROM ; * ERROR: “MGR” : 부적합한 식별자 |
사. 스칼라 서브쿼리
-스칼라 서브쿼리는 한 행, 한 칼럼(1Row, 1Column)만을 반환하는 서브쿼리를 말한다. 스칼라 서브쿼리는 칼럼을 쓸 수 있는 대부분의 곳에서 사용할 수 있다.
-스칼라 서브쿼리도 일종의 함수이므로 중첩해서 사용하고, OUTPUT이 두개 이상 나오는 경우나 OUTPUT의 데이터 타입이 맞지 않는 경우 SYNTAX 에러가 발생합니다.
-스칼라 서브쿼리의 경우 FUNCTION(함수)의 특징을 가집니다. 함수는 기능적으로 많은 INPUT이 있더라도 OUTPUT은 일반적으로 하나만 나온다는 것과 성능면에서는 전체 데이터를 일일이 모든 건수만큼 수행해야 한다는 점입니다.
-대량의 데이터 처리시 스칼라 서브쿼리를 남발하는 경우는 조인의 장점이나 집합적 개념을 적용하기 힘드므로, 같은 결과를 얻을 수 있다면 가능하면 스칼라 서브쿼리가 아니라 조인으로 대체하는 것이 좋습니다.
-Select List 항목
SELECT EMPNO, ENAME, (SELECT DNAME FROM DEPT WHERE DEPTNO = A.DEPTNO) DNAME; FROM EMP A; |
SELECT EMPNO, ENAME, (SELECT GRADE FROM SALGRADE WHERE EMP.SAL BETWEEN LOSAL AND HISAL); FROM EMP; |
-스칼라 서브쿼리에서 출력된 값은 Select List의 한 항목 값으로 대체되고, 자료형이 서브쿼리에서 출력된 칼럼의 자료형과 일치하지 않으면, DBMS는 에러를 출력하게 됩니다.
만약 조회된 결과가 없다면, 즉 공집합이라도 Select List의 값은 집계함수와 같이 NULL로 표시됩니다. 두 ROW 이상의 결과가 출력되면 DBMS는 에러를 출력하게 됩니다.
함수의 인자
SELECT EMPNO, ENAME, SUBSTR((SELECT DNAME FROM DEPT WHERE DEPTNO = E.DEPTNO), 1, 3) as DEPARTMENT_NAME; FROM EMP E; |
-WHERE 절의 조건
SELECT EMPNO, ENAME FROM EMP E WHERE (SELECT DNAME FROM DEPT WHERE DEPTNO = E.DEPTNO) = (SELECT DNAME FROM DEPT_2 WHERE DEPTNO = E.DEPTNO); |
-ORDER BY 절
SELECT EMPNO, ENAME, DEPTNO FROM EMP E ORDER BY (SELECT DNAME FROM DEPT WHERE DEPTNO = E.DEPTNO); |
-CASE 조건 절
SELECT EMPNO, ENAME, DEPTNO (CASE WHEN DEPTNO IN (SELECT DEPTNO FROM DEPT) THEN ‘USA’ ELSE ‘OTHER COUNTRY’ END) LOCATION FROM EMP E; |
-CASE 결과 절
SELECT EMPNO, ENAME, DEPTNO (CASE WHEN DEPTNO = 20 THEN (SELECT DNAME FROM DEPT WHERE DEPTNO = 10) ELSE (SELECT DNAME FROM DEPT WHERE DEPTNO = 30) END) as NEW_DEPARTMENT FROM EMP E; |
-HAVING 절
[예제] 삼성블루윙즈팀의 평균키보다 작은 팀의 이름과 해당 팀의 평균키를 구하는 SQL문을 작성하면 다음과 같다.
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.HEIGHT) < (SELECT AVG(HEIGHT) FROM PLAYER WHERE TEAM_ID = ‘K02’) |