[Database] HeidiSQL 조인 [크로스 조인(Cross Join), 이너조인(Inner Join), 아우터 조인(Outer Join), 셀프 조인(Self Join)], union, exists, update

728x90

프로그래밍 세계를 탐구합시다.

<조인(join) 구분 작성하는 방법>

- Inner Join 방식은 : 일반방식 or 표준방식 모두 표준처럼 사용됨

(Inner Join - 일반방식)
    Select 테이블1.컬럼1...테이블n.컬럼1
    From 테이블1, 테이블2 .... 테이블 n
    Where 관계조건(PK = FK)
      And 일반조건

(Inner Join - 표준방식)
    Select 테이블1.컬럼1...테이블n.컬럼1
    From 테이블1 Inner Join 테이블2
     On(관계조건(PK = FK)
         And 일반조건)
       Inner Join 테이블 n
        On(관계조건(PK = FK)
     And 일반조건)

Group By - 그룹이 있는 경우
Having - 그룹 조건이 있는 경우
Order By - 정렬이 있는 경우

1. Join의 종류

크로스 조인(Cross Join), 이너조인(Inner Join), 아우터 조인(Outer Join)

[크로스 조인 (Cross Join)]

-- :아래와 같이 두 테이블간의 관계 조건 없이 사용한 경우
-- : 행의 갯수 = 테이블행 전체갯수 * 테이블행 전체갯수

[ 활용 예시 ]

SELECT mem_id, cart_member
FROM member, cart, prod;

[ 결과 ]

위 결과는 아래 계산과 같습니다.

member와 cart의 수를 모두 계산한 결과 28과 135를 곱한 값 279,720입니다.

-- 28
SELECT COUNT(*) FROM member;
-- 135
SELECT COUNT(*) FROM cart;

SELECT 28 * 135;

[ 이너 조인 (Inner Join) ]

-- : 두 테이블간에 PK = FK 인 조건을 제시
-- (일반방식)
-- : From절 뒤에 테이블들을 콤마(,)로 구분하여 사용
-- : Where절에 관계조건식(PK = FK)을 추가함
-- : 관계조건식은 최소한 (사용된 테이블의 갯수 - 1)만큼 제시되어야 함

[ 활용 예시 (일반 방식) ]

-- (일반방식)
-- : From절 뒤에 테이블들을 콤마(,)로 구분하여 사용
-- : Where절에 관계조건식(PK = FK)을 추가함
-- : 관계조건식은 최소한 (사용된 테이블의 갯수 - 1)만큼 제시되어야 함
SELECT mem_id, mem_name, cart_prod, cart_qty, prod_name
FROM member, cart, prod
-- 관계조건 추가(PK = FK)
WHERE mem_id = cart_member 
  AND cart_prod = prod_id
  -- 일반조건 추가
  AND mem_add1 LIKE '서울%';

[ 활용 예시 (Ansi 표준방식) ]

-- (inner join Ansi 표준방식)
SELECT mem_id, mem_name, cart_prod, cart_qty, prod_name
FROM member 
			INNER JOIN cart
			 ON(mem_id = cart_member
			  -- 일반조건 추가
			  AND mem_add1 LIKE '서울%')
			Inner JOIN prod
			 ON(cart_prod = prod_id);

[ 활용 예시 (Ansi 표준방식) where 절 사용 ]

-- 일반조건을 where 절에 추가
SELECT mem_id, mem_name, cart_prod, cart_qty, prod_name
FROM member 
			INNER JOIN cart
			 ON(mem_id = cart_member)
			Inner JOIN prod
			 ON(cart_prod = prod_id)
-- 일반조건 추가
where mem_add1 LIKE '서울%';

[ 결과 ]

[ 조건 ]

-- [문제]
-- 주문 내역이 있는 상품에 대한 정보를 조회하려고 합니다.
-- 조회컬럼 : 상품코드, 상품명, 상품분류명, 거래처명
-- 단, 주문수량이 5개 이상 주문한 경우
--     주문 회원의 지역이 서울, 대전, 광주인 경우에 대해서

[ 작성 코드 1 - select 활용]

SELECT Prod_id, Prod_name, 
(select Lprod_nm FROM lprod WHERE lprod_gu=prod_lgu) AS lprod_nm, 
(select Buyer_name FROM buyer WHERE Buyer_id=prod_buyer) AS buyer_name
FROM prod
WHERE 
prod_id IN (SELECT cart_prod FROM cart WHERE cart_qty >= 5
				AND cart_member IN 
				(SELECT mem_id FROM member WHERE SUBSTRING(mem_add1, 1, 2) IN ('서울', '대전', '광주')));

[ 작성 코드 2 - join 활용]

[ 결과 ]

[ 조건 ]

