MariaDB 데이터베이스[database, DB] 아우터조인(Outer Join) / 셀프조인(Self Join)

https://coding-heading.tistory.com/56

MariaDB 데이터베이스[database, DB] 조인(Join)

* OUTER JOIN

 - 특정테이블 전체에 대한 집계를 하고자 할 때 사용
 - Outer Join 종류
    - Left Outer Join : 테이블 순서중 왼쪽에 위치한 테이블 전체
    - Right Outer Join : 오른쪽에 위치한 테이블 전체
    - Full Outer Join : 왼쪽 + 오른쪽 전체 / OracleDB에서만 사용가능
    
 * Outer Join 개념
    - Inner Join을 만족해야함 (Inner Join개념을 그대로 적용)
    - 일반 조건은 무조건 ON()내부에 작성해야 함(where절 사용하면 Outer전체의 개념 적용 X)
    - Left or Right 기준으로, 같으면 같은 조건대로 조회, 다르면 NuLL 조회

---

예제1)

 - 회원별 구매금액의 총액 조회하기
 - 2005년 5월에 구매한 내역이다.
 - 조회컬럼 : 회원이름, 구매금액의 총액

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 mem_name, mem_id
ORDER BY mem_name DESC;

 

 

 

* 조건에 일치하는 값만 나온다. = 값이 없는 결과는 나오지 않았다.

행 : 13개

 

 

 

위 문제에 이어서

 - 회원 전체에 대해서  위 조건으로 조회하기

       => 값이 없는 회원도 0 출력해 주기

SELECT mem_name, SUM(nvl(cart_qty * prod_sale, 0)) AS total
FROM member
	  LEFT OUTER JOIN cart
	  	ON(mem_id = cart_member AND SUBSTRING(cart_no,1,6) = '200505')
	  LEFT OUTER JOIN prod
	   ON(cart_prod = prod_id)
GROUP BY mem_name, mem_id
ORDER BY mem_name DESC;

 

 

 

* 조건에 일치하지 않은 값도 나왔다.

행 : 28개

SUM() 함수 내부에 NVL() 함수를 사용해서 NuLL값을 0으로 바꿔주어야 한다.

 

 

 

 

예제 2)

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

SELECT SUBSTRING(cart_no,5,2) AS 판매월,
		 SUM(cart_qty) AS 월별_총_판매수량,
		 SUM(cart_qty*prod_sale) AS 월별_총_판매금액
FROM cart
		LEFT OUTER JOIN prod
		ON(cart_prod = prod_id 
		    AND SUBSTRING(cart_no,1,4) = '2005')
GROUP BY 판매월;

 

 

 

 

 

위의 문제에 이어서

 - 1월~ 12월까지 모두 표현하고자 한다면?

CREATE TABLE mnth(
cart_month CHAR(2) NOT NULL);
INSERT INTO mnth (cart_month) VALUES(01);
INSERT INTO mnth (cart_month) VALUES(02);
INSERT INTO mnth (cart_month) VALUES(03);
INSERT INTO mnth (cart_month) VALUES(04);
INSERT INTO mnth (cart_month) VALUES(05);
INSERT INTO mnth (cart_month) VALUES(06);
INSERT INTO mnth (cart_month) VALUES(07);
INSERT INTO mnth (cart_month) VALUES(08);
INSERT INTO mnth (cart_month) VALUES(09);
INSERT INTO mnth (cart_month) VALUES(10);
INSERT INTO mnth (cart_month) VALUES(11);
INSERT INTO mnth (cart_month) VALUES(12);

이미 있는 데이터 구조를 해치지 않기 위해서 새로운 테이블을 하나 생성한 뒤 1~12월까지의 값을 넣어준다.

