[오라클/SQL] 08. SET, SUBQUERY, 테이블의 연결
목차
01. SET 연산자
SET : 두 개 이상의 테이블에서 JOIN을 사용하지 않고 연관된 데이터를 조회해 오는 방법
SET 연산자
여러 개의 SQL 문을 연결하여 조회한 데이터를 결합하는 방식을 사용함 (집합들간의 관계로 표현한다.)
첫번째 SELECT 요소 : (X, Y, Z ) 두번째 SELECT 요소 : ( O, P, Q, Z ) 일 경우,
| SET 연산자 | 연산자 의미 | 결과값 |
| UNION | 여러 개의 SQL문의 결과에 대한 합집합 | X,Y,Z,O,P,Q |
| UNION ALL | 여러 개의 SQL문의 결과에 대한 합집합과 공통 부분을 더한 합집합 |
X,Y,Z,Z,O,P,Q |
| INTERSECT | 여러 개의 SQL문의 결과에 대한 교집합 | Z |
| MINUS | 앞의 SQ문 결과에서 뒤 SQL문 결과의 차집합 | X,Y |
SET 사용 형태
SELECT 칼럼명1, 칼럼명2,...
FROM 테이블명1
[WHERE 조건식]
[GROUP BY 칼럼이나 표현식
[HAVING 그룹조건식]]
SET 연산자
SELECT 칼럼명1, 칼럼명2,...
FROM 테이블명2
[WHERE 조건식]
[GROUP BY 칼럼이나 표현식
[HAVING 그룹조건식]]
[ORDER BY 1,2 [ASC 또는 DESC]] ;- SET 연산자는 단순한 두개의 SQL문을 SET 연산자로 연결한 것이기 때문에, 마지막 줄에 ORDER BY절을 한번만 기술할 수 있다.
02. SET - UNION
UNION을 사용하기 위해서는 조회하는 칼럼들의 개수가 맞아야 한다. UNION 연산자는 ORDER BY절과 같은 결과를 보여준다.
- 즉, ORDER BY 절을 이용하지 않아도, 자동으로 오름차순으로 보여준다.
예제
TEAM_ID가 K02인 선수들의 정보와, 전체 골키퍼들의 정보를 선수명,등번호,포지션 순으로 조회하라. 단, UNION 연산자를 사용해서 SQL문을 작성하라.
SELECT PLAYER_NAME, BACK_NO, POSITION
FROM PLAYER
WHERE TEAM_ID='K02'
UNION
SELECT PLAYER_NAME, BACK_NO, POSITION
FROM PLAYER
WHERE POSITION='GK';
실행결과의 일부
03. SET - UNION ALL
- 앞의 SQL문 결과와 뒤의 SQL문의 결과를 붙여주는 역할을 한다.
- UNION ALL 연산자는, 앞뒤에 있는 SELECT문을 UNION한 후 교집합에 해당하는 레코드를 한 번 더 보여준다.
예제
TEAM_ID가 K02인 선수들의 정보와, 전체 골키퍼들의 정보를 선수명,등번호,포지션 순으로 조회하라. 단, UNION ALL 연산자를 사용해서 SQL문을 작성하라.
SELECT PLAYER_NAME, BACK_NO, POSITION
FROM PLAYER
WHERE TEAM_ID='K02'
UNION ALL
SELECT PLAYER_NAME, BACK_NO, POSITION
FROM PLAYER
WHERE POSITION='GK';
* UNION 예제와의 차이는 UNION이냐 UNION ALL이냐의 한 줄 차이 밖에 없다. 그러나 조회 레코드를 보면 UNION보다 UNION ALL에서 조회된 레코드가 더 많은데, 이는 두 SELECT문에서 겹치는 부분이 있기 때문이고(교집합), 겹치는 개수만큼 결과가 더 출력되었기 때문이다. 즉, 골키퍼를 두 번 보여주고 있는 것이다.
* UNION은 ORDER BY가 되고, UNION ALL은 그냥 결과가 출력되는 이유 :
UNION 연산자는 중복되는 부분을 제거하여 말 그대로의 합집합 결과를 출력하는 것이고, UNION ALL은 중복되는 것을 허용하기 때문에 정렬한 필요가 없어 SQL문들의 결과에 결과를 덧붙여서 데이터를 출력하기 때문이다.
04. SET - INTERSECT, MINUS
INTERSECT
INTERSECT는 교집합 부분만 보여준다.
예제
SELECT PLAYER_NAME, BACK_NO, POSITION
FROM PLAYER
WHERE TEAM_ID='K02'
INTERSECT
SELECT PLAYER_NAME, BACK_NO, POSITION
FROM PLAYER
WHERE POSITION='GK'
MINUS
MINUS는 앞 SQL문의 결과에서 뒤의 SQL문의 결과를 뺀 후 보여준다.
예제
SELECT PLAYER_NAME, BACK_NO, POSITION
FROM PLAYER
WHERE TEAM_ID='K02'
MINUS
SELECT PLAYER_NAME, BACK_NO, POSITION
FROM PLAYER
WHERE POSITION='GK'
05. SUBQUERY
SUBQUERY란?
SELECT문 안에 포함되어 있는 또 다른 SELECT 문을 말하는 것으로,
알려지지 않은 기준에 의한 데이터 검색을 위해서 사용된다.
한 가지 정보를 조회하기 위해 2번 이상 SQL문을 작성한다면 번거로울 수 있는데,
SUBQUERY를 이용하면 SQL문을 합쳐 한 개의 SQL문으로 작성할 수 있으므로 조금 더 편해진다.
SUBQUERY 작성방법
- ⓐ SUBQUERY는 괄호로 묶어서 그 안에서 사용되어야 한다.
- ⓑ 단일행 또는 복수행 비교연산자들이 SUBQUERY에 사용된다.
- ⓒ SUBQUERY는 많은 SQL 명령에서 사용 가능하다.
- ⓓ SUBQUERY는 ORDER BY절을 포함할 수 없다.(ORDER BY 절은 Main Query의 마지막 문장에 위치해야 한다.)
SUBQUERY를 사용할 수 있는 절
- WHERE절
- HAVING절
- INSERT 문의 INTO절
- UPDATE문장의 SET절
- SELECT 또는 DELETE문의 FROM절
SUBQUERY의 분류
ⓐ 동작 방식에 따른 SUBQUERY 분류
| SUBQUERY 종류 | 설명 |
| NESTED SUBQUERY |
-SUBQUERY가 Main QUERY가 실행되기 전에 단 한 번 수행됨 -이 때 수행된 SUBQUERY의 결과 값이 MAIN QUERY에 제공되는 형태로 SUBQUERY가 Main Query의 제공자의 역할을 하게 되는 형태를 띔 |
| CORRELATED(상호연관) SUBQUERY |
-SUBQUERY내에 Main Query의 칼럼들이 사용되므로, MainQuery의 각 행에 대해 마지막 행에 도달할 때까지 SUBQUERY가 매번 실행되는 형태 -SUBQUERY가 확인자의 역할을 수행하는 형태 -SUBQUERY가 Main Query의 데이터 행수만큼 실행되므로 심각한 오버헤드 발생 |
ⓑ 리턴되는 데이터 형태에 따른 SUBQUERY 분류
| SUBQUERY 종류 | 설명 |
| SINGLE ROW SUBQUERY (단일행 SUBQUERY) |
-SUBQUERY의 실행 결과인 단 하나의 행이 Main Query에 제공되는 형태 -단일행 비교연산자 이용 |
| MULTI ROW SUBQUERY (다중행 SUBQUERY) |
-SUBQUERY의 실행 결과인 여러 행이 Main Query에 제공되는 형태 -다중행 비교연산자(IN, ANY, ALL)를 사용 |
| MULTI COLUMN SUBQUERY (다중칼럼 SUBQUERY) |
-SUBQUERY의 실행 결과가 여러 칼럼이 반환되므로 Main Query의 조건절에 여러 개의 칼럼을 동시에 비교하는 경우 -pair-wise 되었다고 말함 |
05-1. SINGLE ROW SUBQUERY(한 줄만 있는 SUBQUERY)
SINGLE ROW 비교 연산자 사용 가능 (=, <, <=, >, >=, <>)
예제 1
특정 선수가 소속된 팀코드를 알아내보기. 하성룡 선수와 같은 팀의 선수정보를 선수명, 등번호, 포지션 순으로 조회하라.
SELECT PLAYER_NAME, BACK_NO, POSITION
FROM PLAYER
WHER TEAM_ID=(SELECT TEAM_ID
FROM PLAYER
WHERE PLAYER_NAME='하성룡');
SINGLE ROW SUBQUERY에서도 그룹함수를 사용할 수 있는데, 이유는 무엇일까?
- ⓐ 테이블 전체가 하나의 그룹으로 적용될 경우 그룹함수를 사용할 수 있는데,
- ⓑ 이는 결과값이 단 하나로 출력되기 때문에 SINGLE ROW SUBQUERY로 사용 가능한 것이다.
- ⓒ 주의할 점은, SUBQUERY로 작성된 SELECT문이 반드시 1개의 ROW로 조회가 되어야 한다는 점.
예제 2
전체 선수들 중 평균키보다 작은 선수들의 정보를 선수명, 등번호, 포지션 순으로 조회하라.
SELECT PLAYER_NAME, BACK_NO, POSITION
FROM PLAYER
WHERE HEIGHT < (SELECT AVG(HEIGHT)
FROM PLAYER);
05-2. MULTI ROW SUBQUERY (다중행 쿼리)
SINGLE ROW SUBQUERY는 결과가 2개 이상이 나오면 오류가 나오지만, MULTI ROW SUBQUERY는 결과가 2개 이상이 나올 수 있다.
- 예를 들어, 동명이인인 사람이 여러 팀에 걸쳐서 있다고 하면, 05-1의 첫번째 ex는 오류를 반환할 것이다.
- SUBQUERY의 실행 결과가 여러 개의 행이 나오는 모든 경우에는, MULTI ROW SUBQUERY를 이용해야 한다.
| 다중행(multi row) 연산자 | 설명 |
| IN (SUBQUERY) | -SUBQUERY의 결과값에 있는 임의의 값과 동일한 조건을 의미 |
| ANY (SUBQUERY) | -SUBQUERY의 결과값에 있는 각 값과 비교하는 조건을 의미 -SUBQUERY의 결과로 리턴된 어느 결과값보다 작은 데이터를 모두 조회 -논리연산자의 OR과 같은 의미 -즉, MAIN QUERY의 조건이 SUBQUERY의 값과 하나 이상 만족하면, 참이 되어 조회가 된다. |
| ALL (SUBQUERY) | -SUBQUERY의 결과값에 있는 모든 값과 비교하는 조건을 의미 -비교연산자에 ">"를 사용하면 결과 값들 모두를 만족해야 하므로, 최대값보다 큰 모든 조건을 만족한다는 의미 |
예제
ex1) 정현수 선수와 같은 팀의 선수정보를 팀코드, 선수명, 등번호, 포지션 순으로 조회하라.
SELECT TEAM_ID, PLAYER_NAME, BACK_NO, POSITION
FROM PLAYER
WHERE TEAM_ID IN (SELECT TEAM_ID
FROM PLAYER
WHERE PLAYER_NAME='정현수');
ex2) 선수들 중 포지션이 포워드인 어떤 선수보다 키가 큰 다른 포지션의 선수 정보를, 팀 코드, 선수명, 등번호, 포지션 순으로 조회하라.
SELECT TEAM_ID, PLAYER_NAME, BACK_NO, POSITION, HEIGHT
FROM PLAYER
WHERE POSITION <> 'FW'
AND HEIGHT > ANY(SELECT HEIGHT
FROM PLAYER
WHERE POSITION='FW')포워드들 중에서 단 한명헤게라도 키가 크면 해당 선수는 조회된다. 따라서, 포워드 중 키가 제일 작은 선수보다 키가 큰 선수를 조회하는 예제이다.
ex3) 선수들 중 포지션이 포워드인 모든 선수보다 키가 큰 다른 포지션의 선수 정보를, 팀 코드, 선수명, 등번호, 포지션 순으로 조회하라.
SELECT TEAM_ID, PLAYER_NAME, BACK_NO, POSITION, HEIGHT
FROM PLAYER
WHERE POSITION <> 'FW'
AND HEIGHT > ALL(SELECT HEIGHT
FROM PLAYER
WHERE POSITION='FW')포워드 중 키가 제일 큰 선수보다 키가 큰 선수를 조회하는 예제이다.
05-3. MULTI COLUMN SUBQUERY
한 개의 컬럼만 조회해서 MAIN QUERY와 비교하는 SINGLE ROW SUBQUERY, MULTI ROW SUBQUERY와 달리, MULTI COLUMN SUBQUERY는 SUBQUERY의 실행 결과에 여러 개의 칼럼이 반환되어 MAIN QUERY의 조건절에 사용되어질 때,
그 칼럼들 전체를 동시에 비교하는 경우를 말한다.
- 즉, MULTI COLUMN SUBQUERY는 2개 이상의 칼럼을 SUBQUERY에서 조회하여 MAIN QUERY와 비교하는 것이다.
- WHERE절의 비교될 칼럼도 MULTI로 작성한다.
예제
팀 별로 키가 가장 작은 선수를 팀 코드, 선수명, 등번호, 포지션 순으로 조회하라.
SELECT TEAM_ID, PLAYER_NAME, BACK_NO, POSITION
FROM PLAYER
WHERE (TEAM_ID, HEIGHT) IN (SELECT TEAM_ID, MIN(HEIGHT)
FROM PLAYER
GROUP BY TEAM_ID);
05-4. CORRELATED SUBQUERY
05-1 ~ 05-3 에서의 서브쿼리는 서브쿼리의 결과가 메인쿼리에 제공되는 제공자 역할을 했지만, CORRELATED SUBQUERY는 서브쿼리 내에서 메인 쿼리의 칼럼들을 사용할 수 있다.
예제
각 팀 별로 각 팀의 평균키보다 작은 선수들의 정보를 팀 코드, 선수명, 등번호, 포지션, 키 순으로 조회하라.
SELECT TEAM_ID, PLAYER_NAME, BACK_NO, POSITION, HEIGHT
FROM PLAYER MP
WHERE HEIGHT<(SELECT AVG(HEIGHT)
FROM PLAYER SP
WHERE SP.TEAM_ID=MP.TEAM_ID
GROUP BY SP.TEAM_ID)
ORDER BY TEAM_ID;메인쿼리에서 현재 조회하는 레코드의 팀 코드인 MP.TEAM_ID를 서브쿼리에서 가져와서 해당 팀만 서브쿼리에서 살펴보게 된다.실행 과정은
- 첫째, 메인 쿼리가 수행되어 한 행을 읽어서 그 선수의 소속팀 코드를 얻음.
- 둘째, 서브 쿼리를 실행해서 해당 선수의 소속팀 평균키를 구함.
- 셋째, 서브 쿼리에서 구한 평균키를 해당 선수의 키와 비교해서 평균보다 작을 경우 출력
- 넷째, 이런 일련의 과정을 메인 쿼리의 마지막 행까지 반복
> CORRELATED SUBQUERY는 SQL문의 처리 속도가 떨어지기 때문에 사용을 자제하는 것이 좋다.
06. HAVING절에서의 서브쿼리
WHERE 절이 아닌 HAVING절에서도 서브쿼리를 쓸 수 있다.
예제
팀 코드 K02인 팀의 평균키보다 작은 팀을 팀 코드, 평균키 순으로 조회하라.
SELECT TEAM_ID, AVG(HEIGHT)
FROM PLAYER
GROUP BY TEAM_ID
HAVING AVG(HEIGHT) < (SELECT AVG(HEIGHT)
FROM PLAYER
WHERE TEAM_ID='K02' );
07. FROM절에서의 서브쿼리 ( = INLINE VIEW)
FROM 절에는 원래는 테이블명이 들어가지만, FROM절에 서브쿼리를 작성하게 되면 서브쿼리에 의해 나온 결과를 마치 테이블인 것처럼 사용하겠다는 의미가 된다. 즉, FROM절에 사용되는 서브쿼리가 하나의 테이블인 것처럼 동작된다.
- FROM 절에 사용되는 서브쿼리를 인라인 뷰라고도 하는데, 이 인라인 뷰는 SQL문 속에서 SQL문이 실행될 때만 임시적으로 생성되는 것으로, 일반적인 VIEW TABLE과 달리 데이터베이스에 생성되어 있지 않다.
예제
팀 코드 K02인 팀의 선수정보를 팀명, 선수명, 등번호, 포지션 순으로 조회하라.
SELECT TEAM_NAME, PLAYER_NAME, POSITION
FROM(SELECT P.TEAM_ID, T.TEAM_NAME, P.PLAYER_NAME, P.POSITION
FROM PLAYER P, TEAM T
WHERE P.TEAM_ID=T.TEAM_ID) MT
WHERE MT.TEAM_ID='K02';- 팀 명은 PLAYER테이블에 없기 때문에, TEAM 테이블과의 JOIN을 통해서만 조회가 가능하다.
- 조인을 통해 팀 정보와 선수 정보를 모두 조회가 가능한 가상의 테이블인 인라인 뷰를 만들어 MT라는 ALIAS로 지정한다
- 괄호()안의 SELECT 문을 통해서 MT라는 가상의 테이블이 생긴거고,
- 이 가상의 테이블에는 TEAM_ID, TEAM_NAME, PLAYER_NAME, POSITION이라는 칼럼이 존재하게 된다.
- 이 가상의 테이블에서 필요 정보를 조회하게 된다.
08. TOP-N SUBQUERY
졍렬된 데이터를 만드는 SUBQUERY나 INLINE VIEW에서 나온 결과의 행수를 제한하는 역할을 ROWNUM을 사용하여 수행하는 서브쿼리를 TOP-N SUBQUERY 라고 한다.
- 일반적인 SELECT 문에서도 사용 가능하다.
- 조회된 결과의 레코드 수가 많을 경우에, SQL문이 제대로 동작하는지 구동시켜보면 결과를 살펴보는데 많은 시간이 소요되는데,
- 이럴 경우 TOP-N SUBQUERY를 자주 이용한다.
예제
팀 코드 K02인 팀의 선수 정보를 팀명, 선수명, 등번호, 포지션 순으로 조회하라.
SELECT TEAM_NAME, PLAYER_NAME, POSITION
FROM(SELECT P.TEAM_ID, T.TEAM_NAME, P.PLAYER_NAME, P.POSITION
FROM PLAYER P, TEAM T
WHERE P.TEAM_ID=T.TEAM_ID) MT
WHERE MT.TEAM_ID='K02';
AND ROWNUM <= 10;- ROWNUM을 활용해서 조회되는 레코드가 레코드 순서 10번째 이하까지만 조화하라는 뜻(10번쨰 이상만 조회하려면, >=)
- ROWNUM은 SQL문의 실행결과 나오는 행들에 1부터 시작하는 순차적인 숫자 값을 할당 받게 된다.
09. CREATE, INSERT, UPDATE, DELETE 문에서의 SUBQUERY
ⓐ CREATE 문에서의 SUBQUERY
이미 존재하는 테이블에서 필요한 데이터만 복사해서 테이블을 생성할 수 있다면, 시간이 많이 소요되는 별도의 데이터 입력작업이 필요 없을 것이다. 이렇듯 테이블을 생성할 때 서브쿼리를 사용해 테이블의 구조와 데이터를 복사할 수 있는 방법이 바로 서브쿼리를 이용하는 것이다.
예제
K-리그에 등록된 선수 전체를 대상으로 하는 테이블을 이용해, 특별히 팀코드가 K02인 특정 팀만의 선수들만 관리하는 테이블을 생성하고 싶다면?
CREATE TABLE PLAYER_K00(PLAYER_ID, PLAYER_NAME, TEAM_ID, POSITION, BACK_NO)
AS (SELECT PLAYER_ID, PLAYER_NAME, TEAM_ID, POSITION, BACK_NO
FROM PLAYER
WHERE TEAM_ID='K02');
ⓑ INSERT 문에서의 SUBQUERY
기존 테이블에서 필요한 데이터만을 복사하여 새로운 테이블을 저장할 때 사용한다.
예제
ⓐ의 예제에서 생성한 PLAYER_K00테이블에 팀 코드 K07인 팀에 소속된 선수들의 정보를 INSERT
INSERT INTO PLAYER_K00
(SELECT PLAYER_ID, PLAYER_NAME, TEAM_ID, POSITION, BACK_NO
FROM PLAYER
WHERE TEAM_ID='K07');
ⓒ UPDATE 문에서의 SUBQUERY
기존의 SINGLE ROW SUBQUERY와 동일한 개념
예제
하태근 선수의 소속을 찌코 선수와 같은 팀으로 이동하기
UPDATE PLAYER
SET TEAM_ID=(SELECT TEAM_ID
FROM PLAYER_K00
WHERE PLAYER_NAME='찌코')
WHERE PLAYER_NAME='하태근';
![[오라클/SQL] 08. SET, SUBQUERY, 테이블의 연결](https://blog.kakaocdn.net/dn/bx0ljt/btssihNbzc5/SXQ3ukFsSP1JHxJNKsqWvk/img.png)
