[DB]데이터베이스 명령어5 (MariaDB) - 조인(Join), 가상테이블(inline view)

조인(JOIN)

 

 

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

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

1. Inner Join - 일반방식
    "우리나라 표준처럼 사용"

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

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

 

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

• 일반조건은 Where절에 따로 작성해도 실행 됨.

 

예시1

▶ 크로스 조인 : 아래 같이 두 테이블간의 관계 조건 없이 사용한 경우
                           행의 갯수 = 테이블행 전체갯수 * 테이블행 전체갯수
                           ※ 조심!! DB서버 다운될수 있다.

-- 3780개 ( JOIN된 행의 갯수 )
SELECT mem_id, cart_member
FROM member, cart, prod;

 

-- 28개( 1번 테이블행 전체갯수 )
SELECT COUNT(*) FROM member;
-- 135개( 2번 테이블행 전체갯수 )
SELECT COUNT(*) FROM cart;
-- 3780개( JOIN된 행의 갯수 )
SELECT 28*135;

 

예시2

▶ [Inner Join - 일반방식]  : 두 테이블간에 PK=FK인 관계 조건을 제시
                           

SELECT mem_id, mem_name, cart_prod, cart_qty
FROM member, cart
-- 관계조건 추가(PK=FK)
WHERE mem_id = cart_member;

 

  ※ 관계조건식은 최소한 (사용된 테이블의 갯수 -1) 만큼 제시!!

SELECT mem_id, mem_name, cart_prod, cart_qty, prod_name
FROM member, cart, prod
-- 관계조건 추가 (3-1 = 2)
WHERE mem_id = cart_member
      AND prod_id = cart_prod
-- 일반조건 추가
      AND mem_add1 LIKE '서울%';

 

 

예시3

▶ [Inner Join - 표준방식]  : 두 테이블간에 PK=FK인 관계 조건을 제시
                           

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(prod_id = cart_prod);

 

  ※ 일반조건 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(prod_id = cart_prod)
-- 일반조건 추가
WHERE mem_add1 LIKE '서울%';

 

 

---

 

문제1

주문내역이 있는 상품에 대한 정보를 조회하려 한다.
조회 컬럼 : 상품코드, 상품명, 상품분류명, 거래처명
단, 주문수량이 5개 이상 주문한 경우,

     주문 회원의 지역이 서울, 대전, 광주인 경우에 대해서만

 

<내 코드>

-- 44개
SELECT prod_id, prod_name,
               (SELECT lprod_nm FROM lprod WHERE prod_lgu = lprod_gu) AS lprod_nm,
               (SELECT buyer_name FROM buyer WHERE prod_buyer = buyer_id ) AS buyer_name
FROM prod
WHERE prod_id IN (SELECT cart_prod FROM cart where Cart_qty >= 5)
      AND prod_id IN (SELECT cart_prod FROM cart where cart_member 
                           IN (SELECT mem_id FROM member where substring(mem_add1, 1, 2) IN ('서울', '대전', '광주')));

 

<교수님 코드>

-- 43개
SELECT prod_id, prod_name,
               (SELECT lprod_nm FROM lprod WHERE prod_lgu = lprod_gu) AS lprod_nm,
               (SELECT buyer_name FROM buyer WHERE prod_buyer = buyer_id ) 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 ('서울', '대전', '광주')));

※ 두 개가 다른 값이 나온 이유 (정답은 43개가 맞다!)

1. 첫 번째 쿼리:

• WHERE 절에서 Cart_qty >= 5 를  만족하는 cart_prod 과
  cart_member IN ('서울', '대전', '광주') 조건을 만족하는 cart_prod를 대상으로 함.
• 두 개의 cart_prod 모두 해당하는 prod_id를 넘겨주게 됨(교집합)
  
• 확인용 코드 : SELECT * FROM cart WHERE cart_prod = 'P202000004';

• 여기서 나오면 안되는 1개가 나온 이유를 알 수 있다.
  WHERE 절에서 Cart_qty >= 5 를  만족하는 cart_prod 값으로 두번째 줄에 있는 'P202000004' 가 들어 갔고
  WHERE 절에서 cart_member IN ('서울', '대전', '광주') 조건을 만족하는 cart_prod 값으로 첫번째 줄에 있는 'P202000004' 가 들어 갔다.
• 이 값은 둘의 교집합을 만족하기 때문에 포함되어 출력되는데 실제로는 우리가 설계한
  주문수량 5개 이상이면서 주문 회원 지역이 서울, 대전, 광주인 값이 아닌 값이다.

 

2. 두 번째 쿼리:

• WHERE 절에서 두 개의 독립된 서브쿼리로 분리되어 있음.
• 첫 번째 서브쿼리는 Cart_qty >= 5를 만족하는 cart_prod를 선택하고,
• 두 번째 서브쿼리는 cart_member IN ('서울', '대전', '광주')  을 만족하는 cart_prod를 선택함.

 

문제2

