[SQL] MySQL yalco 강의 정리 (2) 서브쿼리/JOIN/UNION 총 정리

안녕하세요 진자이입니다 :) 이번 포스팅에서는 MySQL 강의로, yalco님의 강의를 실습하면서 정리한 내용, 그 중에서도 SELECT 내용에 대한 추가 내용(서브쿼리, JOIN, UNION을 전반적으로 기록하려고 합니다. 좋은 강의 및 실습 자료를 오픈해주신 yalco님께 감사드립니다 :) https://www.yalco.kr/lectures/sql/ 실습 코드도 yalco님 사이트에서 제공하는 실습 코드 작성 프로그램 내 제공되는 샘플 데이터베이스로 진행하게 됩니다.

MySQL

 

---

1. 쿼리 안에 서브쿼리

• 서브쿼리문을 연습할 때는 서브쿼리를 먼저 출력해보고 전체 문장을 출력해보기!

 

비상관 서브쿼리

-- 의미있는 서브쿼리는 아님! -> 관련이 없기 때문
SELECT
	CategoryID, CategoryName, Description
	(SELECT ProductName FROM Products WHERE ProductID = 1)
FROM Categories;

-- 서브쿼리문에서 조건을 설정
SELECT * FROM Products
WHERE Price < (
  SELECT AVG(Price) FROM Products
);

-- 하나의 조건을 할 때는 = 활용
SELECT
  CategoryID, CategoryName, Description
FROM Categories
WHERE
  CategoryID =
  (SELECT CategoryID FROM Products
  WHERE ProductName = 'Chais');

-- 여러 개의 조건을 할 때는 IN 활용
SELECT
  CategoryID, CategoryName, Description
FROM Categories
WHERE
  CategoryID IN
  (SELECT CategoryID FROM Products
  WHERE Price > 50);

 

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

-- Price가 CategoryID가 2인 모든 제품들의 가격보다 커야한다.
SELECT * FROM Products
WHERE Price > ALL (
  SELECT Price FROM Products
  WHERE CategoryID = 2
);

-- '= ANY'를 IN으로 바꾸면 같은 말!
SELECT
  CategoryID, CategoryName, Description
FROM Categories
WHERE
  CategoryID = ANY
  (SELECT CategoryID FROM Products
  WHERE Price > 50);

 

상관 서브쿼리

• 서브쿼리가 바깥 쪽에 있는 서브쿼리와 맞물려서 사용됨.
• 상관 서브쿼리에서는 테이블을 불러올 때 별명(ALIAS)가 사용됨!

-- 서브쿼리문에서 안, 바깥 쪽에서 불러온 두 개의 테이블이 가져와서 사용됨
-- Categories의 ID과 Product의 ID이 같은 제품들을 가져옴
-- 총 ProductID, ProductName, CategoryName 세 개의 컬럼이 가져와짐
SELECT
  ProductID, ProductName,
  (
    SELECT CategoryName FROM Categories C
    WHERE C.CategoryID = P.CategoryID
  ) AS CategoryName
FROM Products P;

-- 컬럼 이름을 (서브쿼리문) AS 컬럼명 과 같이 설정할 수 있음
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;

-- PRICE 조건 설정
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
);

 

• WHERE EXISTS(NOT EXISTS) : 존재하는지, 존재하지 않는지를 확인

-- PRICE 조건 설정으로 여부 확인
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
);

 

 

2. 여러 테이블 조립하기 - JOIN

FROM 테이블1 테이블1별칭 (____) JOIN 테이블2 테이블2별칭 ON ~

 

INNER JOIN - 내부 조인

• 양쪽 모두에 값이 있는 행(NOT NULL) 반환
• ‘INNER’는 선택사항
• 한꺼 번에 여러 테이블도 join 할 수 있다.

-- CategoryID가 두 개의 테이블에서 같은 모든 데이터 불러오기
SELECT * FROM Categories C 
	JOIN Products P ON C.CategoryID = P.CategoryID;

-- CategoryID와 같이 양쪽에 같은 컬럼이 있다면, 꼭 별칭.컬럼 으로 사용해주기
-- 이런 오류를 ambiguous 라고 함
SELECT C.CategoryID, C.CategoryName, P.ProductName
FROM Categories C
JOIN Products P 
  ON C.CategoryID = P.CategoryID; 

-- 활용
SELECT
  CONCAT(
    P.ProductName, ' by ', S.SupplierName
  ) AS Product,
  S.Phone, P.Price
FROM Products P
JOIN Suppliers S
  ON P.SupplierID = S.SupplierID
WHERE Price > 50
ORDER BY ProductName;

-- 여러개의 테이블을 한 꺼번에 조립할 수 있음
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;

 

SELF JOIN 같은 테이블 끼리 JOIN

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;

-- 1번의 전, 마지막 번호의 다음은?

 

LEFT/RIGHT OUTER JOIN - 외부 조인

• 반대쪽에 데이터가 있든 없든(NULL), 선택된 방향에 있으면 출력 - 행 수 결정
• OUTER는 선택사항

-- EMPLOYEEID의 마지막 행은 NULL값이 나올 것을 예상할 수 있어야 함
-- IFNULL을 활용해서 NULL값일 경우 어떤 값을 출력하는지를 적어보자!
SELECT
  E1.EmployeeID, CONCAT_WS(' ', E1.FirstName, E1.LastName) AS Employee,
  E2.EmployeeID, CONCAT_WS(' ', E2.FirstName, E2.LastName) AS NextEmployee
FROM Employees E1
LEFT JOIN Employees E2
ON E1.EmployeeID + 1 = E2.EmployeeID
ORDER BY E1.EmployeeID;

SELECT
  IFNULL(C.CustomerName, '-- NO CUSTOMER --'),
  IFNULL(S.SupplierName, '-- NO SUPPLIER --'),
  IFNULL(C.City, S.City),
  IFNULL(C.Country, S.Country)
FROM Customers C
LEFT JOIN Suppliers S
ON C.City = S.City AND C.Country = S.Country;

 

CROSS JOIN - 교차 조인

• 조건 없이 모든 조합 반환 (A*B)
• 실전에서는 많이 사용하지 않음

SELECT
  E1.LastName, E2.FirstName
FROM Employees E1
CROSS JOIN Employees E2
ORDER BY E1.EmployeeID;

 

 

3. UNION - 집합으로 다루기

• 실전에서는 많이 사용하지 않음
• JOIN과 다름! → JOIN은 열 단위로, UNION은 행 단위로 주로 합침

 

합집합 - UNION

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

 

교집합

SELECT CategoryID AS ID
FROM Categories C, Employees E
WHERE 
  C.CategoryID > 4
  AND E.EmployeeID % 2 = 0
  AND C.CategoryID = E.EmployeeID;

 

차집합

-- NOT IN을 활용하면 됨!
SELECT CategoryID AS ID
FROM Categories
WHERE 
  CategoryID > 4
  AND CategoryID NOT IN (
    SELECT EmployeeID
    FROM Employees
    WHERE EmployeeID % 2 = 0
  );

 

대칭 차집합

-- UNION ALL + WHERE 절 활용
SELECT ID FROM (
  SELECT CategoryID AS ID FROM Categories
  WHERE CategoryID > 4
  UNION ALL
  SELECT EmployeeID AS ID FROM Employees
  WHERE EmployeeID % 2 = 0
) AS Temp 
GROUP BY ID HAVING COUNT(*) = 1;