-- [문제]
-- 조회컬럼 : 회원아이디, 회원이름, 주문번호, 주문수량, 상품명 조회
-- 조건 : 주문내역이 있는 회원이어야 함
--      : 주문한 회원 거주지가 서울에 거주하는 회원이어야 함
--      : 상품명에 컴퓨터라는 단어가 포함되어 있어야 함
-- 정렬 : 주문수량을 기준으로 내림차순

[ 작성 코드 1 - inner join 일반 방식 ]

-- 일반방식
SELECT Mem_id, Mem_name, Cart_no, Cart_qty, Prod_name
FROM member, cart, prod
-- 관계조건
WHERE mem_id = cart_member
  AND cart_prod = prod_id
-- 일반조건
  AND mem_add1 LIKE '%서울%'
  AND prod_name LIKE '%컴퓨터%'
ORDER BY Cart_qty desc;

[ 작성 코드 2 - inner join Ansi 표준 방식 ]

-- Ansi 표준 방식
SELECT Mem_id, Mem_name, Cart_no, Cart_qty, Prod_name
FROM member
		INNER JOIN cart
		ON (mem_id = cart_member
			AND mem_add1 LIKE '%서울%')
		INNER JOIN prod
		ON (cart_prod = prod_id
			AND prod_name LIKE '%컴퓨터%')
ORDER BY Cart_qty desc;

[ 작성 코드 3 - inner join Ansi 표준 방식 (where절 사용)]

-- Ansi 표준 방식
SELECT Mem_id, Mem_name, Cart_no, Cart_qty, Prod_name
-- 관계조건
FROM member
		INNER JOIN cart
		ON (mem_id = cart_member)
		INNER JOIN prod
		ON (cart_prod = prod_id)
-- 일반조건
WHERE mem_add1 LIKE '%서울%'
  AND prod_name LIKE '%컴퓨터%'
ORDER BY Cart_qty desc;

[ 결과 - 결과 없음 ]

[ 조건 ]

-- [문제]
-- 상품분류별 상품의 갯수를 조회하려고 합니다.
-- 조회컬럼 : 상품분류코드, 상품분류명, 분류별 상품의 갯수
-- 정렬 : 분류별갯수 내림차순

[ 작성 코드 1 - inner join 일반 방식 ]

-- 일반방식
SELECT lprod_gu, lprod_nm, COUNT(prod_id) AS prod_cnt
FROM lprod, prod
WHERE lprod_gu = prod_lgu
GROUP BY lprod_gu, lprod_nm 
ORDER BY prod_cnt;

[ 작성 코드 2 - inner join Ansi 표준 방식 ]

-- ansi 표준 방식
SELECT lprod_gu, lprod_nm, COUNT(prod_id) AS prod_cnt
FROM lprod
	  INNER JOIN prod
	    ON (lprod_gu = prod_lgu)
GROUP BY lprod_gu, lprod_nm 
ORDER BY prod_cnt;

[ 결과 ]

[ 조건 ]

- [문제]
/*
 - 회원아이디, 회원이름, 주문수량, 상품명 조회
 - 단, 구매상품의 거래처 주소가 서울, 대전, 광주인 경우
 -    상품분류명에 '전자'가 포함된 경우
 -    주문수량이 5이상인 경우
 - 정렬은 아이디 오름차순, 주문수량 내림차순
*/

[ 작성 코드 1 - inner join 일반 방식 ]

-- 일반방식
SELECT Mem_id, Mem_name, Cart_qty, Prod_name
FROM member, cart, prod, buyer, lprod
WHERE mem_id = cart_member
  AND cart_prod = prod_id
  AND prod_buyer = buyer_id
  AND prod_lgu = lprod_gu
  AND SUBSTRING(Buyer_add1, 1, 2) IN ('서울', '대전', '광주')
  AND Lprod_nm LIKE '%전자%'
  AND Cart_qty >= 5
  ORDER BY mem_id ASC, Cart_qty desc;

[ 작성 코드 2 - inner join Ansi 표준 방식 ]

-- Ansi 표준 방식
SELECT Mem_id, Mem_name, Cart_qty, Prod_name
FROM member
		inner join cart
		ON (mem_id = cart_member)
		inner join prod
		ON (cart_prod = prod_id
		    AND Cart_qty >= 5)
		inner join buyer
		ON (prod_buyer = buyer_id
		    AND SUBSTRING(Buyer_add1, 1, 2) IN ('서울', '대전', '광주'))
		inner join lprod
		ON (prod_lgu = lprod_gu
		    AND Lprod_nm LIKE '%전자%')
  ORDER BY mem_id ASC, Cart_qty desc;

[ 결과 ] - 출력 결과 없음

[ 조건 ]

