[MySQL] 서브쿼리 사용법

서브쿼리
: SQL 문 안에 부품처럼 들어가는 SELECT 문

sub (하위의, 일부분의)
Query (데이터베이스에 보내는 요청)


서브쿼리를 쓸 땐 괄호로 감싸줘야함

 

서브쿼리는 HAVING 절, SELECT 절, WHERE 절, FROM 절 등에서 사용할 수 있다.

 

 

---

 

 

 

HAVING 절에 있는 서브쿼리

예시 쿼리문 >

SELECT i.id, i.name, AVG(star) AS avg_star 
FROM item AS i LEFT OUTER JOIN review AS r 
ON r.item_id = i.id 
GROUP BY i.id, i.name 
HAVING avg_star < (SELECT AVG(star) FROM review) 
ORDER BY avg_star DESC;

실행 화면 >

 

서브쿼리를 포함하는 전체 SQL 문을 outer query(외부 쿼리), 서브쿼리를 inner query(내부 쿼리)라고 하기도 한다.

 

 

---

 

 

 

SELECT 절에 있는 서브쿼리

예시 쿼리문 >

SELECT 
   id, 
   name, 
   price, 
   (SELECT MAX(price) FROM item) AS max_price #서브쿼리, 전체 아이템중 가장 비싼 가격 
FROM main.item;

실행 화면 >

 

 

---

 

 

WHERE 절에 있는 서브쿼리

예시 쿼리문 >

SELECT id, name, price, (SELECT AVG(price) FROM item) AS avg_price 
FROM main.item 
WHERE price > (SELECT AVG(price) FROM item);

실행 화면 >

예시 쿼리문 >

#가장 저렴한 상품 찾기 
SELECT id, name, price 
FROM item 
WHERE price = (SELECT MIN(price) FROM item);

실행 화면 >

예시 쿼리문 >
#member 테이블에서 주소의 주요 지역을 기준으로 회원들을 그루핑했을 때, 가장 많은 회원들이 사는 주요 지역에 살고 있는 회원들만 조회

SELECT * FROM member 
WHERE SUBSTRING(address, 1, 2) = 
(
SELECT SUBSTRING(address, 1, 2) 
FROM member 
GROUP BY SUBSTRING(address, 1, 2) 
ORDER BY COUNT(*) DESC LIMIT 1
);

 

WHERE IN

 

WHERE  IN 기본 사용법을 모르시는 분은 아래 글 6번 참고

https://devfairy.tistory.com/87

 

 

예시 쿼리문 >

#리뷰가 최소 3개 이상 달린 상품들의 정보만 보고 싶다 

SELECT * FROM item 
WHERE id IN 
( 
SELECT item_id 
FROM review 
GROUP BY item_id HAVING COUNT(*) >= 3 
);

실행 화면 >

하나의 column에 여러 row들이 있는 형태의 결과를 리턴하는 서브쿼리

WHERE ANY ,  WHERE SOME

 

# 서브쿼리 결과값들 보다 어느 하나라도 크면 TRUE 

  = 서브쿼리 결과값들 중 최소값 보다 크면 TRUE
WHERE view_count > ANY (서브쿼리) 
WHERE view_count > SOME (서브쿼리) 

 

 

WHERE ALL

# 서브쿼리 모든 결과값들 보다 큰 값만 TRUE 

  = 서브쿼리 결과값들 중 최대값 보다 커야 TRUE
WHERE view_count > ALL (서브쿼리)

 

 

 

ANY, SOME
단 하나의(ANY) 값보다도 크다면 True를 리턴.
서브쿼리의 결과에 있는 각 row의 값들 중 하나라도 조건을 만족하면 True를 리턴


ALL (서브쿼리)
ALL은 모든 경우에 대해서 해당 조건이 성립해야 True를 리턴

 

---

 

 

 

FROM 절에 있는 서브쿼리

 

서브쿼리 결과 단일값을 리턴할 수도 있고 (스칼라 서브쿼리)

하나의 column에 여러 row들이 있는 형태의 결과를 리턴할 수도 있고

하나의 테이블 형태의 결과(여러 column, 여러 row)를 리턴할 수도 있는데

 

테이블 형태의 결과를 리턴하는 서브쿼리로 일시적으로 탄생한 테이블을 derived table 이라고 한다.

(derived : 파생시키다, 도출해내다)

 

derived table은 SQL문 안에서만 유효한 하나의 테이블이다.

 

FROM 뒤에서 서브쿼리로 derived table 을 생성 시키면 alias 를 꼭 붙여줘야한다.

 

 

 