조회 컬럼 : 회원아이디, 회원이름, 주문번호, 주문수량, 상품명 조회
조건 : 주문내역이 있는 회원이여야 함
주문한 회원 거주지가 서울
상품명에 컴퓨터라는 단어가 포함되어 있어야 함
정렬 : 주문수량을 기준으로 내림차순
일반방식과 표준방식 각각 수행

 

< 일반방식 >

SELECT mem_id, mem_name, cart_no, cart_qty, prod_name
FROM member, cart, prod
-- 관계조건
WHERE cart_prod = prod_id
      AND cart_member = mem_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
                AND mem_add1 LIKE '%서울%')
       INNER JOIN prod
          ON(cart_prod = prod_id
                AND prod_name LIKE '%컴퓨터%')
ORDER BY cart_qty DESC;

 

 

문제3

상품분류별 상품의 갯수를 조회하려 한다.
조회 컬럼 : 상품분류코드, 상품분류명, 분류별 제품의 갯수
정렬 : 분류별제품의 갯수를 기준으로 내림차순
일반방식과 표준방식 각각 수행

 

< 일반방식 >

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

 

< 표준방식 >

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

 

※ 여기서 order by에는 별칭 써도 된다!!(정렬은 항상 맨 마지막에 실행되므로)

※ 왜 그룹을 lprod_gu, lprod_nm 두개 쓰는지 : 컬럼 조회시 개수 맞추려고 (여기서 오류가 나는 경우가 많음)

 

문제4

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

 

< 일반방식 >

SELECT mem_id, mem_name, cart_qty, prod_name
FROM member, cart, prod, buyer, lprod
-- 관계 조건(4개)
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;

 

< 표준방식 >

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 substring(buyer_add1, 1, 2) IN ('서울', '대전', '광주'))
            INNER JOIN lprod
                            ON(prod_lgu = lprod_gu
                                   AND lprod_nm LIKE '%전자%')
ORDER BY mem_id , cart_qty DESC;

 

<  가상테이블 >

 

가상테이블( Inline view )

SELECT  MAX(A.total), MIN(A.total)
FROM (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) A;

• From절 뒤에 테이블 대신에 Sub Query를 사용함
• Sub Query의 Select한 행렬 결과를 메모리에 적재시켜서 사용
• 행렬의 데이터를 테이블과 동일한 개념으로 가상테이블은 별칭을 이용해서 테이블 이름처럼 사용합니다.

 

 

문제5-1

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

 

< 일반방식 >

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

 

< 표준방식 >

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;

 

 

문제5-2

위 문제에 이어서..
위 결과에서 가장 지출이 큰 값과, 가장 작은 값을 조회하기 (가상테이블 적용)
조회 값 : 최대값, 최소값 딱 2개만

 

< 교수님 코드 >

SELECT mem_id, mem_name, total, MAX(A.total), MIN(A.total)
FROM (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) A;

※ 그냥  MAX(sum(prod_sale*cart_qty)) 으로 구하려 했는데 오류가 난다.
    이 문제는 가상테이블(A)을 사용해서 sum(prod_sale*cart_qty) 를 total로 별명 지어주고 그 내용을 MAX(A.total)로 표현      해준다.

 

 

문제5-3

위 문제에 이어서..
위 결과에서 최대값을 가지는 회원아이디와 이름을 조회하기

 

< 교수님 코드 >

SELECT AA.mem_id, AA.mem_name, AA.total
FROM(SELECT mem_id, mem_name, sum(prod_sale*cart_qty) AS total
FROM member, prod, cart
WHERE mem_id = cart_member
AND cart_prod = prod_id
GROUP BY mem_id, mem_name) 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 mem_id, mem_name) A) BB
WHERE AA.total = BB.total_max;

 

※ 문제5-1과 문제5-2를 각각 from에 가상테이블로 가져와서 where조건문을 줘야한다.
    (max값에 해당하는 회원정보 가져오기 위해서)

 

 

 

직접 문제 만들어보기!!

멋진 문제를 만들어준 분  

 

🥰멋진 문제를 만들어준 분의 출처 : https://mzero.tistory.com/

굉장히 엄청나!

 

문제(난이도 上)

가장 최근에 입고된 상품의 상품명과 거래처명, 

그 상품을 가장 많이 구매한 회원의 아이디, 이름, 마일리지, 해당 상품 구매 수량, 회원등급
- 회원 등급 : 마일리지 1000-2000 브론즈, 2000-3000 실버, 3000-4000 골드, 4000-5000플래티넘, 5000이상 다이아

 

<내 코드 >

