[SQLD] 21. 집합연산자 (SET OPERATOR)

1. 집합 연산자(SET OPERATOR)의 종류]

• 두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회할수 있다.
• 집합 연산자는 2개 이상의 질의 결과를 하나의 결과로 만들어 준다.
• SELECT 절의 칼럼 수가 동일하고 SELECT 절의 동일 위치에 존재하는 칼럼의 데이터 타입이 상호 호환 가능해야 한다.
• (반드시 동일한 데이터 타입일 필요는 없음) 그렇지 않으면 데이터베이스가 오류를 반환한다. ??

• 집합 연산자는 개별 SQL문의 결과 집합에 대해 합집합(UNION/UNION ALL), 교집합(INTERSECT), 차집합(EXCEPT)으로 집합간의 관계를 가지고 작업을 한다.

SELECT 칼럼명1, 칼럼명2 
FROM 테이블명1 
[WHERE 조건식 ] 
[[GROUP BY 칼럼(Column)이나 표현식 [HAVING 그룹조건식 ] ] 
집합 연산자 
SELECT 칼럼명1, 칼럼명2 
FROM 테이블명2 
[WHERE 조건식 ] 
[[GROUP BY 칼럼(Column)이나 표현식 [HAVING 그룹조건식 ] ] 
[ORDER BY 1, 2 [ASC또는 DESC ];

TEAM_ID	PLAYER_NAME	BACK_NO
A	박지성	01
A	차범근	02
A	손흥민	03
B	박지성	04
B	기성용	05

2. 합집합(UNION, UNION ALL)

가) UNION

[질문1]

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

1) K-리그 소속 선수 중 소속이 삼성블루윙즈팀인 선수들의 집합과K-리그 소속 선수 중 소속이 전남드레곤즈팀인

 선수들의 집합의 합집합

SELECT PLAYER_NAME 선수명, BACK_NO 백넘버 
FROM PLAYER WHERE TEAM_ID = 'K02' 
UNION 
SELECT PLAYER_NAME 선수명, BACK_NO 백넘버 
FROM PLAYER WHERE TEAM_ID = 'K07' 
ORDER BY 1;

위의 질문에대하여 아래와 같이 IN 또는 OR 연산자를 사용해서 출력하면 순서가 다름을 알 수 있다.

SELECT TEAM_ID, PLAYER_NAME, POSITION, BACK_NO, HEIGHT
FROM PLAYER
WHERE TEAM_ID = 'K02' OR TEAM_ID = 'K07'
ORDER BY 1;

이는 똑같은 집합이지만 출력 순서만 다를뿐이다. 순서를 같게하려면 ORDER BY절에서 임의로 순서를 조정할 수 있다.

 

[질문2]

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

2) K-리그 소속 선수 중 소속이 삼성블루윙즈팀인 선수들의 집합과K-리그 소속 선수 중 포지션이 골키퍼(GK)인 선수들의 집합의 합집합

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'
ORDER BY 1;

위 결과 또한 OR연산자로 나타낼 수 있다.

SELECT TEAM_ID, PLAYER_NAME, POSITION, BACK_NO, HEIGHT
FROM PLAYER 
WHERE TEAM_ID = 'K02' OR POSITION = 'GK'
ORDER BY 1;

 

 

나) UNION ALL 
만약 2번 문제에서 UNION ALL을 사용하면 어떻게 될까?

UNION사용할 때 출력 행은 88개였지만 UNION ALL을 사용하면 92건으로 늘어났다.

SELECT TEAM_ID, PLAYER_NAME, POSITION, BACK_NO, HEIGHT
FROM PLAYER
WHERE TEAM_ID = 'K02'
UNION ALL
SELECT TEAM_ID, PLAYER_NAME, POSITION, BACK_NO, HEIGHT
FROM PLAYER
WHERE POSITION = 'GK'
ORDER BY 1, 2, 3, 4, 5;

결과를 보면 팀 K02의 GK인 사람이 중복됨을 알 수 있다.

 

[질문3]

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

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

SELECT 'P' 구분코드, POSITION 포지션, ROUND( AVG(HEIGHT),2 ) 평균키
FROM PLAYER
GROUP BY POSITION
UNION
SELECT 'T' 구분코드, TEAM_ID 팀명, ROUND( AVG(HEIGHT),2 ) 평균키
FROM PLAYER
GROUP BY TEAM_ID
ORDER BY 1;

위 코드 결과를 보면 합집합을 구하기 위해 그룹함수를 사용했고 출력결과에서 혼동되지 않도록 구분코드를 사용했다.

실제 테이블에서는 존재하지 않지만 출력결과 편의상 구분코드처럼 임의의 컬럼을 사용할 수 있다.

 

 

