(MariaDB) SQL - 조인(Join)/Inline View

 

조인 (Join) 구문 작성하는 방법

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

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

 

크로스 조인(CROSS JOIN)

 

두 테이블 간의 관계 조건 없이 사용한 경우

행의 개수 = 테이블행 전체개수 * 테이블행 전체개수

따라서 사용을 지양할 것

SELECT mem_id , cart_member
FROM member, cart;

 

이너 조인(INNER JOIN)

 

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

두 테이블간에 PK = FK인 조건을 제시

INNER JOIN
일반 방식	표준 방식
From 절 뒤에 테이블들을 콤마(,) 로 구분하여 사용 Where절에 관계조건식(PK=FK)을 추가함 관계 조건식은 최소한(사용한 테이블의 개수 -1 ) 만큼 제시되어야함 < 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 --------------정렬이 있는 경우

 

< 일반 방식 예시 >

-- 일반 방식 (우리나라에서 주로 사용)
SELECT mem_id , mem_name , cart_prod , cart_qty , prod_name
FROM member, cart, prod
-- 관계조건식 추가
-- WHERE cart_member = mem_id;
WHERE mem_id = cart_member
AND cart_prod = prod_id
-- 일반조건 추가
AND mem_add1 IN '%서울%';

 

< 표준 방식 예시 >

-- (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 IN '%서울%')
		INNER JOIN prod
				ON(cart_prod = prod_id);
-- 일반 조건을 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 IN '%서울%';

 

[ 문제 ] - INNER JOIN 일반방식과 표준방식 사용하기

-- [문제]
/*
조회 컬럼 : 회원아이디 , 회원 이름 , 주문번호 , 주문수량 , 상품명 조회
조건 : 주문내역이 있는 회원이어야함
		회원거주지가 서울에 거주하는 회원이어야함
		상품명에 컴퓨터라는 단어가 포함되어있어야함
정렬 : 주문수량을 기준으로 내림차순
*/
-- 일반방식
SELECT mem_id , mem_name , cart_no , cart_qty , prod_name
FROM member , cart , prod
WHERE cart_member = mem_id
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(cart_member = mem_id)
		INNER JOIN prod
			ON(cart_prod = prod_id)
WHERE mem_add1 LIKE '%서울%'
AND prod_name LIKE '%컴퓨터%'
ORDER BY cart_qty DESC;

 

[ 문제 ] - GROUP BY 와 INNER JOIN 사용하기

-- [문제]
/*
상품 분류별 상품의 개수 조회
조회 컬럼 : 상품분류코드 , 상품분류명 , 분류별 상품의 개수  
정렬 : 분류별개수 기준으로 내림차순
*/
-- 일반 방식
SELECT lprod_gu , lprod_nm , COUNT(prod_id) AS cnt
FROM lprod , prod
WHERE lprod_gu = prod_lgu
GROUP BY lprod_id
ORDER BY cnt DESC;
-- 표준 방식
SELECT lprod_gu , lprod_nm , COUNT(prod_id) AS cnt
FROM lprod
		INNER JOIN prod
			ON(lprod_gu = prod_lgu)
GROUP BY lprod_id
ORDER BY cnt DESC;

 

[ 문제 ] - 여러가지의 일반조건을 위한 관계 조건 사용하기

-- [문제]
/*
회원아이디 , 회원이름 , 주문수량 , 상품명 조회
단, 구매상품의 거래처 주소가 서울,대전,광주인 경우
상품분류명에  '전자'가 포함된 경우
주문수량이 5 이상인경우
정렬은 아이디 오름차순 , 주문수량 내림차순
*/
-- 일반방식
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 lprod_gu = prod_lgu
AND lprod_nm LIKE '%전자%'
AND left(buyer_add1,2) in ('서울','대전','광주')
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 buyer 
			ON(prod_buyer = buyer_id AND left(buyer_add1,2) in ('서울','대전','광주'))
		INNER JOIN lprod
			ON(lprod_gu = prod_lgu AND lprod_nm LIKE '%전자%' )
ORDER BY mem_id , cart_qty DESC;

 

[ 문제 ] - JOIN 과 집계함수 사용하기

-- [문제]
/*
주문내역이 있는 회원별로 지금까지 총 지출한 총액 조회하기
조회컬럼 : 회원아이디, 회원이름 , 총지출액
정렬 : 총지출액 기준 내림차순
*/
-- 일반 방식
SELECT mem_id , mem_name , SUM(prod_sale*cart_qty) AS total
FROM member , cart, prod
WHERE mem_id = cart_member
AND prod_id = cart_prod
GROUP BY mem_id
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(prod_id = cart_prod)
GROUP BY mem_id
ORDER BY total DESC;

 

[ 문제 ] - From절에 서브쿼리 넣어 사용하기