FROM 절 뒤 서브쿼리 예시1 _ derived table

 

빨간부분이 서브쿼리 (FROM절 뒤에 derived table을 생성시킨 서브쿼리)

노란부분이 서브쿼리의 alias 

 

FROM 절 뒤 서브쿼리 예시2_ derived table

 

 

derived table 응용

 

BMI 수치를 구하는 식을 매번 똑같이 쓰는 것이 보기 싫다

 

맨 위 SELECT 절에서 alias 를 붙였으니까 CASE 에서도 쓸수 있겠지?

 

 

이렇게 사용하면 에러가 난다. 

BMI는 우리가 SELECT 절 안에서 설정한 alias다. 

CASE 함수가 실행될 때는 BMI라는 alias가 아직 인식되지 않은 상태이기 때문에 오류가 난다. 

실행 순서는 FROM -> WHERE -> SELECT ...   (https://devfairy.tistory.com/96 참고)

 

 

그렇다고 매번 저 긴 식을 쓰자니 가독성이 떨어지고... 

서브쿼리를 통해 이 문제를 해결할 수 있다. 

 

 

 

 

 

 

이렇게 서브쿼리를 사용하여 subquery_for_BMI 라는 derived table 을 생성했다.

subquery_for_BMI는 마치 원래 존재하던 테이블인 것처럼 자유롭게 사용할 수 있다.

FROM 절에서 서브쿼리가 가장먼저 실행되면서 BMI 라는 alias 가 인식 됐다. 

 

그래서 이렇게 outer query에서 BMI라는 단어를 자유롭게 사용할 수 있는 것이다. 

이렇게 쓰면 마치, 이미 BMI라는 컬럼이 있는 테이블에서 조회를 하는 것과 같기 때문에 이전과는 달리 에러가 발생하지 않는다.

 

 

 

 

 

 

---

 

 

여기 까지 살펴본 서브쿼리들은 모두, 그 자체만으로도 실행이 가능한 "비상관 서브쿼리"이다.

 

 

서브쿼리가 그것을 둘러싼 outer query와 별개로, 독립적으로 실행 가능하고, 

outer query와 상관 관계가 없는 서브쿼리를 비상관 서브쿼리라고 한다.

 

 

반대로 outer query와 상관 관계가 있는 서브쿼리를 "상관 서브쿼리"라고 한다

 

 

 

상관 서브쿼리 예시>

 

 

 

 

 

EXISTS 뒤에 나오는 서브쿼리만 별도로 빼서 실행해보면,  item이라는 테이블 이름이 FROM 절에 없기 때문에 실행에 실패한다.

 item 테이블의 이름이 서브쿼리의 FROM 절에 있는 게 아니라 outer query에 있다

 

 

이렇게 서브쿼리가 outer query에 적힌 테이블 이름 등과 상관 관계를 갖고 있어서 그 단독으로는 실행되지 못하는 서브쿼리를 상관 서브쿼리라고 한다.

 

 

여기서 WHERE 뒤에 EXISTS 의 의미는 "만약에 존재한다면" 이라는 의미의 조건문

WHERE 뒤에 NOT EXISTS 는 "만약에 존재하지 않는다면" 이라는 의미의 조건문

 

 

 

 

 

 

상관 서브쿼리 예시>

member 테이블의 회원 중에서 리뷰를 하나도 남기지 않아서 review 테이블에 관련 정보가 하나도 없는 회원들만 조회

SELECT * FROM member WHERE NOT EXISTS 
( 
SELECT * FROM review WHERE review.mem_id = member.id 
);

 

 

 

상관 서브쿼리 예시>

member 테이블을 조회하면서, 같은 해에 태어난 회원들 중 가장 작은 키를 가진 회원의 키 정보를 담은 컬럼을 오른쪽 끝에 추가해서 보자

 

SELECT *,
(SELECT MIN(height)
FROM member AS m2 
WHERE birthday IS NOT NULL 
 AND height IS NOT NULL 
 AND YEAR(m1.birthday) = YEAR(m2.birthday)
 ) AS min_height_in_the_year
 FROM member AS m1
 ORDER BY min_height_in_the_year ASC;

  ㄴ member 테이블 하나를 갖고 마치 SELF JOIN 같은 작업을 처리하고 있다

 

 

출력 결과>

 

 

 

 

 

참고로 비상관 서브쿼리는 영어로 Non-correlated Subquery,

상관 서브쿼리는 영어로 Correlated Subquery라고 한다.