SELECT A.cart_month, nvl(B.total_qty,0) , nvl(B.total,0)
FROM mnth A
		LEFT OUTER JOIN (SELECT SUBSTRING(cart_no,5,2), 
			 SUM(cart_qty) AS total_qty,
			 SUM(prod_sale*cart_qty) AS total 
				FROM  cart 
				LEFT OUTER JOIN prod
			ON(prod_id = cart_prod )
			WHERE LEFT(cart_no,4) = '2005'
			GROUP BY SUBSTR(cart_no,5,2)) B
			ON(B.mnth = A.cart_month);

1~12 중 월이 같은 달의 행에 값을 넣어준다.

 

 

예제 3)

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

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

---

* union

 - 조회결과와 조회결과를 횡단 위로 합치는 기능
 - 조회결과들 간의 컬럼의 갯수는 동일해야 함
 - 조회결과들간의 데이터 타입은 동일해야 함

 

 * Union을 사용 시 
 - 정렬은 제일 마지막에 정의
 - 컬럼명은 첫 번째 select문의 컬럼이름을 따름

 

SELECT 'member', mem_id, mem_name
FROM member
UNION
SELECT 'cart', cart_member, cart_prod
FROM cart;

 

 

 

member와 cart 가 합쳐졌다.

member가 다 출력된 뒤,

그 아래로 cart가 붙었다고 생각하면 쉽다.

 

 

 

 

* UNION VS UNION ALL

SELECT 'member', mem_id, mem_name
FROM member
UNION
SELECT 'cart', cart_member, cart_prod
FROM cart;

 

 - Union : 중복제거

 

 

 

SELECT 'member', mem_id, mem_name
FROM member
UNION ALL
SELECT 'cart', cart_member, cart_prod
FROM cart;

 - Union All : 중복포함(실체 전체)

 

 

 

 

 

 

예제 1) 

 - 회원전체에 대한 총 구매금액을 조회하기
 - 조회컬럼 : 회원아이디, 회원이름, 총 구매금액
 - 마지막 행에는 총 구매금액의 총합

SELECT mem_id, mem_name, sum(nvl(cart_qty * prod_sale,0)) AS total
FROM member
	  LEFT OUTER JOIN cart
	  ON(mem_id = cart_member)
	  LEFT OUTER JOIN prod
	  ON(cart_prod = prod_id)
GROUP BY mem_id, mem_name
UNION
SELECT '', '', sum(nvl(cart_qty * prod_sale,0)) AS total
FROM member
	  LEFT OUTER JOIN cart
	  ON(mem_id = cart_member)
	  LEFT OUTER JOIN prod
	  ON(cart_prod = prod_id)
GROUP BY '', '';

 

 

 

 

 

 

 

 

 

 

 - 위결과에서 총 구매금액 순으로 내림차순으로 정렬
 - 단, 총 구매금액의 총합은 마지막행에 표시

SELECT A.mem_id, A.mem_name, A.total
	FROM(SELECT mem_id, mem_name, sum(nvl(cart_qty * prod_sale,0)) AS total
			FROM member
		  LEFT OUTER JOIN cart
		  ON(mem_id = cart_member)
		  LEFT OUTER JOIN prod
		  ON(cart_prod = prod_id)
		GROUP BY mem_id, mem_name
		ORDER BY total DESC ) A
UNION
SELECT '','', sum(nvl(cart_qty * prod_sale,0)) AS total
FROM member
	  LEFT OUTER JOIN cart
	  ON(mem_id = cart_member)
	  LEFT OUTER JOIN prod
	  ON(cart_prod = prod_id)
GROUP BY '', '';

 

 

 

 

 

 

 

 

---

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

 

 

 

- 셀프조인 시 : 조건은 한쪽 테이블에 적용
- 조회 시 : M1 테이블에서 조회

 

 

 

 

 * 자연스러운 조인 (natural join)

SELECT *
FROM member A, member B
WHERE A.mem_id = B.mem_id;

 - 컬럼명이 같은 경우

* 값에 의한 연결

SELECT *
from cart, lprod
WHERE SUBSTRING(cart_prod,1,2) = lprod_gu;

 조인이라고 칭하지는 않음