SELECT B.prod_name, B.cart_prod, B.buyer_name, B.mem_id, B.mem_name, B.mem_mileage, B.cart_qty, 
(case 
when B.mem_mileage between 1000 AND 1999 then '브론즈'
when B.mem_mileage BETWEEN 2000 AND 2999 then '실버'
when B.mem_mileage BETWEEN 3000 AND 3999 then '골드'
when B.mem_mileage BETWEEN 4000 AND 4999 then '플래티넘'
when B.mem_mileage BETWEEN 5000 AND 6000 then '다이아'
ELSE '아이언'
END) rank
FROM (
    SELECT prod_name, buyer_name, mem_id, mem_name, mem_mileage, cart_qty, cart_prod
    FROM member, cart, prod, buyer
    WHERE mem_id = cart_member
          AND cart_prod = prod_id
          AND prod_buyer = buyer_id
) B,
  (
    SELECT cart_prod, MAX(cart_qty) AS max_qty
    FROM cart
    WHERE cart_prod IN (
            SELECT B.buy_prod
            FROM (SELECT MAX(buy_date) AS date_max
                  FROM buyprod) A,
               (SELECT buy_prod, buy_date
                  FROM buyprod) B
            WHERE A.date_max = B.buy_date)
    GROUP BY cart_prod 
 ) MaxQty
WHERE B.cart_prod = MaxQty.cart_prod 
AND B.cart_qty = MaxQty.max_qty
order BY prod_name;

 

1. 우선 입고상품정보(buyprod) 테이블에서 가장 최근에 입고된 상품의 상품명을 가지고 온다.

buyprod

        → 가장 최근 날짜 : 2005-06-10

        → 상품명 : P201000019, P201000020, P201000021

SELECT B.buy_prod
FROM (SELECT max(buy_date) AS date_max
             FROM buyprod) A,
            (SELECT buy_prod, buy_date
             FROM buyprod) B
WHERE A.date_max = B.buy_date;

 ※ 해석 : 가상테이블 2개 설계( max(buy_date)구하는 테이블  하나, buyprod 테이블 하나 )
               그리고 max(buy_date) = buy_date 조건을 만족하는 B 테이블에서 buy_prod 값을 구하면 된다.

 

 

2. 그럼 찾은  buy_prod값은 prod_id, cart_prod와 같다.

     해당 정보를 찾아서 prod_id와 prod_name은 다음과 같다.

prod

3. 찾은 prod_id에 해당하는 cart_qty는 이렇게 있는데 가장 많이 구매한 회원을 찾아야 하기 때문에 다음 색칠한 회원의 정보만 가져와야 한다.

cart

-- 맥스 수량
SELECT cart_prod, MAX(cart_qty) AS max_cart_qty
FROM cart
WHERE cart_prod IN (
                                    SELECT B.buy_prod
                                    FROM (SELECT MAX(buy_date) AS date_max
                                                 FROM buyprod) A,
                                                (SELECT buy_prod, buy_date
                                                 FROM buyprod) B
                                   WHERE A.date_max = B.buy_date)
GROUP BY cart_prod;

맥스 수량

 

4. 그 다음 회원 정보를 가져오자

SELECT B.prod_name, B.buyer_name, B.mem_id, B.mem_name, B.mem_mileage, B.cart_qty, B.cart_prod
FROM  (SELECT max(buy_date) AS date_max
              FROM buyprod) A,
            (SELECT prod_name, buyer_name, mem_id, mem_name, mem_mileage, cart_qty, cart_prod, buy_date
              FROM member, cart, prod, buyer, buyprod
             WHERE mem_id = cart_member
                            AND cart_prod = prod_id
                            AND prod_buyer = buyer_id
                            AND prod_id = buy_prod) B
WHERE A.date_max = B.buy_date;

 

5. 그 다음 3번과 4번을 합쳐보자. + 회원등급까지 설정하면 완료!

SELECT B.prod_name, B.cart_prod, B.buyer_name, B.mem_id, B.mem_name, B.mem_mileage, B.cart_qty, 
(case 
when B.mem_mileage between 1000 AND 1999 then '브론즈'
when B.mem_mileage BETWEEN 2000 AND 2999 then '실버'
when B.mem_mileage BETWEEN 3000 AND 3999 then '골드'
when B.mem_mileage BETWEEN 4000 AND 4999 then '플래티넘'
when B.mem_mileage BETWEEN 5000 AND 6000 then '다이아'
ELSE '아이언'
END) rank
FROM (
    SELECT prod_name, buyer_name, mem_id, mem_name, mem_mileage, cart_qty, cart_prod
    FROM member, cart, prod, buyer
    WHERE mem_id = cart_member
          AND cart_prod = prod_id
          AND prod_buyer = buyer_id
) B,
  (
    SELECT cart_prod, MAX(cart_qty) AS max_qty
    FROM cart
    WHERE cart_prod IN (
            SELECT B.buy_prod
            FROM (SELECT MAX(buy_date) AS date_max
                  FROM buyprod) A,
               (SELECT buy_prod, buy_date
                  FROM buyprod) B
            WHERE A.date_max = B.buy_date)
    GROUP BY cart_prod 
 ) MaxQty
WHERE B.cart_prod = MaxQty.cart_prod 
AND B.cart_qty = MaxQty.max_qty
order BY prod_name;

정답

 

 

---

 

문제를 풀어준 사람들 

🥰문제를 풀어준 똑똑한 조코딩님 : (MariaDB) SQL - 조인(Join)/Inline View — 미니조코딩 (tistory.com)

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