MySQL 쿼리 안에 서브쿼리

0. Subquery 사용법

- Subquery란? 하나의 SQL 쿼리 안에 또다른 SQL 쿼리가 있는것을 의미한다.

 

자주 쓰이는 SubQuery 유형 살펴보기

1. Where에 들어가는 Subquery

WHERE (필드명) in (or 조건식) (subquery)

 

2. Select에 들어가는 Subquery

기존 테이블과 함께 보고싶은 통계 데이터를 손쉽게 붙일때 사용

SELECT 필드명, 필드명, (SELECT...) FROM

 

3. FROM에 들어가는 Subquery

내가 만든 Table을 바로 사용하거나, 혹은 서브쿼리로 만든 Table을 Join할때 사용

 

1. 비상관 서브쿼리

SELECT
  CategoryID, CategoryName, Description,
  (SELECT ProductName FROM Products WHERE ProductID = 1)
FROM Categories;

SELECT * FROM Products
WHERE Price < (
  SELECT AVG(Price) FROM Products
);

AVG(Price) 평균보다 작은 Price만 나오게 조건 설정 (AVG(Price) = 28.00)

SELECT
  CategoryID, CategoryName, Description
FROM Categories
WHERE
  CategoryID =
  (SELECT CategoryID FROM Products
  WHERE ProductName = 'Chais');

 

WHERE 구문을 보면, Products Table 안에 ProductName = 'Chais', 즉 CategoryID가 1인 것만 가져오겠다.

SELECT
  CategoryID, CategoryName, Description
FROM Categories
WHERE
  CategoryID IN
  (SELECT CategoryID FROM Products
  WHERE Price > 50);

 

먼저 SELECT CategoryID FROM Products WHERE Price > 50 을 살펴보자.

이후 적용해보면

 

~ ALL	서브쿼리의 모든 결과에 대해 ~하다
~ ANY	서브쿼리의 하나 이상의 결과에 대해 ~하다

SELECT * FROM Products
WHERE Price > ALL (
  SELECT Price FROM Products
  WHERE CategoryID = 2
);

=> 서브 쿼리는 CategoryID = 2인 것 중 모든 Price를 뜻함 

SELECT Price FROM Products
  WHERE CategoryID = 2

 => Max(price) WHERE CategoryID = 2와 같다.

SELECT
  CategoryID, CategoryName, Description
FROM Categories
WHERE
  CategoryID = ANY
  (SELECT CategoryID FROM Products
  WHERE Price > 50);

 

서브쿼리 중 어떤것과도 같으면 된다. ANY === IN과 비슷.

 

비상관 서브쿼리는 기존 쿼리와 내부의 서브 쿼리가 독자적으로 실행된다는 것을 의미한다.

 

2. 상관 서브쿼리

SELECT
  ProductID, ProductName,
  (
    SELECT CategoryName FROM Categories C
    WHERE C.CategoryID = P.CategoryID // 서브 쿼리 안에 있는 Categories C 와 밖에 있는 P가 동일한 경우.
  ) AS CategoryName
FROM Products P;

 

=> 즉 서브 쿼리 안과 밖이 상관이 있어짐

 

SELECT
  SupplierName, Country, City,
  (
    SELECT COUNT(*) FROM Customers C
    WHERE C.Country = S.Country
  ) AS CustomersInTheCountry,
  (
    SELECT COUNT(*) FROM Customers C
    WHERE C.Country = S.Country 
      AND C.City = S.City
  ) AS CustomersInTheCity
FROM Suppliers S;

SELECT
  CategoryID, CategoryName,
  (
    SELECT MAX(Price) FROM Products P
    WHERE P.CategoryID = C.CategoryID
  ) AS MaximumPrice,
  (
    SELECT AVG(Price) FROM Products P
    WHERE P.CategoryID = C.CategoryID
  ) AS AveragePrice
FROM Categories C;

SELECT
  ProductID, ProductName, CategoryID, Price
  -- ,(SELECT AVG(Price) FROM Products P2
  -- WHERE P2.CategoryID = P1.CategoryID)
FROM Products P1
WHERE Price < (
  SELECT AVG(Price) FROM Products P2
  WHERE P2.CategoryID = P1.CategoryID
);

=> 같은 테이블도 상관 서브쿼리를 만들 수 있다.

SELECT
  ProductID, ProductName, CategoryID, Price
   ,(SELECT AVG(Price) FROM Products P2
   WHERE P2.CategoryID = P1.CategoryID)
FROM Products P1
WHERE Price < (
  SELECT AVG(Price) FROM Products P2
  WHERE P2.CategoryID = P1.CategoryID
);

평균가보다 낮은 것을 확인.

 

 

EXISTS / NOT EXISTS 연산자

EXISTS = 있는가??

 

SELECT
  CategoryID, CategoryName
  -- ,(SELECT MAX(P.Price) FROM Products P
  -- WHERE P.CategoryID = C.CategoryID
  -- ) AS MaxPrice
FROM Categories C
WHERE EXISTS (
  SELECT * FROM Products P
  WHERE P.CategoryID = C.CategoryID
  AND P.Price > 80 // 조건들 중에, P.Price > 80이 넘는게 있는가??
);

 

 

3. with 절로 깔끔하게 쿼리문 정리

Select a.enrolled_id, a.done_cnt, b.total_cnt, round(a.done_cnt/b.total_cnt, 2) AS ratio From

(

SELECT enrolled_id, count(done) AS done_cnt FROM enrolleds_detail

WHERE done = 1

group by enrolled_id

) a

inner JOIN

(

SELECT enrolled_id, count(done) AS total_cnt FROM enrolleds_detail

group by enrolled_id

) b

on a.enrolled_id = b.enrolled_id

 

이러한 코드를

=>

with table1 as (

SELECT enrolled_id, count(done) AS done_cnt FROM enrolleds_detail

WHERE done = 1

group by enrolled_id

), table2 as (

SELECT enrolled_id, count(done) AS total_cnt FROM enrolleds_detail

group by enrolled_id

)

 

Select a.enrolled_id, a.done_cnt, b.total_cnt, round(a.done_cnt/b.total_cnt, 2) AS ratio

From table1 a

inner join table2 b on a.enrolled_id = b.enrolled_id

이렇게 정리해 줄 수 있다.