(MariaDB) SQL - Join / Union / Exists / Update

 

 Self Join  

조건은 한 쪽 M2 테이블에 적용

조회시 M1 테이블에서 조회

SELECT M1.mem_id , M1.mem_name
FROM member M1 , member M2
WHERE M2.mem_id = 'b001'
AND M1.mem_mileage > M2.mem_mileage;

 

▶ 조건은 한 쪽 M2 테이블에 적용 / 조회시 M1 테이블에서 조회

 

Outer Join  

특정 테이블 전체에 대한 집계를 하고자 할 때 사용

Outer Join 종류

- Left Outer Join : 테이블 순서 중 왼쪽에 위치한 테이블 전체

- Right Outer Join : 오른쪽에 위치한 테이블 전체

- Full Outer Join : 왼쪽 오른쪽 전체 ( Oracle DB에서만 사용 가능 )

 

Outer Join 개념

- Outer Join은 표준방식으로만 사용해야하며 , 일반 조건은 무조건 ON() 내부에 작성해야함

  ( Where 절 사용하면 Outer 전체 개념이 적용 안됨)

- Inner Join을 만족해야함 ( Inner Join 개념을 그대로 적용 )

- Left or Right 기준으로 같으면 같은 조건대로 조회 , 다르면 Null 조회

-- [문제]
/*
상품분류 전체에 대한 상품의 종류가 몇개씩 있는지 집계하기
*/
SELECT lprod_gu , lprod_nm  , COUNT(prod_name)
FROM lprod 
		LEFT OUTER JOIN prod
		ON(lprod_gu = prod_lgu)
GROUP BY lprod_gu;

 

문제

-- [문제]
/*
2005년도 월별 판매 현황 검색하기
조회컬럼 : 판매월 , 월별 총판매수량 , 월별 총판매금액
*/
SELECT concat(SUBSTR(cart_no,5,2),'월') AS mon , 
			 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);

데이터의 값들이 4,5 월인 경우만 있으므로 4,5월에 관한 데이터만 나왔다.

이를 1월 부터 12월 까지의 값이 나올수 있도록 하는 실습을 하였다.

 

1) 테이블 생성해서 푸는 방법

-- 테이블 생성해서 푸는 방법
CREATE TABLE mon
( cart_month VARCHAR(10) NOT NULL);
INSERT INTO mon(cart_month) VALUES ('01');
INSERT INTO mon(cart_month) VALUES ('02');
INSERT INTO mon(cart_month) VALUES ('03');
INSERT INTO mon(cart_month) VALUES ('04');
INSERT INTO mon(cart_month) VALUES ('05');
INSERT INTO mon(cart_month) VALUES ('06');
INSERT INTO mon(cart_month) VALUES ('07');
INSERT INTO mon(cart_month) VALUES ('08');
INSERT INTO mon(cart_month) VALUES ('09');
INSERT INTO mon(cart_month) VALUES ('10');
INSERT INTO mon(cart_month) VALUES ('11');
INSERT INTO mon(cart_month) VALUES ('12');

SELECT A.cart_month, nvl(B.total_qty,0) , nvl(B.total,0)
FROM mon A
		LEFT OUTER JOIN (SELECT SUBSTR(cart_no,5,2) AS mon , 
			 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.mon = A.cart_month);

 

mon이라는 테이블에 cart_month라는 컬럼으로 1월부터 12월까지의 값을 Insert 한 뒤

위에서 풀었던 코드를 from 절에 서브쿼리를 통해 mon 테이블과 left outer join을 하였다.

이때 관계조건식은 mon의 cart_month와 앞에서 구한 substr(cart_no,5,2)가 같다고 두었다.

 

 

2) UNION 사용하는 방법

SELECT months.month , SUM(cart_qty) AS total_qty ,
			 SUM(prod_sale*cart_qty) AS total 
FROM (SELECT '01' AS month
		UNION SELECT '02'
		UNION SELECT '03'
		UNION SELECT '04'
		UNION SELECT '05'
		UNION SELECT '06'
		UNION SELECT '07'
		UNION SELECT '08'
		UNION SELECT '09'
		UNION SELECT '10'
		UNION SELECT '11'
		UNION SELECT '12'
		)AS months
LEFT JOIN cart 
ON(SUBSTR(cart_no,5,2) = months.month)		
LEFT JOIN prod
ON(prod_id = cart_prod)
GROUP BY months.month;

 

union을 사용하여 컬럼명이 month이고 그 안에 데이터가 들어있는 가상테이블 months를 만들어 적용하는 방법이다.

 

 

문제

-- [문제]
/*
2005년도에 대한 전체 거래처별 총 매출금액 조회하기
조회컬럼 : 거래처코드 , 거래처명 , 총매출금액
정렬 : 총매출금액 내림차순
*/

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

 

 Union  

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

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

문제

-- [문제]
/*
회원전체에 대한 총구매금액을 조회하기
조회컬럼 : 회원아이디 , 회원이름 , 총구매금액
마지막 행에는 총구매금액의 총합을 표시
*/
SELECT '총구매금액', mem_id , mem_name , SUM(prod_sale*cart_qty) AS total
FROM member
	LEFT OUTER JOIN cart
	ON(cart_member = mem_id)
	LEFT OUTER JOIN prod
	ON(cart_prod = prod_id)
GROUP BY mem_id
UNION
SELECT '총구매금액의 총합','--' , '--' ,SUM(prod_sale*cart_qty)
FROM member
	LEFT OUTER JOIN cart
	ON(cart_member = mem_id)
	LEFT OUTER JOIN prod
	ON(cart_prod = prod_id);

