2022-06-10-조인(내부조인,외부조인,상호조인등)

01.조인 이란?

• 조인은 두 개이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만들어 내는 것

02.조인의 종류

• 내부 조인 : INNER JOIN
• 외부 조인 : OUTER JOIN
  • LEFT OUTER JOIN: 왼쪽에 있는 테이블이 중심
  • RIGHT OUTER JOIN: 오른쪽에 있는 테이블이 중심
  • FULL OUTER JOIN: 전체 테이블이 중심

03.내부 조인 실습

• 조인 중에서 가장 많이 사용 되는 조인
  • 일반적으로 join이라고 이야기 하는 것이 inner join을 지칭
• 형식

• SELECT <열 목록>
  FROM <첫 번째 테이블>
  	INNER JOIN <두 번째 테이블>
  	ON <조인될 조건>
  [WHERE 검색 조건]

• 사용하기

• SELECT *
  FROM buyTbl
  	INNER JOIN userTbl
  	ON buyTbl.userID = userTbl.userID
  WHERE buyTbl.userID = 'JYP';

• where 조건을 빼면 모든 행에 대한 결과를 없을 수 있음
  • 현재는 전체 데이터를 가지고 오고 있고 이를 선택할 수있음
• 중요하게 체크하기
  • 위 와 같이 테이블에 같은 이름이 존재하는 경우
    • 에러가 생김 어디 테이블의 userID인지 특정할 수 없기 때문에

• SELECT userID, userName, prodName, addr, mobile1||mobile2  AS "연락처"
  FROM buyTbl
  	INNER JOIN userTbl
  	ON buyTbl.userID = userTbl.userID
  WHERE buyTbl.userID = 'JYP';

• 최종적인 join문 모습

  

• -- 조건 없이도 가능하고 WHERE문을 주거나 ORDER BY도 가능함
  SELECT B.userID, U.userName, B.prodName, U.addr, U.mobile1||U.mobile2  AS "연락처"
  FROM buyTbl B
  	INNER JOIN userTbl U
  	ON B.userID = U.userID;
  	
  -- WHERE문 적용
  SELECT B.userID, U.userName, B.prodName, U.addr, U.mobile1||U.mobile2  AS "연락처"
  FROM buyTbl B
  	INNER JOIN userTbl U
  	ON B.userID = U.userID
  WHERE B.userID = 'JYP';
  
  --- ORDER BY 적용
  SELECT B.userID, U.userName, B.prodName, U.addr, U.mobile1||U.mobile2  AS "연락처"
  FROM buyTbl B
  	INNER JOIN userTbl U
  	ON B.userID = U.userID
  ORDER BY B.userID;

• 구매한 사람만 조회
  • DISTINCT를 이용하여 중복을 제거함
  • EXISTS도 같은 결과임

  

• -- DISTINCT
  SELECT DISTINCT U.userID, U.userName, U.addr
  	FROM userTbl U
  		INNER JOIN buyTbl B
  			ON U.userID = B.USERID 
  	ORDER BY U.userID;
  	
  	
  -- EXISTS
  SELECT U.userID, U.userName, U.addr
  	FROM userTbl U
  	WHERE EXISTS(
  		SELECT *
  		FROM buyTbl B
  		WHERE U.userID = B.userID);	

03.1 세개 테이블 내부 조인

• 테이블 준비

