SQL 함수 간단 실습 :: JOIN (내부 조인, 외부 조인, 셀프 조인)

<JOIN>

• JOIN: 여러개의 테이블을 연결해서 정보를 보는 방법
• 내부조인 (Inner Join): 테이블A와 테이블B 중 Key 값이 같은 정보를 모두 합쳐라

SELECT * FROM categories C
JOIN products P
    ON C.`CategoryID` = P.`CategoryID`;
>> categories (C)와 products (P) 중
`CategoryID`가 같은 데이터를 출력

<결과>

SELECT C.categoryid, C.categoryname, P.productname
FROM categories C
JOIN products P
    ON C.`CategoryID` = P.`CategoryID`;

P=products
C=categories

>> categories(C)와 products(P)에서
CategoryID가 같은 경우에
categories의 categoryid, 
categories의 categoryname,
products의 productname를 출력하시오.

<결과>

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`, `Price` DESC;

P=products
S=suppliers

>> products 와 suppliers 중에서 둘의 SupplierID가 같으면
price > 50인 경우에 `ProductName`와 `Price`를 내림차순하여
P의 productname by S의 suppliername를
product라는 이름을 붙여 출력하고,
S의 phone, P의 price를 출력해라.

<결과>

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

O=orders
P=products
C=categories
D=quantity

>> categories와 products 중에서 둘의 CategoryID 값이 같은 것 중
C의 CategoryID 값, C의 categoryname 값, P의 ProductName 값, O의 quantity 값, O의 orderdate 값,
categories와 orderdetails 중에서 ProductID 값이 같은 것 중
C의 CategoryID 값, C의 categoryname 값, P의 ProductName 값, O의 quantity 값, O의 orderdate 값,
categories와 orders 중에서 OrderID 같은 것 중
C의 CategoryID 값, C의 categoryname 값, P의 ProductName 값, O의 quantity 값, O의 orderdate 값,
을 출력하시오.

<결과>

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.`CategoryName`;
C=categories
P=products
D=orderdetails
O=orders

>> categories과 products의 CategoryID이 같은 항목 중
C의 categoryname, O의 orderdate의 최솟값을 FirstOrder라는 이름으로,
    O의 orderdate의 최댓값을 LastOrder라는 이름으로,
    D의 quantity의 합계를 TotalQuantity라는 이름으로 해서
C의 CategoryName이 같은 것을 뭉쳐 출력하고,
products와 orderdetails의 ProductID이 같은 항목 중
C의 categoryname, O의 orderdate의 최솟값을 FirstOrder라는 이름으로,
    O의 orderdate의 최댓값을 LastOrder라는 이름으로,
    D의 quantity의 합계를 TotalQuantity라는 이름으로 해서
C의 CategoryName이 같은 것을 뭉쳐 출력하고,
categories와 orders의 OrderID이 같은 항목 중
C의 categoryname, O의 orderdate의 최솟값을 FirstOrder라는 이름으로,
    O의 orderdate의 최댓값을 LastOrder라는 이름으로,
    D의 quantity의 합계를 TotalQuantity라는 이름으로 해서
C의 CategoryName이 같은 것을 뭉쳐 출력해라.

<결과>

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.`CategoryName`, P.`ProductName`
ORDER BY TotalQuantity;

C= categories
P= products
D= orderdetails
O= orders

>> categories와 products의 CategoryID이 같은 데이터 중
C의 categoryname, O의 orderdate의 최솟값을 FirstOrder라는 이름으로,
    O의 orderdate의 최댓값을 LastOrder라는 이름으로,
    D의 quantity의 합계를 TotalQuantity라는 이름으로 해서
categories와 orderdetails의 ProductID이 같은 데이터 중 
C의 categoryname, O의 orderdate의 최솟값을 FirstOrder라는 이름으로,
    O의 orderdate의 최댓값을 LastOrder라는 이름으로,
    D의 quantity의 합계를 TotalQuantity라는 이름으로 해서
categories와 orders OrderID이 같은 데이터 중
C의 categoryname, O의 orderdate의 최솟값을 FirstOrder라는 이름으로,
    O의 orderdate의 최댓값을 LastOrder라는 이름으로,
    D의 quantity의 합계를 TotalQuantity라는 이름으로 해서
C의 CategoryName와 P의 ProductName를 뭉쳐서
TotalQuantity를 오름차순으로 출력하시오.

<결과>

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

E1= employees
E2= employees

>> employees 중에서 employeeid + 1을 했을 때 employeeid와 같은 결과가 있으면
E1의 employeeid, E1의 firstname 와 E1의 lastname를 ' '으로 이어붙여 Employee라는 항목으로,
E2의 employeeid, E1의 firstname 와 E1의 lastname를 ' '으로 이어붙여 NextEmployee라는 항목으로
출력하시오.

<결과>

<외부조인(Outer JOIN)>

• 반대쪽에 데이터가 있든 없든(NULL), 선택된 방향에 있는 데이터 출력
• JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용
• LEFT JOIN
  • 왼쪽 테이블을 중심으로 오른쪽의 테이블을 매치
  • 왼쪽 테이블의 한개의 레코드에 여러개의 오른쪽 테이블 레코드가 일치할 경우,   해당 왼쪽 레코드를 여러번 표시
  • 왼쪽은 무조건 표시하고, 매치되는 레코드가 오른쪽에 없으면 NULL 표시

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

C=customers
S=suppliers

>> customers와 suppliers의 City가 같고 Country가 같은 경우 
C의 customername, S의 suppliername, C의 city, C의 country를 출력하시오.

<결과>

SELECT
    IFNULL(C.customername, '--NO CUSTOMER--'),
    IFNULL(S.suppliername, '--NO CUSTOMER--'),
    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`;

C=customers
S=suppliers

>> customers와 suppliers의 City가 같고 Country가 같은 경우
C의 customername를 출력하는데, NULL은 '--NO CUSTOMER--'으로 대체하고,
S의 suppliername를 출력하는데, NULL은 '--NO CUSTOMER--'으로 대체하고,
C의 city를 출력하는데, NULL은 S의 city로 대체하고,
C의 country를 출력하는데, NULL은 S의 country로 대체해 출력해라.

** IFNULL(): NULL을 반환할 때, 다른 값으로 출력할 수 있도록 하는 함수

<결과>