-- [문제]
-- 주문내역이 있는 회원별로 지금까지 총 지출한 총액 조회하기
-- 조회컬럼 : 회원아이디, 회원이름, 총지출액

[ 작성 코드 1 - inner join 일반 방식 ]

-- 일반방식
SELECT Mem_id, Mem_name, SUM(cart_qty * prod_sale) AS total
FROM member, cart, prod
WHERE mem_id = cart_member
  AND cart_prod = prod_id
GROUP BY mem_id, mem_name
ORDER BY total desc;

[ 작성 코드 2 - inner join Ansi 표준 방식 ]

-- 표준방식
SELECT Mem_id, Mem_name, SUM(cart_qty * prod_sale) AS total
FROM member
		INNER join cart
		ON(mem_id = cart_member)
		INNER join prod
		ON(cart_prod = prod_id)
GROUP BY mem_id, mem_name
ORDER BY total desc;

[ 결과 ]

[ 조건 ]

-- 위 문제에 이어서
-- 위 결과에서 가장 지출이 큰 값과, 가장 작은 값을 조회해 주세요
-- 조회 값 : 최대값, 최소값

[ from 절 안에 서브쿼리 ]

가상테이블

-- 가상테이블 개념이 적용됨(Inline view)
--  : From절 뒤에 테이블 대신에 Sub Query를 사용함
--  : Sub Query의 Select한 행렬 결과를 메로리에 적재시켜서 사용
--  : 행렬의 데이터를 테이블과 동일한 개념으로 사용하기 때문에
--    가상테이블이라고 칭합니다.
--  : 가상테이블은 별칭을 이용해서 테이블 이름처럼 사용합니다.

[ 조건 ]

-- 위 문제에 이어서
-- 위 결과에서 가장 지출이 큰 값과, 가장 작은 값을 조회해 주세요
-- 조회 값 : 최대값, 최소값

[ 작성 코드 ]

SELECT MAX(a.total), MIN(total)
from(SELECT Mem_id, Mem_name, SUM(cart_qty * prod_sale) AS total
FROM member, cart, prod
  WHERE mem_id = cart_member
  AND cart_prod = prod_id
GROUP BY mem_id, mem_name
ORDER BY total DESC) A;

[ 결과 ]

[ 조건 ]

-- [문제]
/*
 - 회원아이디 b001 회원의 마일리지값보다 큰 회원정보 조회하기
 - 조회컬럼 : 회원아이디, 이름
 - 2가지 이상의 방법 적용
*/

[ 작성 코드 1 ]

SELECT Mem_id, Mem_name
FROM member
WHERE Mem_mileage > (SELECT mem_mileage 
                     FROM member 
							WHERE mem_id='b001');

[ 작성 코드 2 ]

SELECT Mem_id, Mem_name
FROM member,
   (SELECT mem_mileage 
                     FROM member 
							WHERE mem_id='b001') mem
WHERE member.mem_mileage > mem.mem_mileage;

[ 작성 코드 3 - self join ]

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

[ 결과 ]

[ 조건 ]

-- [문제]
-- 회원별 구매금액의 총액을 조회하려고 합니다.
-- 2005년 5월에 구매한 내역입니다.
-- 조회컬럼 : 회원이름, 구매금액총액

[ 작성 코드 1 ]

-- (일반방식)
SELECT mem_name,
		sum(cart_qty * prod_sale) AS total
FROM member, cart, prod
 WHERE mem_id = cart_member
  AND cart_prod = prod_id
  AND cart_no LIKE '200505%'
GROUP BY mem_id, mem_name
ORDER BY total DESC;

[ 작성 코드 2 - Ansi 표준 방식 ]

-- (표준방식)
SELECT mem_name,
		sum(cart_qty * prod_sale) AS total
FROM member
	inner join cart
	ON (mem_id = cart_member
	   AND cart_no LIKE '200505%')
	inner join prod
	ON (cart_prod = prod_id)
 WHERE mem_id = cart_member
GROUP BY mem_id, mem_name
ORDER BY total DESC;

[ 결과 ]

[Outer Join]

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

- Outer Join 종류
   : Left Outer Join : 테이블 순서 중 왼쪽에 위치한 테이블 전체
   : Right Outer Join : 오른쪽에 위치한 테이블 전체
   : Full Outer Join : 왼쪽 오른쪽 전체

: Oracle DB에서만 사용 가능

- Outer Join 개념
   : Outer Join은 표준방식으로만 사용해야 하며,
   : 일반 조건은 무조건 On() 내부에 작성해야 한다.
   : Inner Join을 만족해야 함(Inner Join 개념을 그대로 적용)
   : Left or Right 기준으로 같으면 같은 조건대로 조회, 다르면 Null 조회

Left outer join 예제

[ 조건 ]