• CREATE TABLE stdTBL 
  ( stdName   NCHAR(5) NOT NULL PRIMARY KEY,
    addr	    NCHAR(2) NOT NULL
  );
  CREATE TABLE clubTBL 
  ( clubName    NCHAR(5) NOT NULL PRIMARY KEY,
    roomNo      NCHAR(4) NOT NULL
  );
  CREATE SEQUENCE stdclubSEQ;
  CREATE TABLE stdclubTBL
  (  idNum    NUMBER(5) NOT NULL PRIMARY KEY, 
     stdName  NCHAR(5) NOT NULL,
     clubName NCHAR(5) NOT NULL,
  FOREIGN KEY(stdName) REFERENCES stdTBL(stdName),
  FOREIGN KEY(clubName) REFERENCES clubTBL(clubName)
  );
  INSERT INTO stdTBL VALUES('김범수','경남');
  INSERT INTO stdTBL VALUES('성시경','서울');
  INSERT INTO stdTBL VALUES('조용필','경기');
  INSERT INTO stdTBL VALUES('은지원','경북');
  INSERT INTO stdTBL VALUES('바비킴','서울');
  INSERT INTO clubTBL VALUES('수영','101호');
  INSERT INTO clubTBL VALUES('바둑','102호');
  INSERT INTO clubTBL VALUES('축구','103호');
  INSERT INTO clubTBL VALUES('봉사','104호');
  INSERT INTO stdclubTBL VALUES(stdclubSEQ.NEXTVAL, '김범수','바둑');
  INSERT INTO stdclubTBL VALUES(stdclubSEQ.NEXTVAL,'김범수','축구');
  INSERT INTO stdclubTBL VALUES(stdclubSEQ.NEXTVAL,'조용필','축구');
  INSERT INTO stdclubTBL VALUES(stdclubSEQ.NEXTVAL,'은지원','축구');
  INSERT INTO stdclubTBL VALUES(stdclubSEQ.NEXTVAL,'은지원','봉사');
  INSERT INTO stdclubTBL VALUES(stdclubSEQ.NEXTVAL,'바비킴','봉사');

• 학생이름 기준으로 조인

  

• SELECT S.stdName, S.addr, C.clubName, C.roomNo
     FROM stdTBL S 
        INNER JOIN stdclubTBL SC
             ON S.stdName = SC.stdName
        INNER JOIN clubTBL C
  	  ON SC.clubName = C.clubName 
     ORDER BY S.stdName;

• 동아리 기준으로 조인

  

• SELECT C.clubName, C.roomNo, S.stdName, S.addr
     FROM  stdTBL S
        INNER JOIN stdclubTBL SC
             ON SC.stdName = S.stdName
        INNER JOIN clubTBL C
  	 ON SC.clubName = C.clubName
      ORDER BY C.clubName;

04.외부 조인 실습

• 조건에 만족되지 않는 행까지도 포함시키는 것
• 형식

• SELECT <열 목록>
  FROM <첫 번째 테이블>
  	<LEFT | RIGHT | FULL> OUTER JOIN <두 번째 테이블>
  	ON <조인될 조건>
  [WHERE 검색 조건]

• 실습
  • 전체 회원의 구매 기록 확인시

    

  • SELECT U.userID, U.userName, B.prodName, U.addr, U.mobile1 || U.mobile2 AS "연락처"
    	FROM userTbl U
    		LEFT OUTER JOIN buyTbl B
    			ON U.userID = B.USERID 
    	ORDER BY U.userID;

• 구매 기록이 없는 회원 목록
  • IS NULL 이용

    

  • SELECT U.userID, U.userName, B.prodName, U.addr, U.mobile1 || U.mobile2 AS "연락처"
    	FROM userTbl U
    		LEFT OUTER JOIN buyTbl B
    			ON U.userID = B.USERID
    	WHERE B.prodName IS NULL
    	ORDER BY U.userID;

04.1 세개 테이블 외부 조인

• 동아리에 가입하지 않은 학생까지 조회

  

• SELECT S.stdName, S.addr, C.clubName, C.roomNo
  	FROM stdTbl S
  		LEFT OUTER JOIN stdclubTbl SC 
  			ON S.stdName = SC.stdName
  		LEFT OUTER JOIN clubTbl C
  			ON SC.clubName = C.clubName
  	ORDER BY S.stdName;

• 학생이 한명도 없는 동아리 조회
  • 전체 결과
    • UNION 위 쿼리 결과

    • 

    • UNION 아래 쿼리 결과

    • 

  • 

