[SQL기본 및 활용] 2020년 SQLD 내용 정리 :: SQL 활용 #2

<목차>

• 집합 연산자의 종류

• 예시

---

<< 집합 연산자 >>

* 두 개 이상의 테이블에서 JOIN을 사용하지 않고 연관된 데이터를 조회하는 방법 중에 집합 연산자(Set Operation)를 사용하는 방법이 있다.

* 여러 SQL Query문의 결과를 연결하여 하나로 결합하는 방식을 사용한다.

* SELECT 절의 칼럼 수가 동일하고 SELECT 절의 동일 위치에 존재하는 칼럼의 데이터 타입이 상호 호환이 가능해야 한다. (반드시 동일할 필요는 없다.)

---

 

1. 집합 연산자의 종류

집합 연산자의 종류

집합 연산자의 연산

* UNION ALL 을 제외한 다른 집합 연산자에서는 SQL 문의 결과 집합에서 중복된 데이터 건들을 먼저 배제하는 작업을 수행한 후에 집합 연산을 적용한다.

 

2. 예시

* 아래의 문제들에 대해서 연습을 해볼 것이다.

1. K-리그 소속 선수들 중에서 소속이 '삼성 블루윙즈' 팀인 선수들과 '전남 드래곤즈' 팀인 선수들에 대한 내용을 모두 보고 싶다.

2. K-리그 소속 선수들 중에서 소속이 '삼성 블루윙즈' 팀인 선수들과 포지션이 골키퍼(GK)인 선수들을 모두 보고 싶다.

3. K-리그 소속 선수들에 대한 정보 중에서 포지션별 평균키와 팀별 평균키를 알고 싶다.

4. K-리그 소속 선수들 중에서 소속이 '삼성 블루윙즈' 팀이면서 포지션이 미드필더(MF)가 아닌 선수들의 정보를 보고 싶다.

5. K-리그 소속 선수들 중에서 소속이 '삼성 블루윙즈' 팀이면서 포지션이 골키퍼(GK)인 선수들의 정보를 보고 싶다.

 

* 위의 문제에 대한 SQL 문을 작성하기 전에 집합 연산자를 연습하기 위한 형태로 해석한다.

1. [합집합]

   * K-리그 소속 선수 중 소속이 '삼성 블루윙즈' 팀인 선수들의 집합과

   * K-리그 소속 선수 중 소속이 '전남 드래곤즈' 팀인 선수들의 집합의 합집합

2. [합집합]

   * K-리그 소속 선수 중 소속이 '삼성 블루윙즈' 팀인 선수들의 집합과

   * K-리그 소속 선수 중 포지션이 골키퍼(GK)인 선수들의 집합의 합집합 

3. [합집합]

   * K-리그 소속 선수 중 포지션별 평균키에 대한 집합과

   * K-리그 소속 선수 중 팀별 평균키에 대한 집합의 합집합

4. [차집합]

   * K-리그 소속 선수 중 소속이 '삼성 블루윙즈' 팀인 선수들의 집합과

   * K-리그 소속 선수 중 포지션이 미드필더(MF)인 선수들의 집합의 차집합

5. [교집합]

   * K-리그 소속 선수 중 소속이 '삼성 블루윙즈' 팀인 선수들의 집합과

   * K-리그 소속 선수 중 포지션이 골키퍼(GK)인 선수들의 집합의 교집합

 

* 예시 - 문제 1

   : K-리그 소속 선수들 중에서 소속이 '삼성 블루윙즈' 팀인 선수들과 '전남 드래곤즈' 팀인 선수들에 대한 내용을 모두 출력하라.

 

* 예시 - 결과 1

SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 
FROM PLAYER 
WHERE TEAM_ID = 'K02' 
UNION 
SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 
FROM PLAYER 
WHERE TEAM_ID = 'K07';
 
/*
팀코드 선수명 포지션 백넘버 키 
----- ------ ----- ------ --- 
K02   가비    MF    10    177 
K02   강대희  MF    26    174 
K02   고종수  MF    22    176 
K02   고창현  MF    8     170 
K02   김강진  DF    43    181 
K07   강철    DF    3     178 
K07   김반    MF    14    174 
K07   김영수  MF    30    175 
K07   김정래  GK    33    185 
K07   김창원  DF    5     183 
.....
100개의 행이 선택되었다.
*/