3. 교집합(INTERSECT)

[질문 4]

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

4) K-리그 소속 선수 중 소속이 삼성블루윙즈팀인 선수들의 집합과K-리그 소속 선수 중 포지션이 골키퍼(GK)인

선수들의 집합의 교집합

 

가) INTERSECT

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;

집합 연산자를 사용하지 않고 논리연산자나 EXISTS 또는 IN 서브쿼리로도 표현할 수 있다.

결과는 같다.

--논리 연산자로 출력하기
SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER 
WHERE TEAM_ID = 'K02' AND POSITION = 'GK'
ORDER BY 1,2,3,4,5;

--EXISTS 또는 IN 서브쿼리로도 가능하다.
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 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;

 

 

4. 차집합(EXCEPT)

[질문4]

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

4) K-리그 소속 선수 중 소속이 삼성블루윙즈팀인 선수들의 집합과K-리그 소속 선수 중 포지션이 미드필더(MF)인 선수들의 집합의 차집합

 

가) MINUS

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;

 

이번에는 논리연산자로 표현해보자.

SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 
FROM PLAYER 
WHERE TEAM_ID = 'K02' AND POSITION <> 'MF' 
ORDER BY 1, 2, 3, 4, 5;

 

 

 

 

 

 

정리

------1. 집합연산자
--두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회
--집합 연산자는 2개 이상의 질의 결과를 하나의 결과로 만든다.
--SELECT절의 컬럼 수가 동일하고 SELECT절의 동일 위치에 존재하는 컬럼의 데이터 타입이 
--상호 호환 가능해야 한다.
--UNION : 여러 개의 SQL문의 결과에 대한 합집합으로 결과에서 중복된 행은 하나로 만든다.
--UNION ALL : 여러 개의 SQL문의 결과에 대한 합집합으로 중복된 행도 그대로 반환.
--INTERSECT : 여러 개의 SQL문의 결과에 대한 교집합이고 중복은 하나로 만든다.
--EXCEPT : 앞의 SQL문의 결과에서 뒤의 SQL문 결과에 대한 차집합, 중복은 하나로 만든다.
--일반적인 형식은 아래와 같다.
SELECT 컬럼1, 컬럼2
FROM 테이블1
[WHERE 조건식]
[GROUP BY 컬럼 또는 표현식 [HAVING 그룹조건식]]
집합연산자
SELECT 컬럼1, 컬럼2
FROM 테이블1
[WHERE 조건식]
[GROUP BY 컬럼 또는 표현식 [HAVING 그룹조건식]]
[ORDER BY 컬럼 [ASC | DESC]];

------2. 합집합(UNION, UNION ALL)
--UNION
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'
ORDER BY 1; --1은 첫번째 컬럼을 의미한다.

--IN또는 OR연산자를 사용해서 비교해보자.
SELECT TEAM_ID, PLAYER_NAME, POSITION, BACK_NO, HEIGHT
FROM PLAYER
WHERE TEAM_ID = 'K02' OR TEAM_ID = 'K07'
ORDER BY 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 POSITION = 'GK'
ORDER BY 1;

SELECT TEAM_ID, PLAYER_NAME, POSITION, BACK_NO, HEIGHT
FROM PLAYER 
WHERE TEAM_ID = 'K02' OR POSITION = 'GK'
ORDER BY 1;

SELECT TEAM_ID, PLAYER_NAME, POSITION, BACK_NO, HEIGHT
FROM PLAYER
WHERE TEAM_ID = 'K02'
UNION ALL
SELECT TEAM_ID, PLAYER_NAME, POSITION, BACK_NO, HEIGHT
FROM PLAYER
WHERE POSITION = 'GK'
ORDER BY 1, 2, 3, 4, 5;

--
SELECT 'P' 구분코드, POSITION 포지션, ROUND( AVG(HEIGHT),2 ) 평균키
FROM PLAYER
GROUP BY POSITION
UNION
SELECT 'T' 구분코드, TEAM_ID 팀명, ROUND( AVG(HEIGHT),2 ) 평균키
FROM PLAYER
GROUP BY TEAM_ID
ORDER BY 1;

----3. 교집합 (INTERSECT)
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;

--논리 연산자로 출력하기
SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER 
WHERE TEAM_ID = 'K02' AND POSITION = 'GK'
ORDER BY 1,2,3,4,5;

--EXISTS 또는 IN 서브쿼리로도 가능하다.
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 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;


----4. 차집합(EXCEPT)
SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE TEAM_ID = 'K02' 
EXCEPT
SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE 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 POSITION <> 'MF' 
ORDER BY 1, 2, 3, 4, 5;

 

 

[출처]

wiki.gurubee.net/pages/viewpage.action?pageId=27427321