[SQL] Chapter 03. Join Union

 

/*
  1. 테이블간 데이터 연결 및 조회(JOIN)
  JOIN : 둘 이상의 테이블을 연결해서 데이터를 검색하는 방법
       테이블을 서로 연결하기 위해서는 하나 이상의 컬럼을 공유하고 있어야 함
    ON : 두 테이블을 연결할 기준 컬럼 설정 명령어

  - 내부 조인 (INNER JOIN) : JOIN
  - 외부 조인 (OUTER JOIN) : LEFT JOIN, RIGHT JOIN, FULL JOIN
 */
-- JOIN
-- 공통적인 부분만 조회되는 연결 문
/* TB_CUST 테이블과 TB_SaleList 테이블에서
각각 (TB_CUST) ID, (TB_SaleList) CUST_ID 값이 동일한 데이터를 가지고
고객 ID, 고객이름, 판매일자, 과일, 판매수량을 표현 */
-- ** 명시적 표현법 JOIN 문과 ON
-- 판매 현황 리스트 테이블을 기준으로 고객 정보를 JOIN 한 경우
SELECT cust_id,-- 고객 ID
       '' AS CUST_NAME,-- 고객 명
       date,-- 판매일자
       fruit_name,-- 과일이름
       amount -- 판매수량
FROM   tb_salelist A
       JOIN tb_cust B
         ON A.cust_id = B.id;

-- 고객 정보를 기준으로 조회
-- 아래의 결과처럼 고객 정보가 전부 나타난다.
SELECT A.id,
       A.NAME
FROM   tb_cust A

-- 고객정보 테이블을 기준 테이블로 하고 판매 현황 테이블을 서브 테이블로 한 후 
-- JOIN 하였지만 기준테이블에 있는 데이터가 나타나지 않는 경우가 생긴다.
SELECT A.id,
       A.NAME,
       B.date,
       B.fruit_name,
       B.amount
FROM   tb_cust A
       JOIN tb_salelist B
         ON A.id = B.cust_id

-- *** JOIN : 공통적인 부분만 조회되며
-- 연결되는 테이블 간 공유하는 칼럼의 데이터가 둘다 존재해야 데이터를 나타낸다.
-- 묵시적 표현법 : JOIN ON 문을 쓰지 않고 테이블만 나열 후 참조될 컬럼을 정의하는 방법
SELECT A.id         AS ID,
       A.NAME       AS CUSTNAME,
       B.fruit_name AS FRUIT_NAME
FROM   tb_cust A,
       tb_salelist B -- INNER JOIN (JOIN)
WHERE  A.id = B.cust_id

-- 외부 JOIN (LEFT JOIN, RIGHT JOIN)
-- ** LEFT JOIN
--  . 왼쪽에 있는 테이블의 데이터를 기준으로 오른쪽에 있는 테이블의 데이터를 검색하고
--     오른쪽 테이블에 데이터가 없을 경우 NULL 로 표시된다.
/* 왼쪽에 있는 TB_SalesList 의 내용을 기준으로 판매 현황 별 고객 정보를 나타내세요. */
SELECT A.date -- 판매일자
       ,
       A.cust_id -- 고객 ID
       ,
       B.NAME -- 고객 명
       ,
       A.fruit_name -- 과일명
       ,
       A.amount -- 판매수량
FROM   tb_salelist A
       LEFT JOIN tb_cust B -- LEFT OUTER JOIN
              ON A.cust_id = B.id

-- ** RIGHT JOIN (RIGHT INNER JOIN)
-- 오른쪽에 있는 테이블의 데이터를 기준으로 왼쪽에 있는 테이블의 데이터를 검색하고
-- 왼쪽 테이블에 데이터가 없을 경우 NULL 로 표시된다. (JEFT JOIN 과 반대)
/* 오른쪽에 있는 TB_Cust 의 내용을 기준으로 고객 별 판매 현황을 나타내세요. */
SELECT B.id -- 고객
       ,
       B.NAME -- 고객 명
       ,
       A.date -- 판매일자
       ,
       A.fruit_name -- 과일명
       ,
       A.amount -- 숫자
FROM   tb_salelist A
       RIGHT JOIN tb_cust B
               ON A.cust_id = B.id;