-- 위 결과에서 총구매금액 순으로 내림차순해주세요
-- 단, 총 구매금액의 총합은 마지막행에
SELECT A.mem_id , A.mem_name , A.total
FROM (SELECT mem_id , mem_name , SUM(nvl(prod_sale*cart_qty,0)) AS total
FROM member
	LEFT OUTER JOIN cart
	ON(cart_member = mem_id)
	LEFT OUTER JOIN prod
	ON(cart_prod = prod_id)
GROUP BY mem_id
ORDER BY total DESC) A
UNION
SELECT '--' , '--' ,SUM(nvl(prod_sale*cart_qty,0)) AS total
FROM member
	LEFT OUTER JOIN cart
	ON(cart_member = mem_id)
	LEFT OUTER JOIN prod
	ON(cart_prod = prod_id) ;

구매금액 총합이 제일 큰값이라 첫번째행으로 표기되는데 이를 총구매금액 순으로 내림차순한 구문을 서브쿼리 형식으로 묶어(이때 order by) inline view로 사용하여 새로운 select 구문을 만든다.

이를 구매금액 총합과 Union 하면 총구매금액 기준으로 내림차순 정렬되고 총 구매금액의 총합은 제일 마지막 행에 표기되는 것을 알 수있다.

 

Union 하게되면 정렬은 제일 마지막에 정의해야한다.

컬럼명은 첫번째 Select 문의 컬럼이름을 따온다.

 Exists  

조회결과가 1건이라도 있으면 True , 0건이면 False
서브쿼리를 적용(다중컬럼의 다중행 모두 가능)

예시)

SELECT mem_id , mem_name
FROM member
WHERE EXISTS(SELECT * FROM cart WHERE cart_member = mem_id);

 

Not Exists 를 통해 조회 결과가 존재하지 않을때의 경우로 사용할 수 있다

-- [문제]
/*
구매 내역이 없는 회원만 조회
exists 사용
*/
SELECT mem_id,mem_name
FROM member
WHERE not EXISTS(SELECT * FROM cart WHERE cart_member = mem_id);

문제

-- [문제]
/*
2005년도 구매내역이 있는 회원에 대한 회원 아이디 , 회원이름 ,마일리지 조회
단, 구매내역이 있는 회원의 총구매금액이 3천만원 이상인 데이터에 대해서만 조회
*/
SELECT mem_id , mem_name , mem_mileage
FROM member 
WHERE mem_id =  (SELECT mem_id FROM cart , prod 
					WHERE cart_prod = prod_id AND cart_member = mem_id 
					AND left(cart_no,4) = '2005' AND cart_member = mem_id 
					GROUP BY mem_id HAVING SUM(prod_sale*cart_qty)>=30000000);
SELECT mem_id , mem_name , mem_mileage
FROM member 
WHERE EXISTS (SELECT SUM(cart_qty*prod_sale) 
					FROM cart, prod
					WHERE mem_id = cart_member AND prod_id = cart_prod AND left(cart_no,4) = '2005'
					HAVING SUM(cart_qty*prod_sale)>= 30000000);

 

위는 서브쿼리로 mem_id를 비교하는 것

아래는 exists로 조건을 만족하는 경우의 행만 출력되게 하는 코드이다.

 

 Update  

Update 테이블명 Set 컬럼명 = 값

Where 조건;

 

-- [문제]
/*
구매내역이 있는 회원의 마일리지값을 3으로 일괄 수정처리해주세요
*/

UPDATE member SET mem_mileage = 3
WHERE mem_id in (SELECT mem_id FROM member , cart WHERE mem_id = cart_member);

SELECT mem_id , mem_mileage
FROM member, cart 
WHERE mem_id = cart_member;

ROLLBACK;
SET autocommit = 0;

 

Update를 할때는 수정이 되었는지 확인할 수있는 조회문을 같이 써서 확인하는것이 좋다.

Heidi SQL의 경우에는 자동으로 Commit 이 되어 Rollback을 해도 수정 전으로 데이터값이 돌아가지 않는다.

그러나 SET autocommit = 0; 을 실행하여주면  Update후 Rollback시 다시 그 전 데이터 값으로 되돌아가는 것을 확인할 수 있다. (똑똑한 오씨가 알려줌 땡큐)

 

문제

-- [문제]
/*
2005년도 구매내역이 있는 회원에 대한 회원 아이디, 회원이름 , 마일리지 조회
단, 구매내역이 있는 회원의 총 구매금액이 3천만원 이상인 데이터에 대해서만 조회
위 문제 조건을 만족하는 회원에 대해서 마일리지 값을 1000점 부여하기
*/
UPDATE member SET mem_mileage = 1000
WHERE EXISTS (SELECT mem_id , mem_name , mem_mileage
					FROM member 
					WHERE EXISTS (SELECT SUM(cart_qty*prod_sale) 
					FROM cart, prod
					WHERE mem_id = cart_member AND prod_id = cart_prod AND left(cart_no,4) = '2005'
					HAVING SUM(cart_qty*prod_sale)>= 30000000));

 

나의 경우에는 exists 뒤의 조건문에 위에서 작성한 코드를 그대로 넣었기때문에 select 하는 컬럼명이 많지만 

이와 같은 경우에는 mem_id 하나를 쓰거나 전체 * 을 써도 똑같은 결과값이 나온다.