[Database][SQL] Join 조인 / Inline View 가상 테이블

⛄ JOIN

• 조인(join) 구문 작성하는 방법
• Inner Join 방식은 일반방식 or 표준방식 모두 표준처럼 사용됨
• Inner Join - 일반방식
  - 두 테이블 간에 PK = FK인 조건을 제시
  - from절 뒤에 테이블들을 ,로 구분하여 사용
  - where절에 관계 조건식(PK=FK)을 추가함
  - 관계조건식은 최소한 (사용된 테이블의 갯수 -1) 만큼 제시되어야 함

select 테이블1.컬럼1...테이블n.컬럼1
 From 테이블1, 테이블2 ... 테이블n
 Where 관계조건(PK = FK)
   And 일반조건

SELECT mem_id, mem_name, cart_prod, cart_qty
FROM member, cart
-- 관계조건식 추가(PK = FK)
WHERE mem_id = cart_member
	-- 일반조건 추가
	AND mem_add1 LIKE '서울%';

• Inner Join - 표준방식
  - 일반 조건을 where절에 작성하는 것도 가능하다.

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 ...정렬이 있는 경우

SELECT mem_id, mem_name, cart_prod, cart_qty
FROM member INNER JOIN cart
		on (mem_id = cart_member
       	 		-- 일반조건 추가
			AND mem_add1 LIKE '서울%')
	    INNER join prod
		on (cart_prod = prod_id);

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

SELECT mem_id, cart_member
FROM member, cart;

 

JOIN 문제

📝문제 1 

• 조회컬럼 : 회원아이디, 회원이름, 주문번호, 주문수량, 상품명 조회
• 조건
  1. 주문내역이 있는 회원이어야 하고 회원 거주지가 서울에 거주하는 회원이어야 한다.
  2. 상품명에 컴퓨터라는 단어가 포함되어있어야 한다.
  3. 일반방식과 표준방식으로 작성하기
  4. 주문수량을 기준으로 내림차순

✏️일반방식 

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;

 

✏️표준방식 

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;

조회되는 데이터 없음

---

📝문제 2 

• 조회 컬럼 : 상품분류코드, 상품분류명, 분류별 상품의 수
• 조건
  1. 상품 분류별 상품의 수를 조회
  2. 분류별 상품의 수 내림차순

✏️일반방식 

SELECT lprod_gu, lprod_nm, COUNT(prod_lgu) AS cnt
FROM lprod, prod
WHERE lprod_gu = prod_lgu
GROUP BY lprod_gu
ORDER BY cnt DESC;

 

✏️표준방식 

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

---

📝문제 3

• 조회 컬럼 : 회원아이디, 회원이름, 주문 수량, 상품명
• 조건
  1. 구매상품의 거래처 주소가 서울, 대전, 광주
  2. 상품 분류명에 전자가 포함
  3. 주문수량이 5이상
  4. 아이디 오름차순, 주문수량 내림차순

✏️일반방식 

SELECT mem_id, mem_name, cart_qty, prod_name
FROM member, cart, prod, lprod, buyer
WHERE mem_id = cart_member
	AND cart_prod = prod_id
	AND prod_lgu  = lprod_gu
	AND Prod_buyer = buyer_id
	AND substring(buyer_add1,1,2) IN ('서울', '대전','광주')
	AND lprod_nm LIKE '%전자%'
	AND cart_qty >= 5
ORDER BY mem_id, cart_qty DESC;

 

✏️표준방식 

SELECT mem_id, mem_name, cart_qty, prod_name
FROM member
		INNER JOIN cart
 			ON(mem_id = cart_member AND cart_qty >= 5)
 		INNER JOIN prod
 			ON(cart_prod = prod_id)
		INNER JOIN lprod
			ON(prod_lgu  = lprod_gu AND lprod_nm LIKE '%전자%')
		INNER JOIN buyer
		 	ON(Prod_buyer = buyer_id AND substring(buyer_add1,1,2) IN ('서울', '대전','광주'))
ORDER BY mem_id, cart_qty DESC;

조회되는 데이터 없음

---

📝문제 4

• 주문내역이 있는 회원별로 지금까지 총 지출한 총액 조회
• 조회컬럼 : 회원아이디, 회원이름, 총지출액

✏️일반방식 

SELECT mem_id, mem_name, SUM(prod_sale*cart_qty) AS 'total'
FROM member, cart, prod
WHERE mem_id = cart_member
	AND cart_prod = prod_id
GROUP BY cart_member
ORDER BY total desc;

✏️표준방식 

SELECT mem_id, mem_name, SUM(prod_sale*cart_qty) AS 'total'
FROM member 
	inner join cart
		ON mem_id = cart_member
	inner join prod
		ON cart_prod = prod_id
GROUP BY cart_member
ORDER BY total desc;

---

⛄ Inline View

Inline View 문제

✏️문제 1

• JOIN 문제 4번에 이어서 지출이 가장 큰 값과, 가장 작은 값을 조회하기
• 조회 컬럼 : 최댓값, 최솟값

SELECT MAX(A.total) AS total_max
			, MIN(A.total) AS total_min
FROM (
    SELECT SUM(prod_sale * cart_qty) AS total
    FROM member
        INNER JOIN cart 
		  ON mem_id = cart_member
        INNER JOIN prod 
		  ON cart_prod = prod_id
    GROUP BY cart_member) A;

---

✏️문제 2 

- Inline View 1번 문제의 결과에서 최대값을 가지는 회원아이디와 이름 조회

SELECT AA.mem_id, AA.mem_name, AA.total
FROM(
	SELECT mem_id, mem_name, SUM(prod_sale * cart_qty) AS total
    			FROM member, cart, prod
				WHERE mem_id = cart_member
					AND cart_prod = prod_id
			GROUP BY cart_member) AA,
	(
	SELECT MAX(A.total) AS total_max, MIN(A.total) AS total_min
		FROM (SELECT mem_id, mem_name, SUM(prod_sale * cart_qty) AS total
    			FROM member, cart, prod
				WHERE mem_id = cart_member
					AND cart_prod = prod_id
				GROUP BY cart_member) A) BB
WHERE AA.total = BB.total_max;