-- ** 다중 JOIN
-- 참조할 데이터가 여러 테이블에 있을 때 기준 테이블과 참조 테이블과의 다중 JOIN 으로 데이터를 검색할 수 있다.
/* 판매 현황을 판매일자, 고객이름, 고객 연락처, 판매과일, 과일단가, 판매금액 으로 나타내세요. */
SELECT A.date                 AS SALEDATE -- 판매일자
       ,
       B.NAME                 AS CUSTNAME -- 고객이름
       ,
       B.phone                AS CUSTPHONE -- 고객 연락처
       ,
       A.fruit_name           AS FRUITNAME -- 판매 과일
       ,
       C.unitprice            AS UNITPRICE -- 과일단가
       ,
       A.amount               AS AMOUNT -- 판매수량
       ,
       C.unitprice * A.amount AS SALES_PRICE -- 판매금액
FROM   tb_salelist A
       LEFT JOIN tb_cust B
              ON A.cust_id = B.id
       LEFT JOIN t_fruit C
              ON A.fruit_name = C.fruit_name;

/****** 실습 *******
TB_StockMMrec (자재 입출 이력) A 테이블에서 ITEMCODE 가 NULL 이 아니고, INOUTFLAG (입출여부) = 'I' (입고)
TB_ItemMaster (품목 마스터) B 테이블에서 ITEMTYPE (품목타입) 이 'HALB' 인 것의
A.INOUTDATE,  A.INOUTSEQ,    A.MATLOTNO,    ,A.ITEMCODE,  B.ITEMNAME,    B.CARTYPE 정보를 나타내세요.
(입출 일자)    (입출순번)    (LOT 번호)    (품목코드)    (품명)      (차종)
*/
SELECT A.inoutdate,
       A.inoutseq,
       A.matlotno,
       A.itemcode,
       B.itemname,
       B.cartype
FROM   tb_stockmmrec A
       LEFT JOIN tb_itemmaster B
              ON A.plantcode = B.plantcode
                 AND A.itemcode = B.itemcode
--AND B.ITEMTYPE = 'ROH' -- JOIN 을 하기 전에 TB_ItemMaster 테이블에서 필터링
WHERE  A.itemcode IS NOT NULL
       AND A.inoutflag = 'I'
       AND B.itemtype = 'ROH'; -- JOIN 이후에 검색 결과에서 필터링
-- ** 하위쿼리의 JOIN
/* 고객별 과일의 총 계산 금액 구하기 (고객ID, 고객명, 과일이름, 과일별총구매금액)
   . 구매 내역이 없는 고객은 ID와 이름만 표현 */
-- 산출 과정
-- 1. 기준 테이블 설정(구상) -- 고객별 과일의 총 구매 현황이므로 고객 테이블이 기준이 된다. TB_Cust
-- 2. 고객별 과일별 총 판매수량을 구한다.
SELECT cust_id     AS CUSTID -- 고객 ID
       ,
       fruit_name  AS FRUITNAME -- 과일정보
       ,
       Sum(amount) AS FRUITCOUNT -- 과일 판매 수량
FROM   tb_salelist
GROUP  BY cust_id,
          fruit_name;

-- 3. 고객별 과일의 총 판매 수량에 단가를 곱한다.
-- **** SELECT 구문을 테이블처럼 FROM 절에서 사용하기
SELECT A.custid                   AS CUSTID,
       A.fruitname                AS FRUITNAME,
       A.fruitcount * B.unitprice AS FRUIT_TOTAL_PRICE
FROM   (SELECT cust_id     AS CUSTID -- 고객 ID
               ,
               fruit_name  AS FRUITNAME -- 과일정보
               ,
               Sum(amount) AS FRUITCOUNT -- 과일 판매 수량
        FROM   tb_salelist
        GROUP  BY cust_id,
                  fruit_name) A
       LEFT JOIN t_fruit B
              ON A.fruitname = B.fruit_name
       LEFT JOIN tb_cust C
              ON A.custid = C.id;

SELECT AA.id,
       AA.NAME,
       BB.fruitname,
       BB.fruit_total_price
