서브쿼리 1

순동·2022년 3월 29일
sql
0

📌 서브쿼리란?

서브쿼리란 SQL문 안에 부품처럼 들어가는 SELEC문이다.

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

높은 별점 평균값을 가진 상품부터 내림차순으로 정렬되어 있다.

✅ 안 좋은 평가를 받은 상품들이 무엇인가?

  • 낮은 별점 평균을 가진 상품들만 조회
  • 각 상품을 기준으로 그루핑하였으므로 특정 그룹만 선별하는 HAVING

Tip ❗
Ctrl + T : 새 쿼리창 열기

SELECT AVG(star) FROM copang_main.review;

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

전체 상품의 별점 평균보다 낮은 별점 평균을 가진 상품들만 조회되었다.

위의 3.7273은 다른 쿼리 창에서 별도로 구한 값이다.
하지만 반드시 다른 쿼리 창을 사용할 필요는 없다.

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

3.7273 대신 (SELECT AVG(star) FROM copang_main.review)이라는 SQL문 자체를 넣어줘도 같은 결과가 나타난다. 이러한 SELECT문을 서브쿼리라고 한다.

즉, 서브쿼리는 전체의 SQL문에서 일부를 이루는 또 다른 SQL문이다.

서브쿼리를 사용하기 위해 꼭 지켜야하는 규칙이 있다.
반드시 괄호()로 서브쿼리를 감싸줘야 한다. ❗❗

서브쿼리는 HAVING, SELECT, WHERE, FROM 절에서도 사용 가능하다.

📌 SELECT절 서브쿼리

SELECT 절에 서브쿼리를 사용한다는 건 일반적으로 원래의 테이블에는 없던 새로운 컬럼을 추가해서 보겠다는 걸 의미한다.

우선 상품 정보가 담긴 item 테이블에서 id, name, price 컬럼을 조회하자.

SELECT id, name, price FROM copang_main.item;

여기에 서브쿼리를 이용하여 특별한 컬럼 하나를 더 추가해보자.
서브쿼리를 이용해 추가할 것은 가장 비싼 상품의 가격이다.

SELECT
	id,
    name,
    price,
    (SELECT MAX(price) FROM copang_main.item) AS max_price
FROM copang_main.item;

서브쿼리를 이용해 만든 컬럼에도 alias를 붙일 수 있다.

📌 WHERE절 서브쿼리 1

SELECT
	id,
    name,
    price,
    (SELECT AVG(price) FROM copang_main.item) AS avg_price
FROM copang_main.item;

✅ 평균 가격보다 높은 가격의 상품들만 조회

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

✅ 상품들 중 가격이 가장 비싼 상품 조회

SELECT id, name, price
FROM item
WHERE price = (SELECT MAX(price) FROM item);

✅ 상품들 중 가격이 가장 저렴한 상품 조회

SELECT id, name, price
FROM item
WHERE price = (SELECT MIN(price) FROM item);

📌 WHERE절 서브쿼리 2

이전에 보았던 서브쿼리들은 AVG, MAX처럼 값 하나를 리턴하는 서브쿼리였다.

✅ 리뷰가 최소 3개 이상 달린 상품들의 정보만 조회

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

IN이 있을 경우 뒤에 있는 값들 중 하나라도 같을 경우 조건을 만족한다.
괄호() 안의 서브쿼리는 리뷰의 수가 3개 이상인 모든 상품들의 id를 출력한다.

  • 리뷰 테이블의 각 row를 item_id 컬럼을 기준으로 그루핑
  • 그룹들 중 그 안에 포함된 row 수가 3개 이상인 그룹들

서브쿼리 부분만 다른 쿼리 창에서 실행해보면 다음과 같다.
리뷰 수가 3개 이상인 id만 출력된다.

SELECT item_id
FROM review
GROUP BY item_id HAVING COUNT(*) >= 3

📝 ANY(SOME), ALL

하나의 Column에 여러 개의 row들이 있는 결과를 리턴하는 서브쿼리를 배웠다.

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

IN을 붙여서 유용하게 사용했다. 그러나 IN 말고도 서브쿼리와 함께 유용하게 사용되는 다른 키워드들도 있다.

  1. ANY

테이블에 id(PK), name, category, month, cie_count 컬럼이 있다.

✅ category의 값이 Action인 영화들의 view_count 컬럼

SELECT view_count FROM FOR_TEST.theater WHERE category = 'Action'

위의 SELECT문을 서브쿼리로 사용해보자.

SELECT * FROM FOR_TEST.theater
WHERE view_count > ANY(SELECT view_count FROM FOR_TEST.theater WHERE category = 'ACTION')
	AND category != 'Action'

WHERE절을 자세히 보면 서브쿼리 옆에 ANY라는 키워드가 있다.

WHERE view_count > ANY(서브쿼리)

ANY는 무슨 뜻일까 ❓

(120000, 2300000, 7000000, 8500000) 중 단 하나의(ANY) 값보다도 크다면 True를 리턴한다. 즉, 4개의 값 중에서도 최소값인 120000보다만 큰 값이라면 조건을 만족하게 된다.

view_count가 120000보다 큰 영화들이, 그 중에서도 액션 영화를 제외하고(AND category != 'Action') 조회되었다.

ANY는 영어로 '~중 하나라도' 라는 뜻을 가지며 WHERE절에서 사용될 경우 서브쿼리의 결과에 있는 각 row의 값들 중 하나라도 조건을 만족하는 경우가 있으면 True를 리턴한다는 뜻이다.

ANY와 같은 기능을 하는 SOME도 있다. 같은 결과를 나타낸다.

  1. ALL
SELECT * FROM FOR_TEST.theater
WHERE view_count > ALL(SELECT view_count FROM FOR_TEST.theater WHERE category = 'ACTION')
	AND category != 'Action'

ALL은 '모든~' 이라는 뜻을 갖고 있으며 모든 경우에 대해서 해당 조건이 성립해야 True를 리턴한다.

(120000, 2300000, 7000000, 8500000)에 있는 4가지 값보다 커야 True가 된다. 즉, 최댓값인 8500000보다 커야 True가 된다.

✅ 과제 : 서브쿼리 기초

꽤 오래 전에 등록되었지만 아직까지도 리뷰가 달리고 있는 스테디 셀러 상품들의 리뷰들을 살펴보려고 한다. review 테이블에서

  1. '2018년 12월 31일' 이전에 코팡 사이트에 등록된 상품들에 관한 리뷰들만 추리기
  2. 이때 review 테이블의 모든 컬럼들을 조회

❗ 조인 말고 서브쿼리를 사용

💻 풀이

SELECT *
FROM copang_main.review
WHERE item_id IN
	(SELECT id
	FROM copang_main.item
	WHERE registration_date < '2018-12-31');

profile
순동
이전 포스트

테이블 조인 3

다음 포스트

서브쿼리 2

0개의 댓글