• SELECT S.stdName, S.addr, C.clubName, C.roomNo
     FROM stdTBL S 
        LEFT OUTER JOIN stdclubTBL SC
            ON S.stdName = SC.stdName
        LEFT OUTER JOIN clubTBL C
            ON SC.clubName = C.clubName
  UNION 
  SELECT S.stdName, S.addr, C.clubName, C.roomNo
     FROM  stdTBL S
        LEFT OUTER JOIN stdclubTBL SC
            ON SC.stdName = S.stdName
        RIGHT OUTER JOIN clubTBL C
            ON SC.clubName = C.clubName;

04.2 CROOS JOIN

• 한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인시키는 기능
  • 회원테이블이 10개, 구매테이블이 12개의 데이터를 가지고 있다면 결과는 120개가 나온다.
  • 대량 데이터를 만들때 사용한다고 함

    

  • -- 크로스 조인
    SELECT * 
       FROM buyTbl 
         CROSS JOIN userTbl;
         
    -- 권장하지 않지만 크로스조인과 결과 같음     
    SELECT * 
       FROM buyTbl , userTbl 

04.3 SELF JOIN

• 다른 구문이 있는 것이 아닌 자기 자신과 자기 자신이 조인하는 것

  

• CREATE TABLE empTbl (emp NCHAR(3), manager NCHAR(3), department NCHAR(3));
  INSERT INTO empTbl VALUES('나사장','없음','없음');
  INSERT INTO empTbl VALUES('김재무','나사장','재무부');
  INSERT INTO empTbl VALUES('김부장','김재무','재무부');
  INSERT INTO empTbl VALUES('이부장','김재무','재무부');
  INSERT INTO empTbl VALUES('우대리','이부장','재무부');
  INSERT INTO empTbl VALUES('지사원','이부장','재무부');
  INSERT INTO empTbl VALUES('이영업','나사장','영업부');
  INSERT INTO empTbl VALUES('한과장','이영업','영업부');
  INSERT INTO empTbl VALUES('최정보','나사장','정보부');
  INSERT INTO empTbl VALUES('윤차장','최정보','정보부');
  INSERT INTO empTbl VALUES('이주임','윤차장','정보부');
  
  SELECT A.emp AS "부하직원" , B.emp AS "직속상관", B.department AS "직속상관부서"
     FROM empTbl A
        INNER JOIN empTbl B
           ON A.manager = B.emp
     WHERE A.emp = '우대리';

05.UNION, UNION ALL, NOT IN, IN

05.1 UNION [ALL]

• UNION은 두 쿼리의 결과를 행으로 합치는 것
• 형식

• SELECT 문장1
  	UNION [ALL]
  SELECT 문장2

• 조건
  • SELECT 문장1과 SELECT 문장2
    • 결과 열의 개수
    • 데이터 형식이 열 단위로 같거나 호환되어야함
• UNION과 UNION ALL 차이
  • UNION 중복된 열 1개만 나옴
  • UNION ALL 중복된 것 전부 나옴
• sql

  

• SELECT stdName, addr FROM stdTBL
     UNION ALL
  SELECT clubName, roomNo FROM clubTBL;

05.2 NOT IN, IN

• NOT IN 첫 번째 쿼리의 결과 중
  • 두 번째 쿼리에 해당하는 것을 제외하기 위한 구문
• 예시: 사용자 모두 조회하되, 전화가 없는 사람 제외하는 경우

  

• SELECT userName, CONCAT(mobile1, mobile2) AS "전화번호" FROM USERTBL
  	WHERE userName NOT IN (SELECT userName FROM userTbl WHERE mobile1 IS NULL);

• 예시: 사용자 모두 조회하되, 전화가 없는 사람 만하는 경우

  

• SELECT userName, CONCAT(mobile1, mobile2) AS "전화번호" FROM USERTBL
  	WHERE userName IN (SELECT userName FROM userTbl WHERE mobile1 IS NULL);