FROM   tb_cust AA
       LEFT JOIN (SELECT A.custid                   AS CUSTID,
                         A.fruitname                AS FRUITNAME,
                         A.fruitcount * B.unitprice AS FRUIT_TOTAL_PRICE
                  FROM   (SELECT cust_id     AS CUSTID -- 고객 ID
                                 ,
                                 fruit_name  AS FRUITNAME -- 과일정보
                                 ,
                                 Sum(amount) AS FRUITCOUNT
                          -- 과일 판매 수량
                          FROM   tb_salelist
                          GROUP  BY cust_id,
                                    fruit_name) A
                         LEFT JOIN t_fruit B
                                ON A.fruitname = B.fruit_name) BB
              ON AA.id = BB.custid;

-- 간략하게 줄여서 구현
-- 1. 고객별 과일별 총 수량 구하기
SELECT cust_id,-- 고객 ID
       fruit_name,-- 과일 이름
       Sum(amount) AS FRUIT_AMOUNT -- 과일별 총 구매 수량
FROM   tb_salelist
GROUP  BY cust_id,
          fruit_name

-- 2. 단가와 구매수량을 합쳐서 총 금액 산출
SELECT A.cust_id,
       C.NAME,
       A.fruit_name,
       Sum(A.amount)               AS AMOUNT,
       Sum(A.amount * B.unitprice) AS FRUIT_PRICE
FROM   tb_salelist A
       LEFT JOIN t_fruit B
              ON A.fruit_name = B.fruit_name
       RIGHT JOIN tb_cust C
               ON A.cust_id = C.id
GROUP  BY A.cust_id,
          C.NAME,
          A.fruit_name;

/********** 실습 ***************
 과일가게 고객별 총 금액을 구하세요. 판매 내역이 없는 고객은 표현하지 않아도 됨. */
SELECT C.NAME,
       Sum(A.amount * B.unitprice) AS FRUIT_PRICE
FROM   tb_salelist A
       LEFT JOIN t_fruit B
              ON A.fruit_name = B.fruit_name
       LEFT JOIN tb_cust C
              ON A.cust_id = C.id
GROUP  BY C.NAME;

-- REVIEW
SELECT A.cust_id,
       C.NAME,
       Sum(A.amount * B.unitprice) AS FRUIT_PRICE
FROM   tb_salelist A
       LEFT JOIN t_fruit B
              ON A.fruit_name = B.fruit_name
       RIGHT JOIN tb_cust C
               ON A.cust_id = C.id
GROUP  BY A.cust_id,
          C.NAME;

/****** 실습 **************
고객의 총 구매 금액이 가장 큰 고객만 나타내세요. (고객 ID, 고객이름, 총구매금액) */
SELECT TOP (1) A.cust_id,
               C.NAME,
               Sum(A.amount * B.unitprice) AS FRUIT_PRICE
FROM   tb_salelist A
       LEFT JOIN t_fruit B
              ON A.fruit_name = B.fruit_name
       RIGHT JOIN tb_cust C
               ON A.cust_id = C.id
GROUP  BY A.cust_id,
          C.NAME
ORDER  BY fruit_price DESC;

/****** 실습 **************
고객별 총 구매 금액이 40000원이 넘는 고객의 내역만 검색하세요. (고객 ID, 고객이름, 총구매금액) */
SELECT A.cust_id,
       C.NAME,
       Sum(A.amount * B.unitprice) AS FRUIT_PRICE
FROM   tb_salelist A
       LEFT JOIN t_fruit B
              ON A.fruit_name = B.fruit_name
       LEFT JOIN tb_cust C
              ON A.cust_id = C.id
GROUP  BY A.cust_id,
          C.NAME
HAVING Sum(A.amount * B.unitprice) > 40000;

/******** 실습 ***************
2022-06-13 일부터 2022-06-14 일까지의 고객별 총 구매 금액을 구하시오. (고객 ID, 고객이름, 총구매금액) */
SELECT A.cust_id,
       C.NAME,
       Sum(A.amount * B.unitprice) AS FRUIT_PRICE
FROM   tb_salelist A
       LEFT JOIN t_fruit B
              ON A.fruit_name = B.fruit_name
       LEFT JOIN tb_cust C
              ON A.cust_id = C.id
WHERE  A.date BETWEEN '2022-06-13' AND '2022-06-14'
GROUP  BY A.cust_id,
          C.NAME,
          A.date;

