서브쿼리(SUB QUERY)
하나의 SQL문에 포함되어 있는 또 다른 SQL문을 말합니다.
SUB: 하위의, 일부분의
QUERY: 데이터베이스에 보내는 요청
서브쿼리는 괄호로 감싸서 사용합니다.
SELECT 절에 있는 서브쿼리
SELECT 절에 서브쿼리를 사용한다는 것은 일반적으로 원래 테이블에는 없던 새로운 컬럼을 추가해서 보겠다는 의미입니다.
아래의 예시의 경우 원래의 테이블에 id, 이름, 가격 외에 가격들의 평균도 옆에서 볼 수 있도록 서브쿼리를 이용해 구현된 예시입니다.
SELECT
id,
name,
price,
(SELECT AVG(price) FROM item)AS avg_price -- ()로 감싸진 부분이 서브쿼리입니다.
FROM item;WHERE 절에 있는 서브쿼리
item테이블 중 가장 낮은 가격을 보유한 데이터를 조회하는 예시입니다.
SELECT
id,
name,
price,
FROM item
WHERE price = (SELECT MIN(price) FROM item);FROM 절에 있는 서브쿼리
FROM 절에 서브쿼리를 사용해 테이블을 바꾸어 적용해 줄 수 있습니다.
이렇게 서브쿼리로 인해 새롭게 도출된 테이블을 derived table 이라고 합니다.
그리고 derived table에는 반드시 AS 즉 alias를 붙혀줘야합니다. 규칙이기 때문에 붙이지 않으면 에러가 발생합니다.
아래 예시는 리뷰 수의 평균, 최대 값, 최소 값을 구하는 예시입니다.
SELECT
avg(review_count),
max(review_count),
min(review_count)
FROM
(SELECT
SUBSTRING(address, 1, 2) AS region,
COUNT(*) AS review_count
FROM review AS r LEFT OUTER JOIN member AS m
ON r.mem_id = m.id
GROUP BY SUBSTRING(address, 1, 2)
HAVING region IS NOT NULL) AS review_count_summary; substring
문자열의 일부분을 추출하는 함수로
SUBSTRING(컬럼명, 시작할 위치, 길이)로 구성되어 있습니다.
예시에서 적힌SUBSTRING(address, 1, 2)의 경우address컬럼의1번째 부터 시작해2만큼의 길이를 추출하는 의미입니다.
일반적인 코드는 0부터 시작하는 것과 별개로 대부분의 sql 데이터베이스 시스템에서는 1부터 시작합니다.address컬럼의 문자열이 대부분 서울특별시~ 광주광역시~ 로 시작하기에 앞에 두 글자만 따왔습니다.
다시 예시로 돌아와서
서브쿼리 부분을 보면 review테이블을 기준으로 member테이블을 join해 address컬럼에서 2글자만 따와 그룹화해주고 null이 아닌 대상만 가져왔습니다.
서브쿼리로 만든 derived table을 사용해 null값이 없는 간략한 지역명으로 그룹화 된 테이블을 가지고 리뷰 수의 평균, 최대 값, 최소값을 구할 수 있었습니다.
서브쿼리 연산자
IN 연산자
IN 연산자는 서브쿼리 결과에 존재하는 임의의 값과 동일한 조건을 의미합니다.
아래의 예시는 IN연산자를 사용해 리뷰 수가 3개 이상인 제품들만 조회하는 예시입니다.
서브쿼리로 review테이블의 item_id 컬럼을 그룹화해 갯수가 3개 이상인 제품들을 조회하고
해당 제품 id를 item테이블의 id와 비교해 같은 id를 조회할 수 있습니다.
SELECT * FROM item
WHERE id IN
(
SELECT item_id
FROM review
GROUP BY item_id HAVING COUNT(*) >= 3
);ANY(SOME) 연산자
ANY 연산자는 서브쿼리에 존재하는 어느 하나의 값이라도 만족하는 조건을 의미합니다.
영화 정보를 가지고 있는 theater테이블에서 액션 카테고리를 제외한 영화들 중 액션 카테고리를 가진 영화의 관객 수를 넘는 영화가 있는지를 조회하는 예시입니다.
SELECT * FROM theater
WHERE view_count > ANY(SELECT view_count FROM theater WHERE category = 'ACTION')
AND category != 'ACTION'액션 카테고리를 가진 영화들의 조회 수가 120000, 2300000, 7000000, 8500000 이런 식으로 되어있다면 ANY 연산자가 붙었기에 그 숫자 중 가장 작은 120000의 숫자를 넘는 액션 카테고리가 아닌 영화가 있다면 결과에 조회됩니다.
ANY 연산자말고 SOME 연산자도 있는데 같은 역할을 가진 연산자이기에 어느 것을 사용해도 상관이 없습니다.
ALL 연산자
ALL 연산자는 서브쿼리의 결과에 존재하는 모든 값을 만족하는 조건을 의미합니다.
ANY에서 사용한 동일한 예시에서 ANY를 ALL로 바꿔본 예시입니다.
SELECT * FROM theater
WHERE view_count > ALL(SELECT view_count FROM theater WHERE category = 'ACTION')
AND category != 'ACTION'ALL 연산자의 경우 모든 결과 값을 만족해야하기에 액션 카테고리를 가진 영화들의 조회 수 중 가장 높은 숫자인 8500000을 넘는 액션 카테고리가 아닌 영화가 있을 때 결과에 조회됩니다.
EXISTS 연산자
EXISTS 연산자는 서브쿼리 결과값을 만족하는 값이 존재하는지 여부를 확인하는 조건을 의미합니다.
SELECT * FROM item
WHERE EXISTS (SELECT * FROM review WHERE review.item_id = item_id)리뷰가 달린 아이템을 조회하는 예시입니다.
item테이블의 row들을 하나씩 돌면서 그 row의 id값과 같은 값을 가진 review테이블의 item_id 컬럼을 조회합니다.
만약 존재한다면 WHERE절은 TRUE가 되어 해당 row는 조회 결과에 담기게 되고 다음 row로 이동하며 전의 과정을 반복합니다.
반대로 리뷰가 달리지 않은 아이템을 조회할 때에는 EXISTS를 NOT EXISTS로 바꾸어주면 원하는 결과를 얻을 수 있습니다.
