서브쿼리
WHERE절에서의 활용
서브쿼리라는 단어를 들으니, 쿼리긴 하지만 어떤 특정한 역할을 수행하는 쿼리인 듯 싶다.
서브쿼리란?
->SQL 명령어 안에 지정하는 하부 SELECT를 이야기한다.
정의로만 보면 무슨 말인지 어려우니, 예시를 들어보자.
"연봉이 역대급으로(가장) 높은 선수의 정보"를 출력한다고 가정하자.
그러기 위해선
1) salaries 테이블에서 연봉이 가장 높은 사람의 ID를 추출하고
2) players 테이블에서 WHERE절에 playerID='추출한 ID'로 쿼리를 만든다.
라는 과정이 필요할 것이다.(일단 우리가 지금까지 배운 범위까지 말이다.)
--연봉이 역대급으로 높은 선수의 정보
Select TOP 1 *
from salaries
ORDER BY salary DESC;
아하, rodrial01이라는 ID를 가진 선수가 연봉을 가장 많이 받는다.
--players테이블에서 rodrial01이라는 사람의 정보를 조회하자.
SELECT *
FROM players
WHERE playerID='rodrial01'
두번의 쿼리로 정보를 조회하였다. 참으로 까다롭다. 1)과정에서 별도로 ID를 하드코딩하는 과정이 있기 때문이다. 서브쿼리는 이런 상황에서 빛을 발휘한다.
--플레이어 정보를 조회할건데, 플레이어 아이디가
--(연봉 기준 내림차순 정렬시, 가장 1등인 사람)인 정보를 조회하자.
SELECT *
FROM players
WHERE playerID=(SELECT TOP 1 playerID from salaries ORDER BY salary DESC);이와 같이 서브쿼리는 대부분의 상황에서 WHERE절에 활용한다.
해당 쿼리문은 단일행 서브쿼리인데, 한 사람(가장 높은 연봉)의 정보만 반환하기 때문이다.
그렇다면, 1등부터 20등까지의 정보를 추출하고 싶다면 어떻게 할까?
--당연히 단일행에 다중값을 넣으려니 오류가 뜬다.
SELECT *
FROM players
WHERE playerID=(SELECT TOP 20 playerID from salaries ORDER BY salary DESC);
다중행을 위해선 = 대신 IN을 사용하면 된다. IN의 좋은 점은 중복된 행을 제거해준다는 점이다.
--실제론 20행이 아닌 9행만 나온다.
SELECT *
FROM players
WHERE playerID IN (SELECT TOP 20 playerID from salaries ORDER BY salary DESC);
20행이 아닌 9행이 나온 이유는 해당 테이블은 같은 플레이어 ID를 가졌음에도 년도별로 다른 연봉을 갖는 행 또한 존재하기 때문이다.
SELECT에서의 활용
서브 쿼리는 WHERE절 뿐만 아니라 SELECT에서도 활용 가능하다.
players 테이블과 batting 테이블의 행 개수를 저장하여 별도의 변수명으로 표기하고 싶다고 가정하자.
--players 테이블의 행 수를 playerCount, batting 테이블의 행 수를 battingCount로 저장하자.
SELECT (SELECT COUNT(*) FROM players) AS playerCount, (SELECT COUNT(*) FROM batting) AS battingCount;
아주 잘 나온다.
INSERT에서의 활용
INSERT에서도 활용가능하다!
지난시간, 우리는 INSERT INTO문을 통해 일일이 열 값을 넣어줌으로써 새로운 행을 추가하였다.
만일 INSERT INTO 문 내 SELECT 문으로 가장 높은 연봉을 선택하여 열 값에 넣어줄 수 있을까?
--가장 높은 연봉 값을 select로 조회하여 행을 삽입한다.
INSERT INTO salaries
VALUES (2020,'KOR','NL','rookiss5',(SELECT MAX(salary) FROM salaries))
아주 잘 적용된다!
비슷하면서 같은 방법으로, VALUES 대신 아예 SELECT문으로 대체하는 방법도 있다.
--INSERT INTO VALUES와 똑같이 작동된다.
INSERT INTO salaries
SELECT 2020,'KOR','NL','Rookiss6',(SELECT MAX(salary) FROM salaries)그렇다면, INSERT INTO VALUES와 INSERT INTO SELECT의 차이점은 무엇일까?
결론부터 말하면, INSERT INTO SELECT는 SELECT 문이므로, INSERT INTO VALUES와 다르게 한 행이 아닌 여러 행을 지정할 수 있다는 점이다.
가령 예를 들면, 새 테이블에 기존 테이블 값을 복사하여 삽입할 경우 INSERT INTO VALUES는 일일이 한 행씩 넣어야 하는 반면, INSERT INTO SELECT는 SELECT 문 절로 명령문 '한 번에' 모든 행을 집어넣을 수 있을 것이다.
EXISTS
지금까지 단일 행, 다중 행 서브쿼리에 대해 배웠는데
상관 관계 서브쿼리인 EXISTS도 존재한다.
처음 보면 다소 난해하니 이런 것이 있고, 대강 이렇게 작동하는구나! 식으로 이해하자.
포스트 시즌에 참여한 선수들의 목록을 SELECT문으로 추출해보자.
battingpost 테이블을 다음과 같다.
--battingpost 내 playerID가 players 내 playerID에 있는가?
SELECT *
FROM players
WHERE playerID IN(
SELECT playerID FROM battingpost);
포스트 시즌에 참여한 선수들의 목록은 players 테이블에 있는 playerID가 battingpost 테이블에도 있다는 의미이다.
여러 행이므로 역시 IN을 써주었다. 같은 의미의 EXISTS는 다음과 같다.
--'선수들' 목록이니, players 테이블에서 행을 선택한다.(select *문)
--battingpost 테이블 내 playerID가 players 테이블 내 playerID와 같은게 존재하는가?
--존재한다면, 포스트 시즌에 참여한 선수다.
--없다면, 포스트 시즌에 참여하지 않은 선수다.
SELECT *
FROM players
WHERE EXISTS(SELECT playerID FROM battingpost WHERE players.PlayerID=battingpost.playerID);