데이터베이스 정리 정리16 - 서브쿼리

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

서브 쿼리를 inner query(내부 쿼리)라고 하기도 한다.

위 빨간 줄이 서브 쿼리이다. review 테이블에서 별점 평균값을 나타네는 구문이다.

SUB - '하위의', '일부분의'라는 의미이고

QUERY - '데이터베이스에 보내는 요청'이라는 의미이다.

서브 쿼리를 사용하면 무조건 ()을 사용해줘야 한다.

서브 쿼리를 사용하면 쿼리 창을 새로 켤 필요 없이, 하나의 쿼리 창에서 하나의 SQL 문만으로도 원하는 결과를 얻을 수 있다.

서브 쿼리는 HAVING절뿐만 아니라 SELECT절, WHERE절, FROM절 등에서도 사용할 수 있다.

 

SELECT절에 있는 서브 쿼리

SELECT id, name, price,
(SELECT MAX(price) FROM item) AS MAX_price
FROM subpang_main.item;

위에 있는 서브 쿼리 구문은 전체 상품 중 가장 비싼 상품의 가격이 담긴 새로운 칼럼을 보이는 구문이다.

SELECT절에 서브 쿼리를 사용하는 이유는 보통 원래 테이블에는 없던 새로운 칼럼을 추가해서 보겠다는 뜻이다.

 

WHERE절에 있는 서브 쿼리

SELECT id, name, price,
(SELECT AVG(price) FROM item) AS AVG_price
FROM subpang_main.item
WHERE price > (SELECT AVG(price) FROM item);

상품들 중에서 그 가격이 평균 가격보다 높은 상품들만 추려낸 구문이다.

 

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

리뷰가 3개 이상 달려 있는 모든 상품들의 id를 리턴해준다.

IN 이 있으면 그 뒤에 있는 값들 중에서 하나라도 같은 게 있으면 조건을 만족한다.

 

FROM절에 있는 서브 쿼리

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
	AND region != '안드') AS review_count_summary;

 

비상관 쿼리, 상관 쿼리

비상관 쿼리

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

위 코드의 서브 쿼리는 그 자체만으로도 실행이 가능한 서브 쿼리이다.

이 서브 쿼리를 둘러싼 outer query와 별개로, 독립적으로 실행되기 때문에 가능하다. 이렇게 outer query와 상관 관계가 없는 서브쿼리를 비상관 서브 쿼리라고 한다.

 

 

서브 쿼리 

서브쿼리 뒷부분을 보면 item이라는 테이블 이름이 있다. 그런데 여기서 신기한 것은 item 테이블의 이름이 서브 쿼리의 FROM 절에 있는 게 아니라 outre query에 있다는 점이다. 

SELECT * FROM review WHERE review.item_id = item.id;

위 코드는 서브 쿼리가 필요로 하는 item 테이블이 outer query에 적혀있기 때문에 위에 '비상관 서브쿼리' 때와 달리 단독으로 실행되지 못한다. 위 코드는 오류가 발생한다. 

 

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

 

위 코드를 해석해보면

1. 일단 item 테이블의 첫 번째 row를 생각합니다.

2. 그 row의 id(item.id) 값과 같은 값을 item_id(review.item_Id) 칼럼에 가진 review 테이블의 row가 있는지 조회한다.

3. 만약에 존재하면(위 파란 줄의 EXIT, EXIT는 우리말로 '존재한다'라는 뜻이다.)

4.WHERE 절은 True가 되고, 1. 에서 생각했던 item 테이블의 row는 최종 조회 결과에 담기게 된다.

이렇게 item 테이블의 두 번째 row에 대해서 2~4의 과정을 반복하고 -> item 테이블의 마지막 row까지 2~4의 과정을 반복하면 된다.

 

서브 쿼리의 종류

1. 단일 값을 리턴하는 서브 쿼리

하나의 값, 단일 값을 리턴하는 서브 쿼리이다. 단일 값은 수학 , 물리 분야에서 스칼라(scalar)라고도 한다. 그래서 이런 서브 쿼리를 스칼라 서브 쿼리라고도 한다.

 

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

3. 하나의 테이블 형태의 결과(여러 column, 여러 row)를 리턴하는 서브 쿼리

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

이런 서브쿼리로 생겨난 derived table은 마치 원래 있던 테이블인 것처럼 사용하면 된다. 대신 derived table에는 alias를 붙여줘야 한다는 규칙이 있다.