---> 굳이 UNION을 사용하지 않고도 WHERE 절에 IN 이나 OR 키워드를 통해 같은 결과를 출력할 수 있다.

---> 하지만 IN/OR 키워드를 통해 출력하게 되면 결과 출력 순서가 달라질 수도 있기 때문에 ORDER BY 절을 통해 정렬 하는 것이 바람직하다.

 

* 예시 - 문제 2

   : K-리그 소속 선수들 중에서 소속이 '삼성 블루윙즈' 팀인 선수들과 포지션이 골키퍼(GK)인 선수들을 모두 보고 싶다.

 

* 예시 - 결과 2

SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 
FROM PLAYER 
WHERE TEAM_ID = 'K02' 
UNION 
SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 
FROM PLAYER 
WHERE POSITION = 'GK';
 
/*
팀코드 선수명 포지션 백넘버 키 
------ ----- ------ ---- ---- 
K01    권정혁 GK     1    195 
K01    서동명 GK     21   196 
K01    양지원 GK     45   181 
K01    이무림 GK     31   185 
K01    최창주 GK     40   187 
K02    가비   MF     10   177 
K02    강대희 MF     26   174 
K02    고종수 MF     22   176 
K02    고창현 MF     8    170 
K02    김강진 DF     43   181 
......
88개의 행이 선택되었다.
*/

---> 문제 1과 마찬가지로 굳이 UNION을 사용하지 않고 하나의 WHERE 절로도 같은 결과를 출력할 수 있다. 하지만 조건의 칼럼이 다르기 때문에 IN은 사용할 수 없고, OR을 통해서만 가능하다.

 

* 예시 - 문제 3

   : K-리그 소속 선수들에 대한 정보 중에서 포지션별 평균키와 팀별 평균키를 알고 싶다.

 

* 예시 - 결과 3

SELECT 'P' 구분코드, POSITION 포지션, AVG(HEIGHT) 평균키 
FROM PLAYER 
GROUP BY POSITION 
UNION 
SELECT 'T' 구분코드, TEAM_ID 팀명, AVG(HEIGHT) 평균키 
FROM PLAYER 
GROUP BY TEAM_ID 
ORDER BY 1;
/* ORDER BY 1은 SELECT의 칼럼set 중 첫 번째 칼럼에 대해서 정렬한다는 뜻() */
 
/*
구분코드 포지션 평균키 
------- ------ -------- 
P       DF     180.409 
P       FW     179.91 
P       GK     186.256 
P       MF     176.309 
P       TC     178.833 
T       K01    180.089 
T       K02    179.067 
T       K03    179.911 
T       K04    180.511 
T       K05    180.422 
......
23개의 행이 선택되었다.
*/

---> 집합 연산자의 결과에서 두 번째 SELECT 절의 TEAM_ID는 '팀명' 으로 표기되어 있지만 실제로는 '포지션' 으로 출력되었다. 첫 번째 SELECT 절에서 사용된 HEADING이 적용되기 때문이다.

---> 그렇기 때문에 헷갈리지 않게 포지션별/팀별에 따라서 구분코드를 두었다.

 

* 예시 - 문제 4

   : K-리그 소속 선수들 중에서 소속이 '삼성 블루윙즈' 팀이면서 포지션이 미드필더(MF)가 아닌 선수들의 정보를 보고 싶다.

 

* 예시 - 결과 4

SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 
FROM PLAYER 
WHERE TEAM_ID = 'K02' 
MINUS 
SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 
FROM PLAYER 
WHERE POSITION = 'MF' 
ORDER BY 1, 2, 3, 4, 5;
/* TEAM_ID 기준 오름차순, 같으면 PLAYER_NAME 오름차순, 같으면 POSITION 오름차순, ...*/
 
/*
팀코드 선수명   포지션 백넘버 키 
------ ------- ------ ----- ---- 
K02    김강진   DF     43   181 
K02    김관희   FW     39   180 
K02    김만근   FW     34   177 
K02    김병국   DF     2    183 
K02    김병근   DF     3    175 
K02    왕선재   TC 
K02    윤성효   TC 
K02    윤화평   FW     42   182 
K02    이성용   DF     20   173 
K02    정광수   GK     41   182 
.....
31개의 행이 선택되었다.
*/

