JOIN (INNER JOIN / OUTER JOIN)

JOIN

- 둘 이상의 테이블의 행을 결합

자주 쓰이는 종류는 INNER, OUTER 이너와 아우터 이다.

 

실습 테이블 미리 추가해 두자

book.sql

0.02MB

명령어 : @c:\db\book.sql

 

book db에 있는 테이블 정보들

 

 

INNER JOIN

이너 조인 종류 : EQUI JOIN / NATURAL JOIN / CROSS JOIN / SELF JOIN / NON-EQUI JOIN

네츄럴 크로스 셀프는 다루지 잘 안쓰인다.

많이 쓰이는데 EQUI JOIN이다.

 

EQUI JOIN

: 조인 대상 테이블의 컬럼 값들이 서로 정확하게 일치하는 경우에 사용

WHERE 절에 = 부등호를 이용한다.

 

         SELECT b.bCode, bName, bPrice, b.pNum, pName, sDate, 
                   s.cNum, cName, qty, bPrice * qty amt
         FROM book b
         JOIN  pub p  ON  b.pNum = p.pNum
         JOIN  dsale d  ON  b.bCode = d.bCode
         JOIN  sale s  ON  d.sNum = s.sNum
         JOIN  cus c  ON  s.cNum = c.cNum;

컬럼들을 가져오는데 처음엔 book에서 가져오고

(옆에 b,p,d,s 이런것들은 별명임 AS 생략된 형태)

 

다른 필요한 항목들을 가져오기 위해 JOIN을 한다.

book의 pNum과 pub의 p.Num이 공통 되니까 1차로 조인 -> book과 pub의 모든 컬럼을 가져왔음

그리고 다시 dsale의 bCode와 book의 bCode가 공통 되니까 가져온다

그리고 sale을 가져오기 위해서 위에서 가져왔던 dsale과 공통이 되는 sNum을 활용

마지막으로 cus를 가져오기 위해 공통이 되는 컬럼을 찾는다

cNum은 sale에 있었다.

 

이렇게 하면 맨 위에 SELECT 부분에서 우리가 가져오면서 이름이 겹치는 애들은 오류가 뜨니까 앞에 명시를 해줘야한다.

bCode -> b.bCode 이런식으로 

pNum과 sNum 마찬가지로 앞에 명시를 해줘야한다.

 

판매된 책코드(bCode), 책이름(bName), 판매수량(qty) : 책코드 순서로 오름차순

              SELECT b.bCode, bName, qty
              FROM book b
              JOIN  dsale d ON b.bCode = d.bCode
              ORDER BY bCode;

판매된 책코드(bCode), 책이름(bName), 판매수량의합(qty) : 책코드 오름차순

              SELECT b.bCode, bName, SUM(qty)
              FROM book b
              JOIN  dsale d ON b.bCode = d.bCode
              GROUP BY b.bCode, bName
              ORDER BY bCode;

 

판매현황 : 서점명이 '서울서점'인 자료만 출력 고객번호 순 오름차순 출력

              SELECT b.bCode, bName, bPrice, sDate, s.cNum, cName, qty, bPrice*qty amt
              FROM book b
              JOIN dsale d ON b.bCode = d.bCode
              JOIN sale s ON d.sNum = s.sNum
              JOIN cus c ON s.cNum = c.cNum
              WHERE cName = '서울서점'
              ORDER BY cNum;

판매된 책코드(bCode), 책이름(bName), 판매수량의합(qty) : 단, 판매수량합이 80이상인 책만 출력

           SELECT b.bCode, bName, SUM(qty) 판매수량합
           FROM book b
           JOIN dsale d ON b.bCode = d.bCode
           GROUP BY b.bCode, bName
           HAVING SUM(qty) >= 80;

판매된 책코드(bCode), 책이름(bName) : 중복 배제

                SELECT DISTINCT b.bCode, bName
                FROM book b
                JOIN dsale d ON b.bCode = d.bCode;
                
               SELECT DISTINCT b.bCode, bName
               FROM book b, dsale d
               WHERE b.bcode = d.bcode
               ORDER BY bCode;

 

