[SQLD 시험 대비] 2과목. SQL 기본 및 활용 : 2장. SQL 활용 - 2. 집합 연산자(Set Operator)
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 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'
// OR 연산자 사용
SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE TEAM_ID = 'K02' OR TEAM_ID = 'K07';
// IN 연산자 사용
SELECT TEAM_ID 팀코드, PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM PLAYER
WHERE TEAM_ID IN ('K02', 'K07');
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 '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 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'
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;
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;
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;