🏠 MySQL
Section 2. SELECT 더 깊이 파보기
Lesson 2. JOIN - 여러 테이블 조립하기

🕹️ 실습 링크 바로가기 ▶️


1. JOIN(INNER JOIN) - 내부 조인

  • 양쪽 모두에 값이 있는 행(NOT NULL) 반환
  • 'INNER '는 선택사항

SELECT * FROM Categories C JOIN Products P ON C.CategoryID = P.CategoryID;
SELECT C.CategoryID, C.CategoryName, P.ProductName FROM Categories C JOIN Products P ON C.CategoryID = P.CategoryID; -- ambiguous 주의!
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;



💡 여러 테이블을 JOIN할 수 있습니다.

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한 테이블 GROUP하기

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; SELECT C.CategoryName, P.ProductName, 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, P.ProductID;



💡 SELF 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번의 전, 마지막 번호의 다음은?



2. LEFT/RIGHT OUTER JOIN - 외부 조인

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

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; -- LEFT를 RIGHT로 바꿔서도 실행해 볼 것
SELECT C.CustomerName, S.SupplierName, C.City, C.Country FROM Customers C LEFT JOIN Suppliers S ON C.City = S.City AND C.Country = S.Country; -- LEFT를 RIGHT로 바꿔서도 실행해 볼 것 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; -- LEFT를 RIGHT로 바꿔서도 실행해 볼 것



3. CROSS JOIN - 교차 조인

  • 조건 없이 모든 조합 반환(A * B)

SELECT E1.LastName, E2.FirstName FROM Employees E1 CROSS JOIN Employees E2 ORDER BY E1.EmployeeID;
🤔 얄코에게 질문하기
강의에서 이해가 안 되거나 실습상 문제가 있는 부분,
설명이 잘못되었거나 미흡한 부분을 메일로 알려주세요!

답변드린 뒤 필요할 경우 본 페이지에
관련 내용을 추가/수정하도록 하겠습니다.
이메일 주소
yalco@kakao.com
메일 제목 (반드시 아래 제목을 붙여넣어주세요!)
[질문] MySQL 2-2
🏠 MySQL