[Database][SQL] Self Join / Outer Join

Inner Join 복습 문제

문제 

• 회원아이디 b001 회원의 마일리지 값보다 큰 회원정보 조회
• 조회칼럼 : 회원아이디, 이름

-- 1
SELECT mem_id, mem_name
FROM member
WHERE mem_mileage > (SELECT mem_mileage
FROM member
WHERE mem_id ='b001') ;

-- 2
SELECT A.mem_id, A.mem_name
FROM (SELECT mem_id, mem_name
FROM member
WHERE mem_mileage > (SELECT mem_mileage FROM member WHERE mem_id ='b001')) A ;

-- 3
SELECT mem_id, mem_name
FROM member, 
(SELECT mem_mileage
FROM member
WHERE mem_id ='b001') MEM
WHERE member.mem_mileage > MEM.mem_mileage ;

 

결과는 모두 동일하다.

 

 

 

 

 

 

 

 

🎀 Self Join

• Self Join으로 처리하기
• 셀프조인 시 : 조건은 한쪽 m2 테이블에 적용
• 조회 시 : m1 테이블에서 조회

Self Join 문제

문제 1번

• Inner Join 문제 1번에 Self join을 사용해 보기

SELECT m1.mem_id, m1.mem_name
FROM member m1, member m2
WHERE m2.mem_id = 'b001'
AND m1.mem_mileage > m2.mem_mileage;

Inner Join 문제 1번과 결과가 동일하다.

 

🎀 Outer Join

• 특정 테이블 전체에 대한 집계를 하고자 할 때 사용
• OUTER JOIN 종류
  1. LEFT OUTER JOIN : 테이블 순서 중 왼쪽에 위치한 테이블 전체
  2. RIGHT OUTER JOIN : 테이블 순서 중 오른쪽에 위치한 테이블 전체
  3. FULL OUTER JOIN : 왼쪽, 오른쪽 전체 테이블 (Oracle DB만 사용 가능)
• OUTER JOIN 개념
  1. Outer Join은 표준방식으로만 사용해야 하며 일반 조건은 무조건 On() 내부에 작성해야 한다
  2. 일반 조건을 where 절을 사용하면 Outer 전체 계념이 적용이 안된다
  3. Inner Join을 만족해야 한다(Inner Join 개념을 그대로 적용)
  4. Left or Right 기준으로 같으면 같은 조건대로 조회하고 다르면 null로 조회된다

Outer join 문제

문제 1번

• 상품분류 전체에 대한 상품종류 집계

SELECT lprod_nm, COUNT(prod_lgu) AS cnt
FROM lprod 
	LEFT JOIN prod
		ON (lprod_gu = prod_lgu)
GROUP BY lprod_gu
ORDER BY cnt;

---

문제 2 - 1번

• 회원별 구매금액의 총액 조회
• 2005년 5월에 구매한 내역
• 조회칼럼 : 회원이름, 구매금액 총액
  

  -- 일반 방식
  SELECT mem_name, SUM(cart_qty*prod_sale) AS total
  FROM member, prod, cart
  WHERE mem_id=cart_member
  AND cart_prod = prod_id
  AND substring(cart_no,1,6) = '200505'
  group BY mem_id, mem_name
  ORDER BY total desc;
  
  -- 표준 방식
  SELECT mem_name, SUM(cart_qty*prod_sale) AS total
  FROM member INNER join cart
  ON (mem_id=cart_member AND substring(cart_no,1,6) = '200505')
  INNER JOIN prod
  ON (cart_prod = prod_id)
  group BY cart_member
  ORDER BY total desc;​

 

찾은 행 13개

5월에 구매한 내역이 있는 고객만 조회됨

 

 

 

 

 

 

 

 

---

문제 2 - 2번

• 문제 2 - 1에 이어서 회원 전체에 대해서 문제 2 - 1조건으로 조회하기
• 총회원은 28명

