[SQLD] SQL 활용 2-2. 집합 연산자

집합 연산자

: 두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회하는 방법 

 

차이

기존의 JOIN	FROM 절에 검색하고자 하는 테이블을 나열하고, WHERE 절에 조인 조건을 기술하여 원하는 데이터를 조회
집합 연산자	여러 개의 질의의 결과를 연결하여 하나로 결합하는 방식을 사용한다. 즉, 집합 연산자는 2개 이상의 질의 결과를 하나의 결과

목적

• 서로 다른 테이블에서 유사한 형태의 결과를 반환하는 것을 하나의 결과로 합치고자 할 때
• 동일 테이블에서 서로 다른 질의를 수행하여 결과를 합치고자 할 때
• 튜닝관점에서 실행계획을 분리하고자 하는 목적

사용제약조건

-SELECT 절의 칼럼 수가 동일하고 SELECT 절의 동일 위치에 존재하는 칼럼의 데이터 타입이 상호 호환 가능

(단, 반드시 동일한 데이터 타입일 필요는 없음)

 

종류

 

이용

결과 집합에 대해 합집합(UNION / UNION ALL), 교집합 (INTERSECT), 차집합(EXCEPT)으로 집합간의 관계를 이용

 

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

 

즉, UNION ALL을 제외하고 중복을 허용하지 않는다.
UNION ALL은 중복을 허용해 결과 집합을 단순히 합친 것과 동일하다.

 

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 ] ;

집합 연산자는 여러 개의 SELECT문을 연결하는 것에 지나지 않기 때문에,
사용상의 제약조건을 만족한다면 어떤 형태의 SELECT문에서도 사용이 가능하다.
ORDER BY는 집합 연산을 적용한 최종 결과에 대한 정렬 처리이므로 가장 마지막 줄에 한번만 기술

삼성블루윙즈팀인 선수들과 전남드레곤즈팀인 선수	삼성블루윙즈팀인 선수들의 집합과 전남드레곤즈팀인 선수들의 집합의 합집합
삼성블루윙즈팀인 선수들과 포지션이 골키퍼(GK)인 선수	삼성블루윙즈팀인 선수들의 집합과 포지션이 골키퍼(GK)인 선수들의 집합의 합집합
포지션별 평균키와 팀별 평균키	포지션별 평균키에 대한 집합과 팀별 평균 키 집합의 합집합
삼성블루윙즈팀이면서 포지션이 미드필더(MF)가 아닌 선수	삼성블루윙즈팀인 선수들의 집합과 포지션이 미드필더(MF))인 선수들의 차집합
삼성블루윙즈팀이면서 포지션이 골키퍼(GK)인 선수	삼성블루윙즈팀인 선수들의 집합과 포지션이 골키퍼(GK)인 집합의 교집합

 

(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'

SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE TEAM_ID = 'K02' OR TEAM_ID = 'K07';

SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE TEAM_ID IN ('K02', 'K07');

합집합이라는 것은 WHERE 절에 IN 또는 OR 연산자로도 변환이 가능하다.
다만 IN 또는 OR 연산자를 사용할 경우에는 결과의 표시 순서가 달라질 수 있다
[ORDER BY절을 사용해 명시적으로 정렬 순서를 정의하는 것을 권장한다.]

 

(2)

삼성블루윙즈팀인 선수들과 포지션이 골키퍼(GK)인 선수

삼성블루윙즈팀인 선수들의 집합과 포지션이 골키퍼(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';

SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE TEAM_ID = 'K02' OR POSITION = 'GK';

서로 다른 칼럼에 조건을 사용했기 때문에 IN 연산자를 사용할 수 없다.

+)UNION ALL 사용시 [ORDER BY를 사용해 중복행을 확인]

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;

 

(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;

그룹함수도 집합 연산자에서 사용이 가능
목적을 위해 SELECT 절 에 임의의 칼럼을 추가
-실제로 테이블에는 존재하지 않지만 결과 행을 구분하기 위해 SELECT 절에 칼럼('구분코드')을 추가
결과를 표시할 때 HEADING 부분은 첫 번째 SQL문에서 사용된 HEADING 이 적용
[구분코드, POSITION 포지션, AVG(HEIGHT) 평균키]

(4)

삼성블루윙즈팀이면서 포지션이 미드필더(MF)가 아닌 선수

삼성블루윙즈팀인 선수들의 집합과 포지션이 미드필더(MF))인 선수들의 차집합

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 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;

MINUS 연산자는 NOT EXISTS 또는 NOT IN 서브쿼리를 이용한 SQL문으로도 변경가능

(5)

삼성블루윙즈팀이면서 포지션이 골키퍼(GK)인 선수

삼성블루윙즈팀인 선수들의 집합과 포지션이 골키퍼(GK)인 집합의 교집합

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;

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;

INTERSECT 연산자는 EXISTS 또는 IN 서브쿼리를 이용한 SQL문으로도 변경가능