---> Oracle : MINUS, SQL Server : EXCEPT 키워드 사용

---> 차집합 연산을 하지 않고 하나의 WHERE 절을 통해서도 같은 결과를 출력할 수 있다. TEAM_ID가 'K02' 인 조건과 POSITION이 'MF' 가 아닌 조건을 AND 연산으로 연결해준다.

(WHERE TEAM_ID = 'K02' AND NOT POSITION = 'MF';) 또는

(WHERE TEAM_ID = 'K02' AND POSITION <> 'MF';)

---> 또한 MINUS 연산자는 NOT EXISTS 또는 NOT IN 서브쿼리를 이용한 SQL문으로도 변경이 가능하다. (추후 링크)

SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 
FROM PLAYER X 
WHERE X.TEAM_ID = 'K02' 
AND NOT EXISTS (
                SELECT 1 
                FROM PLAYER Y 
                WHERE Y.PLAYER_ID = X.PLAYER_ID AND POSITION = 'MF'
                ) 
ORDER BY 1, 2, 3, 4, 5;
 
 
SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 
FROM PLAYER 
WHERE TEAM_ID = 'K02' 
AND PLAYER_ID NOT IN (
                    SELECT PLAYER_ID 
                    FROM PLAYER 
                    WHERE POSITION = 'MF'
                ) 
ORDER BY 1, 2, 3, 4, 5;
 
/*
팀코드 선수명   포지션 백넘버 키 
------ ------- ------ ----- ---- 
K02    김강진   DF     43   181 
K02    김관희   FW     39   180 
K02    김만근   FW     34   177 
K02    김병국   DF     2    183 
K02    김병근   DF     3    175 
K02    왕선재   TC 
K02    윤성효   TC 
K02    윤화평   FW     42   182 
K02    이성용   DF     20   173 
K02    정광수   GK     41   182 
.....
31개의 행이 선택되었다.
*/

 

 

* 예시 - 문제 5

   : K-리그 소속 선수들 중에서 소속이 '삼성 블루윙즈' 팀이면서 포지션이 골키퍼(GK)인 선수들의 정보를 보고 싶다.

 

* 예시 - 결과 5

SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 
FROM PLAYER 
WHERE TEAM_ID = 'K02' 
INTERSECT 
SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 
FROM PLAYER 
WHERE POSITION = 'GK'
ORDER BY 1, 2, 3, 4, 5;
 
/*
팀코드 선수명  포지션 백넘버 키 
------ ------ ----- ------ ---
K02    김운재  GK    1     182 
K02    정광수  GK    41    182 
K02    조범철  GK    21    185 
K02    최호진  GK    31    190 
4개의 행이 선택되었다.
*/

---> INTERSECT 를 사용하지 않고도 하나의 WHERE 절을 통해 같은 결과를 출력할 수 있다. (AND)

(WHERE TEAM_ID = 'K02' AND POSITION = 'GK')

---> 또한 INTERSECT 연산자는 EXISTS 또는 IN 서브쿼리를 이용한 SQL문으로도 변경이 가능하다. (추후 링크)

SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 
FROM PLAYER X 
WHERE X.TEAM_ID = 'K02' 
AND EXISTS (
            SELECT 1 
            FROM PLAYER Y 
            WHERE Y.PLAYER_ID = X.PLAYER_ID 
            AND Y.POSITION = 'GK'
            ) 
ORDER BY 1, 2, 3, 4, 5;
 
 
SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 
FROM PLAYER 
WHERE TEAM_ID = 'K02' 
AND PLAYER_ID IN (
                SELECT PLAYER_ID 
                FROM PLAYER 
                WHERE POSITION = 'GK'
                ) 
ORDER BY 1, 2, 3, 4, 5;
 
/*
팀코드 선수명  포지션 백넘버 키 
------ ------ ----- ------ ---
K02    김운재  GK    1     182 
K02    정광수  GK    41    182 
K02    조범철  GK    21    185 
K02    최호진  GK    31    190 
4개의 행이 선택되었다.
*/

 

 

 

출처

http://www.dbguide.net/db.db?cmd=view&boardUid=148199&boardConfigUid=9&categoryUid=216&boardIdx=135&boardStep=1

데이터 전문가 지식포털 DBGuide.net

질문은 댓글로 남겨주시면 되겠습니다. 감사합니다.