SELECT mem_name, SUM(nvl(cart_qty,0)*nvl(prod_sale,0)) AS total
FROM member left join cart
ON (mem_id=cart_member)
left JOIN prod
ON (cart_prod = prod_id)
group BY mem_id, mem_name
ORDER BY total desc;

 

찾은 행 28개

구매이력이 없는 회원도 모두 조회되며

NULL값을 가지면 0으로 대체됨

 

 

 

 

 

 

---

문제 3 - 1번

• 2005년도 월별 판매 현황 검색하기
• 조회칼럼 : 판매월, 월별 총판매수량, 월별 총판매금액

SELECT substring(cart_no,1,6) AS cart_no, sum(cart_qty) AS cart_qty, sum(cart_qty*prod_sale) AS total
	FROM cart 
		left join prod
			ON (cart_prod = prod_id
					AND SUBSTRING(cart_no,1,4) = '2005')
	GROUP BY substring(cart_no,1,6);

 

---

문제 3 - 2번

• 문제 3 - 1번 조건에서 1월 ~ 12월까지 모두 표현하고자 한다면?
  
• 방법 1 - UNION 사용

SELECT
    months.month,
    NVL(SUM(A.cart_qty), 0) AS total_sales,
    NVL(SUM(A.cart_qty*B.prod_sale),0) AS total
FROM (
    SELECT '200501' AS month
    UNION SELECT '200502'
    UNION SELECT '200503'
    UNION SELECT '200504'
    UNION SELECT '200505'
    UNION SELECT '200506'
    UNION SELECT '200507'
    UNION SELECT '200508'
    UNION SELECT '200509'
    UNION SELECT '200510'
    UNION SELECT '200511'
    UNION SELECT '200512'
) AS months
LEFT JOIN cart A ON (SUBSTRING(A.cart_no, 1, 6) = months.month)
LEFT join prod B ON (A.cart_prod = B.prod_id)
GROUP BY months.month
ORDER BY months.month;

• 방법 2 - 새로운 날짜 테이블 생성

-- 테이블 생성
CREATE TABLE date_mm (
	mm CHAR(2) NOT NULL
);

-- 값 입력
INSERT INTO date_mm VALUES('01');
INSERT INTO date_mm VALUES('02');
INSERT INTO date_mm VALUES('03');
INSERT INTO date_mm VALUES('04');
INSERT INTO date_mm VALUES('05');
INSERT INTO date_mm VALUES('06');
INSERT INTO date_mm VALUES('07');
INSERT INTO date_mm VALUES('08');
INSERT INTO date_mm VALUES('09');
INSERT INTO date_mm VALUES('10');
INSERT INTO date_mm VALUES('11');
INSERT INTO date_mm VALUES('12');

-- 조회 코드 작성
SELECT date_mm.mm,
	SUM(nvl(cart_qty,0)) AS total_qty,
	SUM(nvl(cart_qty*prod_sale,0)) AS total
FROM date_mm
	LEFT JOIN cart
		ON(date_mm.mm = SUBSTRING(cart_no,5,2))
	LEFT JOIN prod
		ON(cart_prod = prod_id
			AND substring(cart_no,1,4) = '2005')
GROUP BY date_mm.mm;

 

결과는 동일하다.

 

 

 

 

 

 

 

 

 

---

문제 4번

• 2005년도에 대한 전체 거래처별 총매출금액 조회하기
• 조회칼럼 : 거래처 코드, 거래처명, 총매출금액
• 정렬 : 총매출금액 - 내림차순

SELECT buyer_id, buyer_name, SUM(nvl(cart_qty*prod_sale,0)) AS total
FROM buyer 
	LEFT join prod
		ON (buyer_id = prod_buyer)
	LEFT join cart
		ON(prod_id = cart_prod AND substring(cart_no,1,4) = '2005' )
GROUP BY buyer_id
ORDER BY total DESC;

 

찾은 행 13개