/*********************************************************
2. UNION / UNION ALL
   - 다수의 검색 내역 병합
     . 검색한 결과가 여러 개 있을 때 병합

   UNION   : 중복되는 데이터는 합병하여 표시
   UNION ALL : 데이터를 모두 표시

   ** 합병될 컬럼의 데이터 형식과 컬럼의 수는 일치해야 한다. */
SELECT date       AS DATE,
       cust_id    AS CUSTID,
       fruit_name AS FRUITNAME,
       amount     AS AMOUNT
FROM   tb_salelist -- 고객 판매 현황 리스트 8
UNION ALL
SELECT date       AS DATE,
       custcode   AS CUSTCODE,
       fruit_name AS FRUIT_NAME,
       amount     AS AMOUNT
FROM   tb_orderlist -- 거래처 발주 현황 리스트 6
-- UNION 으로 실행 시 6월 12일 사과의 주문 거래처코드와 주문과일, 수량이 일치하여 중복
-- ** 판매 내역/발주 내역인지 확인하기 위하여 TITLE 을 설정하였을 경우
SELECT '판매'     AS TITLE,
       A.date       AS DATE,
       A.cust_id    AS CUST_ID,
       A.fruit_name AS FRUIT_NAME,
       A.amount     AS AMOUNT
FROM   tb_salelist A
       LEFT JOIN tb_cust B
              ON A.cust_id = B.id
UNION
SELECT '발주'   AS TITLE,
       date       AS DATE,
       custcode   AS CUSTCODE,
       fruit_name AS FRUIT_NAME,
       amount     AS AMOUNT
FROM   tb_orderlist

-- 고객명, 거래처명으로 표현하고 싶은 경우 
-- JOIN, 직접입력
-- 고객이 구매한 내역
SELECT '판매'     AS TITLE,
       A.date       AS DATE,
       A.cust_id    AS CUST_ID,
       B.NAME       AS NAME,
       A.fruit_name AS FRUIT_NAME,
       A.amount     AS AMOUNT
FROM   tb_salelist A
       LEFT JOIN tb_cust B
              ON A.cust_id = B.id
UNION
-- 거래처에 발주한 내역
SELECT '발주'   AS TITLE,
       date       AS DATE,
       custcode   AS CUST_ID,
       CASE custcode
         WHEN 1 THEN '대림'
         WHEN 2 THEN '삼전'
         WHEN 3 THEN '하나'
       END        AS NAME,
       fruit_name AS FRUIT_NAME,
       amount     AS AMOUNT
FROM   tb_orderlist

/************* 실습 ************
발주 내역과 판매 내역에 각각의 과일의 총 발주금액과 판매금액을 추가하여 표현하세요
발주금액(ORDER_PRICE * 주문수량(AMOUNT)), 판매금액(PRICE * 구매수량(AMOUNT))
*컬럼 이름은 INOUTPRICE
*발주 금액은 -로 표현 */
SELECT '판매'               AS TITLE,
       A.date                 AS DATE,
       B.id                   AS CUST_ID,
       B.NAME                 AS NAME,
       A.fruit_name           AS FRUIT_NAME,
       A.amount               AS AMOUNT,
       A.amount * C.unitprice AS INOUTPRICE
FROM   tb_salelist A
       LEFT JOIN tb_cust B
              ON A.cust_id = B.id
       LEFT JOIN t_fruit C
              ON A.fruit_name = C.fruit_name
UNION
-- 거래처에 발주한 내역
SELECT '발주'                  AS TITLE,
       date                      AS DATE,
       A.custcode                AS CUSTCODE,
       CASE A.custcode
         WHEN 1 THEN '대림'
         WHEN 2 THEN '삼전'
         WHEN 3 THEN '하나'
       END                       AS NAME,
       A.fruit_name              AS FRUIT_NAME,
       A.amount                  AS AMOUNT,
       A.amount * -C.order_price AS INOUTPRICE
FROM   tb_orderlist A
       LEFT JOIN t_fruit C
              ON A.fruit_name = C.fruit_name

-- ** 조회 결과 일자별로 정렬 ORDER BY
-- ** 주의 **
-- UNION 할 대상의 조회내역은 개별적으로 ORDER BY 를 할 수 없다.
SELECT '판매'               AS TITLE,
       A.date                 AS DATE,
       B.id                   AS CUST_ID,
       B.NAME                 AS NAME,
       A.fruit_name           AS FRUIT_NAME,
       A.amount               AS AMOUNT,
       A.amount * C.unitprice AS INOUTPRICE
