study
[SQL] Chapter 03. Join Union
yz
2022. 6. 15. 18:16
/*
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.dateLIST