SQLD 2과목 SQL 활용 - 집합연산자

<목차> 서브쿼리 집합연산자 그룹 함수 윈도우 함수 TOP N 쿼리 계층형 질의와 셀프 조인 PIVOT 절과 UNPIVOT 절 정규 표현식

 

※ 자료출처: 데이터 자격검정 협회 

 

2. 집합연산자

집합연산자

• 두 개 이상의 테이블에서 조인을 사용하지 않고 연관된 데이터를 조회하는 방법 중 하나
• 여러 개의 질의의 결과를 연결하여 하나로 결합하는 방식
• 2개 이상의 질의 결과를 하나의 결과로 만들어줌.
• 집합연산자를 사용하는 상황은 1) 서로 다른 테이블에서 유사한 형태의 결과를 반환하는 것을 하나의 결과로 합치고자 할 때
• 2) 동일 테이블에서 서로 다른 질의를 수행하여 결과를 합치고자 할 때
• 3) 튜닝관점에서 실행계획을 분리하고자 할 때 사용
• 집합연산자를 사용하기 위해서는 다음 제약 조건을 만족해야 함.

- SELECT 절의 칼럼 수가 동일

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

▶ 그렇지 않으면 데이터베이스가 오류 반환

---

집합 연산자의 종류

집합 연산자	연산자의 의미
UNION	여러 개의 SQL 문의 결과에 대한 합집합으로 결과에서 모든 중복된 행은 하나의 행으로 만듦.
UNION ALL	여러 개의 SQL 문의 결과에 대한 합집합으로 중복된 행도 그대로 결과로 표시. (단순히 결과만 합쳐놓은 것) 일반적으로 여러 질의 결과가 상호 배타적인 경우 많이 사용. 개별 SQL 문의 결과가 서로 중복되지 않는 경우, UNION과 결과가 동일. (결과의 정렬 순서에는 차이가 있을 수 있음)
INTERSECT	여러 개의 SQL 문의 결과에 대한 교집합. 중복된 행은 하나의 행으로 표시
EXCEPT	앞의 SQL 문의 결과에서 뒤의 SQL 문의 결과에 대한 차집합. 중복된 행은 하나의 행으로 만듦. (일부 데이터베이스는 MINUS 사용)

 

집합 연산자의 연산 (출처: 데이터 자격 검정 협회)

 

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

---

집합 연산자를 사용하여 만들어지는 SQL 문의 형태

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

 

※ 집합 연산자는 사용상의 제약조건을 만족한다면 어떤 형태의 SELECT 문이라도 이용 가능.

---

집합 연산자를 시용하여 처리하는 방법

 

예제 1)

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

 

▼ (질문을 집합 연산의 개념으로 해석한 결과)

 

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

▼ (SQL 문 작성)

 

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 K07 강철 DF 3 178 K07 김반 MF 14 174 K07 김영수 MF 30 175 ...... 100개의 행이 선택되었다.

 

※ 합집합이라는 것은 WHERE 절에 IN 또는 OR 연산자로도 변환 가능.

다만, IN 또는 OR 연산자를 사용할 경우에는 결과의 표시 순서가 달라질 수도 있음. (하지만 집합이라는 관점에서는 두 집합이 서로 다르다고 할 수는 없다.)

 

▼ (IN 또는 OR 연산자 사용)

 

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

 

---

 

예제 2)

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

 

▼ (질문을 집합 연산의 개념으로 해석한 결과)

 

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

 

▼ (SQL 문 작성)

 

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 K02 가비 MF 10 177 K02 강대희 MF 26 174 K02 고종수 MF 22 176 K02 고창현 MF 8 170 K02 김강진 DF 43 181 ....... 88개의 행이 선택되었다.

※ UNION ALL 은 각각의 질의 결과를 단순히 결합시켜 줄 뿐 중복된 결과를 제외시키지 않기 때문에 결괏값이 달라질 수 있음. UNION ALL에서 중복된 결과들을 확인해 보고자 할 때는 ORDER BY 절을 사용하면 용이.

 

▼ (IN 또는 OR 연산자 사용)

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

---

 

예제 3)

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

 

▼ (질문을 집합 연산의 개념으로 해석한 결과)

 

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

 

▼ (SQL 문 작성)

 

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;

※ SQL Server에서는 MINUS 대신 EXCEPT 사용

[실행결과] 팀코드 선수명 포지션 백넘버 키 ---- ------- ------ ------- ------ K02 김강진 DF 43 181 K02 김관희 FW 39 180 K02 김만근 FW 34 177 K02 김병국 DF 2 183 K02 김병근 DF 3 175 K02 왕선재 TC .... 31개의 행이 선택되었다.

 

▼ (논리 연산자 사용)

 

SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키 FROM PLAYER WHERE TEAM_ID = 'K02' AND POSITION <> 'MF'

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

 

▼ (서브 쿼리 사용 - NOT EXISTS / NOT IN)

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 NOT IN (SELECT PLAYER_ID FROM PLAYER WHERE POSITION = 'MF') ORDER BY 1, 2, 3, 4, 5;

 

---

예제 4)

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

 

▼ (질문을 집합 연산의 개념으로 해석한 결과)

 

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

 

▼ (SQL 문 작성)

 

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;

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

 

▼ (서브 쿼리 사용 - 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 IN (SELECT PLAYER_ID FROM PLAYER WHERE POSITION = 'GK') ORDER BY 1, 2, 3, 4, 5;

---

이전글

서브쿼리

 

다음글

그룹 함수