판매된 책 중 판매권수의 합이 가장 큰 책 코드, 책 이름 출력

              SELECT bCode, bName FROM (
                  SELECT b.bCode, bName, RANK() OVER( ORDER BY SUM(qty) DESC ) 순위
                  FROM book b
                  JOIN  dsale d ON b.bCode = d.bCode
                  GROUP BY b.bCode, bName
              ) WHERE 순위 = 1;

 

올해의 판매현황 출력

 

SELECT b.bcode, bname, bprice,b. pnum, pname, sdate,s.cnum,cname,qty,bprice*qty AS amt
FROM book b
JOIN pub p ON b.pnum = p.pnum
JOIN dsale d ON b.bcode = d.bcode
JOIN sale s ON d.snum = s.snum
JOIN cus c ON s.cNum = c.cnum
WHERE TO_CHAR(sDate,'YYYY') = TO_CHAR(SYSDATE,'YYYY');

전체 출력을 먼저 하고 WHERE절로 올해만 나올 수 있게 해준다.

 

작년의 판매현황 출력

SELECT b.bcode, bname, bprice,b. pnum, pname, sdate,s.cnum,cname,qty,bprice*qty AS amt
FROM book b
JOIN pub p ON b.pnum = p.pnum
JOIN dsale d ON b.bcode = d.bcode
JOIN sale s ON d.snum = s.snum
JOIN cus c ON s.cNum = c.cnum
WHERE TO_CHAR(sDate,'YYYY') = TO_CHAR(SYSDATE,'YYYY')-1;

마지막에 -1만 해주면 작년이 된다.

 

 

고객번호(cNum), 고객이름(cName), 년도, 판매금액합

고객번호 오름차순, 년도 오름차순

SELECT s.cNum, cName, TO_CHAR(sDate,'YYYY') 년도 , SUM(bprice * qty) 금액
FROM book b
JOIN dsale d ON b.bCode = d.bCode
JOIN sale s ON d.snum = s.snum
JOIN cus c ON s.snum = c.cnum
GROUP BY s.cnum, cname, TO_CHAR(sDate,'YYYY')
ORDER BY cnum,년도;

 

 

OUTER JOIN

종류 : LEFT OUTER JOIN, RIGTH OUTER JOIN, FULL OUTER JOIN

 

LEFT JOIN

 

판매된 책만 출력

SELECT b.bcode, bname
FROM book b
LEFT OUTER JOIN dsale d ON b.bcode = d.bcode
WHERE d.bcode IS NOT NULL;

LEFT OUTER 조인으로 book의 모든 값들을 가져오게 되는데

WHERE절에 d.bcode IS NOT NULL의 의미는

dsale(판매된 책의) bcode(책 코드)가 널이면 안된다, 즉 판매된 놈들은 책코드가 존재하기 때문에(안팔린놈들은 널임)

NOT NULL을 써주면 판매된 책만 출력하게 된다.

 

 

전체 책 리스트와 판매된 책을 같이 보고 싶을때

SELECT b.bcode, bname, d.bcode
FROM book b
LEFT OUTER JOIN dsale d ON b.bcode = d.bcode;

이럴때는 그냥 LEFT OUTER 조인으로 book을 먼저 잡아주면 

먼저 book의 모든 값들을 가져오고

그 다음으로 dsale와 교집합을 가져온다.

 

OUTER JOIN

LEFT JOIN 과는 반대 개념이라 생각하면 된다.

 

FULL OUTER JOIN

 

모든 결과를 다 가져올 수 있고

반대로 교집합만 빼고 나머지를 가져올 수 있다.

 

 

회원, 비회원 판매 현황 구하기 (전혀 구매 하지 않은 회원도 나옴)

SELECT snum, sdate, s.cnum, m.cnum, userId
FROM sale s
FULL OUTER JOIN member m ON s.cnum = m.cnum;