1. 비상관 서브쿼리

원래 쿼리와 안의 쿼리와는 완전 상관없이 돌아가는 것

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

ategoryID	CategoryName	Description	(SELECT ProductName FROM Products WHERE ProductID = 1)
1	Beverages	Soft drinks, coffees, teas, beers, and ales	Chais
2	Condiments	Sweet and savory sauces, relishes, spreads, and seasonings	Chais

→ 사실 이거는 의미는 없음

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

ProductID	ProductName	SupplierID	CategoryID	Unit	Price
1	Chais	1	1	10 boxes x 20 bags	18.00
2	Chang	1	1	24 - 12 oz bottles	19.00

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

CategoryID	CategoryName	Description
1	Beverages	Soft drinks, coffees, teas, beers, and ales

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

CategoryID	CategoryName	Description
1	Beverages	Soft drinks, coffees, teas, beers, and ales
3	Confections	Desserts, candies, and sweet breads
4	Dairy Products	Cheeses

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

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

ProductID	ProductName	SupplierID	CategoryID	Unit	Price
9	Mishi Kobe Niku	4	6	18 - 500 g pkgs.	97.00
18	Carnarvon Tigers	7	8	16 kg pkg.	62.50
20	Sir Rodney's Marmalade	8	3	30 gift boxes	81.00

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

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

→ = ANY를 IN 으로 바꿔도 똑같은 결과가 나옴

CategoryID	CategoryName	Description
1	Beverages	Soft drinks, coffees, teas, beers, and ales
3	Confections	Desserts, candies, and sweet breads
4	Dairy Products	Cheeses

2. 상관 서브쿼리

바깥 쿼리와 안쪽 쿼리가 같이 맞물려져서 돌아감

그래서 상관된 테이블들의 닉네임을 만들어줘야함!

SELECT
  ProductID, ProductName,
  (
    SELECT CategoryName 
    FROM Categories C
    WHERE C.CategoryID = P.CategoryID
  ) AS CategoryName
FROM Products P;

ProductID	ProductName	CategoryName
1	Chais	Beverages
2	Chang	Beverages
3	Aniseed Syrup	Condiments

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;

SupplierName	Country	City	CustomersInTheCountry	CustomersInTheCity
Exotic Liquid	UK	Londona	7	0
New Orleans Cajun Delights	USA	New Orleans	13	0
Grandma Kelly's Homestead	USA	Ann Arbor	13

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;

CategoryID	CategoryName	MaximumPrice	AveragePrice
1	Beverages	263.50	37.979167
2	Condiments	43.90	23.062500
3	Confections	81.00	25.160000

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
);

ProductID	ProductName	CategoryID	Price
1	Chais	1	18.00
2	Chang	1	19.00
3	Aniseed Syrup	2	10.00

EXISTS / NOT 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
);

CategoryID	CategoryName
1	Beverages
3	Confections
6	Meat/Poultry