-- [문제]
-- 회원별 구매금액의 총액을 조회하려고 합니다.
-- 2005년 5월에 구매한 내역입니다.
-- 조회컬럼 : 회원이름, 구매금액총액
-- 회원 전체에 대해서 위 조건으로 조회해주세요
-- 총 회원은 28명 입니다.
-- null 값은 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 cart_no LIKE '200505%')
	left outer join prod
	ON (cart_prod = prod_id)
GROUP BY mem_id, mem_name
ORDER BY total DESC;

[ 결과 ]

[ 조건 ]

-- [문제]
-- 상품분류 전체에 대한 상품 종류의 갯수 집계하기

[ 작성 코드 ]

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

[ 결과 ]

[ 조건 ]

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

[ 작성 코드 ]

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

[ 결과 ]

[ 조건 ]

-- [문제]
-- 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문의 컬럼이름을 따름

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

[ 활용 예시 ]

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

[ 조건 ]

-- [문제]
-- 회원전체에 대한 총구매금액을 조회하기
-- 조회컬럼 : 회원아이디, 회원이름, 총구매금액
-- 마지막 행에는 총구매금액의 총합을 표시하고 싶어요

[ 작성 코드 ]

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 '', '';

[ 결과 ]

-- exists()

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

[ 조건 ]

-- [문제]
-- 구매(주문) 내역이 없는 회원만 조회해주세요.
-- exists 사용

[ 작성 코드 1 ]

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

[ 결과 ]

[ 작성 코드 2 ]

-- [문제]
-- 구매(주문) 내역이 없는 회원만 조회해주세요.
-- exists 사용
SELECT Mem_id, mem_name
FROM member
WHERE not exists(SELECT *
					from cart
					where mem_id=cart_member);

[ 결과 ]

[ 조건 ]

-- [ 문제 ]
-- 2005년도 구매내역이 있는 회원에 대한
-- 회원아이디, 회원이름, 마일리지 조회
-- 단, 구매내역이 있는 회원의 총구매금액이
--     3천만원 이상인 데이터에 대해서만 조회

[ 작성 코드 ]

SELECT mem_id, mem_name, mem_mileage
FROM member
WHERE EXISTS(
		SELECT SUM(cart_qty * prod_sale)
				FROM cart, prod
				WHERE cart_member = mem_id
				AND cart_prod = prod_id
				AND cart_no LIKE '2005%'
				HAVING SUM(cart_qty * prod_sale) >= 30000000
				);

[ 결과 ]

[ Update ]

-- 수정 및 삭제 시
-- 기준 컬럼은 누구?
-- PK값을 기준으로 행을 조건처리 해야 합니다.

[ 조건 ]

-- 수정하기
-- 회원아이디 a001, b001인 회원의
-- 마일리지 값을 2로 수정해주세요
-- (검증용 select 문)

[ 작성 코드 ]

-- (검증용 select 문)
SELECT mem_id, mem_mileage
FROM member
WHERE mem_id IN ('a001', 'b001');

--수정하기
update member
	SET mem_mileage = 2
WHERE mem_id IN ('a001', 'b001');

[ 결과 ]

[ 조건 ]

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

[ 작성 코드 ]

-- (검증용 select 문)
SELECT mem_id, mem_mileage
FROM member
WHERE EXISTS
(SELECT cart_member FROM cart WHERE cart_member = mem_id);

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

[ 결과 ]

[ 조건 ]

-- [문제]
-- 상품분류 전체에 대한 상품이름과 구매수량의 총합을 조회
-- 단, 구매년도가 2005년도
--     prod, cart 테이블만 사용
--     조회컬럼 : 전체상품분류코드, 상품명, 구매수량의 총합

[ 작성 코드 ]

SELECT LEFT(prod_id, 4) AS lgu,
		 prod_name,
		 SUM(cart_qty) AS total
FROM prod 
		LEFT OUTER JOIN cart
		ON(prod_id = cart_prod
			AND cart_no LIKE '2005%')
GROUP BY LEFT(prod_id, 4), prod_name;

[ 결과 ]

728x90

저작자표시 비영리 변경금지

'IT > Database' 카테고리의 다른 글

[Database] HeidiSQL 함수_뷰_프로시저 (3)	2023.11.29
[Database] HeidiSQL 그룹 함수 (집합 함수) (4)	2023.11.27
[Database] Heidi SQL 문자열함수, 대소문자 변환, 공백 제거, 특정 자릿수까지의 문자, 문자열 내 위치 값, 서브쿼리 (3)	2023.11.26
[Database] HeidiSQL 함수 In, Replace, Select, nvl, 날짜 함수 (2)	2023.11.25
[Database] Heidi SQL 문자열함수, Select, From, Where, 별칭 사용, 정렬, 연산자, 날짜 형식, 데이터 처리 순서 (2)	2023.11.24