-- [문제]
/*
위 결과에서 가장 지출이 큰 값과 가장 작은 값을 조회하세요
조회값 : 최대값 , 최소값
*/
SELECT MAX(total) AS total_max , MIN(total) AS total_min
FROM (SELECT SUM(prod_sale*cart_qty) AS total
		FROM member ,cart, prod 
		WHERE mem_id = cart_member
		AND prod_id = cart_prod
		GROUP BY mem_id) A;

 

▶

가상테이블 개념이 적용됨

컬럼명은 A. total 또는 total의 형식으로 사용이 가능함

Inline View

 

- From 절 뒤에 테이블 대신에 Sub Query를 사용함
- Sub Query의 Select 한 행렬 결과를 메모리에 적재시켜서 사용
- 행렬의 테이터를 테이블과 동일한 개념으로 사용하기 때문에 가상테이블이라고 칭함
- 가상테이블은 별칭을 이용해서 테이블 이름 처럼 사용함( AS  없이 대문자 주로 사용)

 

[ 문제 ] -  Inline View

-- [문제]
/*
위의 결과에서 최대값을 가지는 회원아이디와 이름을 조회해주세요
*/
SELECT mem_id , mem_name , B.total_max
FROM (SELECT MAX(A.total) AS total_max , MIN(total) AS total_min
		FROM (SELECT SUM(prod_sale*cart_qty) AS total
		FROM member ,cart, prod 
		WHERE mem_id = cart_member
		AND prod_id = cart_prod
		GROUP BY mem_id)A )B ,
		(SELECT mem_id , mem_name , SUM(prod_sale*cart_qty) AS total
		FROM member , cart, prod
		WHERE mem_id = cart_member
		AND prod_id = cart_prod
		GROUP BY mem_id
		ORDER BY total DESC) C
WHERE C.total = B.total_max;

 

▶ From 절에 위에서 구한 총지출액이 있는 구문과 총지출액의 최대값이 있는 구문을 서브쿼리로 넣어 사용한다.

이때 주의할 점은 서브쿼리에서 select한 값들만  메인쿼리에서 사용할 수 있다는 것이다. 

 

[ 문제 만들기 ]

이제까지 배운 내용을 토대로 문제를 만들어보았다.

-- [문제 만들기]
-- 매입 단가가 10만원 이상인 한 제품을 5개 이상 구매한 회원들 중 
-- 총 구매 갯수가 30개 이상인 사람들 중
-- 가장 최근에 주문한 회원을 구하시오.
-- 조회컬럼은 회원아이디, 회원이름, 총 구매갯수, 주문번호(주문일자는 주문번호로 알수있음)
SELECT mem_id, mem_name, sum(cart_qty) AS sum_qty, cart_no
FROM member, prod, cart , buyprod
WHERE cart_prod = prod_id
  AND cart_member = mem_id
  AND prod_id = buy_prod
  AND buy_cost >= 100000
  AND cart_qty >=3
GROUP BY mem_id
HAVING sum(cart_qty) >= 30
order by cart_no DESC
LIMIT 1;

 

 

[ 멋진분들의 문제 ]

문제 만들기를 한 팀 중 아주 어렵고 복잡한 문제를 만드신 분들이 계셔서 풀어봤다.

정말 정말 어려웠다 문제 만들기의 귀재이신듯

나는 Inline View를 사용하지 않고 where절에 서브쿼리 형식으로만 사용해서 풀었는데 

Inline View를 사용하면 조금 더 깔끔할 것 같다.

-- 가장 최근에 입고(buy_date)된 상품의 상품명(prod_name)과 거래처명(buyer_name), 
-- 그 상품을 가장 많이 구매한 회원의 아이디, 이름, 마일리지, 해당 상품 구매 수량, 회원등급
-- 마일리지 1000-2000 브론즈, 2000-3000 실버, 3000-4000 골드, 4000-5000플래티넘, 5000이상 다이아

SELECT  prod_id ,buyer_name ,mem_id , mem_name , mem_mileage , cart_qty ,
		case 
		when mem_mileage BETWEEN 1000 AND 2000 then '브론즈'
		when mem_mileage BETWEEN 2000 AND 3000 then '실버'
		when mem_mileage BETWEEN 3000 AND 4000 then '골드'
		when mem_mileage BETWEEN 4000 AND 5000 then '플래티넘'
		ELSE '다이아' 
		END AS '회원등급'
FROM member , cart , prod , buyer
WHERE prod_id = cart_prod
AND mem_id = cart_member
AND prod_buyer = buyer_id
AND prod_id IN (SELECT prod_id FROM buyprod , prod 
					WHERE prod_id = buy_prod
					AND buy_date = (SELECT MAX(buy_date)
					FROM buyprod))
AND cart_qty in (SELECT  MAX(cart_qty)
				FROM cart , prod
				WHERE cart_prod = prod_id
				and prod_id IN (SELECT prod_id FROM buyprod , prod 
					WHERE prod_id = buy_prod
					AND buy_date = (SELECT MAX(buy_date)
					FROM buyprod))
GROUP BY prod_id);

 

문제 만드신 분들의 출처 ❤️

https://mzero.tistory.com/

https://codingwithyou.tistory.com/