FROM   tb_salelist A
       LEFT JOIN tb_cust B
              ON A.cust_id = B.id
       LEFT JOIN t_fruit C
              ON A.fruit_name = C.fruit_name
UNION
-- 거래처에 발주한 내역
SELECT '발주'                  AS TITLE,
       date                      AS DATE,
       A.custcode                AS CUST_ID,
       CASE A.custcode
         WHEN 1 THEN '대림'
         WHEN 2 THEN '삼전'
         WHEN 3 THEN '하나'
       END                       AS NAME,
       A.fruit_name              AS FRUIT_NAME,
       A.amount                  AS AMOUNT,
       A.amount * -C.order_price AS INOUTPRICE
FROM   tb_orderlist A
       LEFT JOIN t_fruit C
              ON A.fruit_name = C.fruit_name
ORDER  BY A.date; -- 전체 UNION의 정렬
/************** 실습 ******************
 두가지 방법으로 과일가게의 일자별 마진 금액을 산출하세요.
 * 위의 UNION 방법을 사용하고 구할 것
 * UNION 을 쓰지 말고 구할 것

 * 마진 금액 : 판매한 금액 - 발주 금액
 * 표현할 컬럼 : DATE,  MARGIN */
SELECT date                        AS DATE,
       Sum(X.inprice - X.outprice) AS MARGIN
FROM   (SELECT date                   AS DATE,
               A.amount * C.unitprice AS INPRICE
        FROM   tb_salelist A
               LEFT JOIN tb_cust B
                      ON A.cust_id = B.id
               LEFT JOIN t_fruit C
                      ON A.fruit_name = C.fruit_name
        UNION
        SELECT date                      AS DATE,
               A.amount * -C.order_price AS OUTPRICE
        FROM   tb_orderlist A
               LEFT JOIN t_fruit C
                      ON A.fruit_name = C.fruit_name) X
GROUP  BY date
ORDER  BY date;

-- REVIEW
-- 1. UNION을 써서 구하기
SELECT A.date,
       Sum(A.inoutprice) AS INOUTPRICE
FROM   (SELECT A.date                 AS DATE,
               A.amount * C.unitprice AS INOUTPRICE
        FROM   tb_salelist A
               LEFT JOIN tb_cust B
                      ON A.cust_id = B.id
               LEFT JOIN t_fruit C
                      ON A.fruit_name = C.fruit_name
        UNION
        SELECT A.date                    AS DATE,
               A.amount * -C.order_price AS INOUTPRICE
        FROM   tb_orderlist A
               LEFT JOIN t_fruit C
                      ON A.fruit_name = C.fruit_name) A
GROUP  BY A.date;

-- 2. UNION을 쓰지 않고 구하기
-- 1) SALES LIST의 일자별 이윤 구하기
SELECT A.date,
       Sum(A.amount * B.unitprice) AS SALES_PRICE
FROM   tb_salelist A
       LEFT JOIN t_fruit B
              ON A.fruit_name = B.fruit_name
GROUP  BY A.date;

-- 2) ORDER LIST의 일자별 발주금액 구하기
SELECT A.date,
       Sum(A.amount * -B.order_price) AS ORDER_PRICE
FROM   tb_orderlist A
       LEFT JOIN t_fruit B
              ON A.fruit_name = B.fruit_name
GROUP  BY A.date;

-- 3) 일자별 판매금액과 일자별 발주 금액 합하기
SELECT A.date                     AS DATE,
       Isnull(A.sales_price, 0)
       + Isnull(B.order_price, 0) AS MARGIN
FROM   (SELECT A.date,
               Sum(A.amount * B.unitprice) AS SALES_PRICE
        FROM   tb_salelist A
               LEFT JOIN t_fruit B
                      ON A.fruit_name = B.fruit_name
        GROUP  BY A.date) A
       LEFT JOIN (SELECT A.date,
                         Sum(A.amount * -B.order_price) AS ORDER_PRICE
                  FROM   tb_orderlist A
                         LEFT JOIN t_fruit B
                                ON A.fruit_name = B.fruit_name
                  GROUP  BY A.date) B
              ON A.date = B.date