Sub Query

비상관 서브쿼리

• 본 쿼리에 독립적인 서브쿼리문
• ALL : 서브쿼리의 모든 결과에 대해 적용
• ANY : 서브쿼리의 하나 이상의 결과에 대해 적용

SELECT * FROM Products
WHERE Price < (
  SELECT AVG(Price) FROM Products	-- 서브쿼리문
);
-- Product테이블에서 평균 이하의 가격을 가진 데이터를 조회

SELECT
  CategoryID, CategoryName, Description
FROM Categories
WHERE
  CategoryID IN
  (SELECT CategoryID FROM Products	-- 다른 테이블의 데이터를 필터링에 사용함
  WHERE Price > 50);
-- 서브쿼리문 : Products 테이블에서 가격이 50보다 큰 카테고리를 조회
-- 본 쿼리문 : Categories 테이블에서 CategoryID가 서브쿼리의 결과에 포함되는 데이터를 조회

SELECT * FROM Products
WHERE Price > ALL (
  SELECT Price FROM Products
  WHERE CategoryID = 2
);
-- Products테이블에서 CategoryID가 2인 모든 물건보다 비싼 데이터를 조회

SELECT
  CategoryID, CategoryName, Description
FROM Categories
WHERE
  CategoryID = ANY
  (SELECT CategoryID FROM Products
  WHERE Price > 50);
-- Products테이블에서 가격이 50보다 큰 물건의 카테고리를 Categories테이블에서 조회

상관 서브쿼리

• 본 쿼리에 의존하는 서브쿼리문
• EXISTS/ NOT EXISTS : 서브쿼리의 결과에 데이터가 존재하는지 여부

SELECT
  ProductID, ProductName,
  (
    SELECT CategoryName FROM Categories C	-- Categories테이블을 C라는 이름으로 참조
    WHERE C.CategoryID = P.CategoryID
  ) AS CategoryName
FROM Products P;							-- Products테이블을 P라는 이름으로 참조
-- Products테이블에서 ProductID, ProductName조회
-- Categories테이블에서 각 Product에 해당하는 CategoryName을 가져옴

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
  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
  CategoryID, CategoryName
FROM Categories C
WHERE EXISTS (
  SELECT * FROM Products P
  WHERE P.CategoryID = C.CategoryID
  AND P.Price > 80
);
-- 각 카테고리에 속한 제품들 중 가격이 80보다 큰 제품이 있는 카테고리를 조회

JOIN

• 테이블 합치기
• 기본적으로 내부(INNER) JOIN : 양쪽 모두 값이 있는 행만 반환

SELECT * FROM Categories C
JOIN Products P 
  ON C.CategoryID = P.CategoryID;
-- Categories테이블과 Products테이블을 CategoryID를 기준으로 JOIN

SELECT CategoryID, C.CategoryName, P.ProductName
FROM Categories C
JOIN Products P 
  ON C.CategoryID = P.CategoryID; 

-- ERROR) ambiguous : 양쪽 테이블에 이름이 같은 COLUMN이 있을 경우 테이블을 명시해야 함
-- CategoryID가 어느 테이블의 것인지 모름

SELECT 
  C.CategoryID, C.CategoryName, 
  P.ProductName, 
  O.OrderDate,
  D.Quantity
FROM Categories C
JOIN Products P 
  ON C.CategoryID = P.CategoryID
JOIN OrderDetails D
  ON P.ProductID = D.ProductID
JOIN Orders O
  ON O.OrderID = D.OrderID;
-- 여러 테이블을 JOIN

SELECT 
  C.CategoryName,
  MIN(O.OrderDate) AS FirstOrder,
  MAX(O.OrderDate) AS LastOrder,
  SUM(D.Quantity) AS TotalQuantity
FROM Categories C
JOIN Products P 
  ON C.CategoryID = P.CategoryID
JOIN OrderDetails D
  ON P.ProductID = D.ProductID
JOIN Orders O
  ON O.OrderID = D.OrderID
GROUP BY C.CategoryID;
-- JOIN한 테이블을 CategoryID로 그룹화

SELECT
  E1.EmployeeID, CONCAT_WS(' ', E1.FirstName, E1.LastName) AS Employee,
  E2.EmployeeID, CONCAT_WS(' ', E2.FirstName, E2.LastName) AS NextEmployee
FROM Employees E1 JOIN Employees E2
ON E1.EmployeeID + 1 = E2.EmployeeID;
-- 같은 테이블끼리 JOIN할 때는 다른 이름으로 참조

LEFT/RIGHT JOIN

• 외부(OUTER) JOIN : 한쪽만 값이 있어도 반환
• LEFT JOIN : 오른쪽 값이 없어도 가져옴
• RIGHT JOIN : 왼쪽 값이 없어도 가져옴

SELECT
  C.CustomerName, S.SupplierName,
  C.City, C.Country
FROM Customers C					-- LEFT : Customers / RIGHT : Suppliers
LEFT JOIN Suppliers S				-- Supplier가 없는 Customer도 출력
-- RIGHT JOIN Suppliers S			-- Customer가 없는 Supplier도 출력
ON C.City = S.City AND C.Country = S.Country;

• LEFT JOIN

CustomerName	SupplierName
A	A'
B	B'
...	...
F	F'
G	NULL

• RIGHT JOIN

CustomerName	SupplierName
A	A'
B	B'
...	...
F	F'
NULL	G'

CROSS JOIN

• 모든 조합을 반환

SELECT
  E1.LastName, E2.FirstName
FROM Employees E1
CROSS JOIN Employees E2
ORDER BY E1.EmployeeID;
-- 이름과 성의 모든 조합을 반환

UNION

• 서로다른 테이블, column들을 같은 column으로 이어서 보여줌
• UNION : 중복을 제거한 집합
• UNION ALL : 중복을 포함한 집합

SELECT CustomerName AS Name, City, Country, 'CUSTOMER'
FROM Customers
UNION
SELECT SupplierName AS Name, City, Country, 'SUPPLIER'
FROM Suppliers
ORDER BY Name;
-- Customer와 Supplier의 데이터를 하나의 테이블로 합침
-- COLUMN의 개수가 같아야 함

참고자료

• 왕초보용! 갖고 노는 MySQL 데이터베이스 강좌