inner join, outer join / union과 in/not in

join에 대해서는 mysql 전반을 다룬 포스트에서 이미 한 번 다룬 바가 있습니다.

darrengwon.tistory.com/5

본 포스트에서는 inner join, outer join, self join 등에 대해 알아보는 등 조금 더 깊게 들어가볼까 합니다.

 

 

inner join

일반적으로 join이라고 하면 inner join을 말합니다.

inner 를 생략하고 join만 쓰면 inner join으로 간주하고 join됩니다.

table1의 입장에서 table2를 join합니다.

FROM <table1> INNER JOIN <table2> ON 조인될 조건 [WHERE 검색조건]

 

예를 들어 아래와 같은 경우

buytbl의 입장에서 usertbl을 join합니다. 따라서 조인 결과 buytbl의 결과가 먼저 왼쪽에 위치하게 됩니다.

join의 기준으로는 buytbl의 userID와 usertbl의 userID가 같은 것을 inner join합니다.

SELECT 
    *
FROM
    buytbl
        INNER JOIN
    usertbl ON buytbl.userID = usertbl.userID
ORDER BY num

 

이러한 join에서 자주 발생하는 오류로 특정 컬럼만 projection하고 싶은 경우에 일어납니다.

userID의 경우 두 테이블 모두 존재하므로 어느 테이블의 userID인지를 명시해줘야 합니다.

따라서 아래와 같이 작성되어야 오류가 없습니다.

SELECT num, usertbl.userID, prodName, addr -- usertbl.userID와 같이 어느 테이블의 column인지 명시할 것
  FROM buytbl
    INNER JOIN usertbl
      ON buytbl.userID = usertbl.userID
        ORDER BY num

 

추가적으로, 아래와 같이 별칭(alias)를 줄 수도 있습니다. 이 방식이 좀 더 가독성이 좋아집니다. 저는 생 sql을 짜야할 때는 별칭을 무조건 씁니다.

SELECT 
    num, U.userID, prodName, addr
FROM
    buytbl B
        INNER JOIN
    usertbl U ON B.userID = U.userID
ORDER BY num

 

dstinct를 이용해서 고유값만 가져올 수도 있습니다.

select distinct U.userID, U.name, U.addr
 from usertbl U
   inner join buytbl B
     on U.userID = B.userID

 

만약 3개 이상의 table을 inner join하기 위해서는 어떻게 해야 할까요?

우선 아래와 같은 관계를 가진 세 테이블이 존재한다고 가정합니다.

 

학생 이름, 주소, 동아리 이름, 동아리 방 주소 이렇게 column으로 가진 테이블을 join해서 만들어보면 다음과 같습니다. 단순히 inner join을 2번 연달아 쓰면 됩니다.

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;

 

 

outer join

 

inner join과 다르게 조건에 만족하지 않는 행까지도 포함시킨다. 자주 사용되는 편은 아니지만 필요할 때가 있다.

left, right, full 조건이 존재한다. 조건에 해당하지 않아도 left/right 테이블을 전부 가져오기 혹은 전부 가져오기에 해당한다. 

 

사용법은 join과 똑같습니다. 아래처럼 사용하면 됩니다.

left outer join이므로 왼쪽에 해당하는 usertbl의 내용은 전부 다 가져오고 조건에 해당하는 buytbl의 행만 가져옵니다.

 

하나의 팁으로, left, right, full 셋 중 하나만 적으면 outer join으로 인식하기 때문에 outer를 생략해도 됩니다.

select U.userID, U.name, B.prodName, U.addr, concat(U.mobile1, U.mobile2) as '연락처'
  from usertbl U
    left outer join buytbl B
      on U.userID = B.userID
        order by U.userID;

 

위와 똑같은 결과를 가지는 right 조인을 사용해보았습니다. 테이블 위치만 바꿔주면 똑같이 나오겠죠?

select U.userID, U.name, B.prodName, U.addr, concat(U.mobile1, U.mobile2) as '연락처'
  from buytbl B
    right join usertbl U
      on U.userID = B.userID
        order by U.userID;

 

left/right/full 개념을 테이블 3개 이상을 활용할 때도 사용할 수 있습니다. 

select S.stdName, S.addr, C.clubName, C.roomNo
  from stdtbl S
    left join stdclubtbl SC
      on S.stdName = SC.stdName
	right join clubtbl C
      on SC.clubName = C.clubName;

 

 

 

 

그 외의 join

 

cross join

 

한 테이블의 행을 다른 테이블의 모든 행과 join하는 기능이다.

5개 행을 가진 테이블은 10개 행을 가진 테이블과 cross join하면 50개의 행을 가진 테이블이 된다.

cross join은 실무에서 사용된다기 보다는 테스트용 데이터가 필요할 때 사용된다고 한다.

 

select * from buytbl cross join usertbl;

 

self join

 

자기 자신과 join하는 것이다.  join할 때 별칭을 꼭 붙여주도록하자.

create table emptbl (
  emp char(3),
  manager char(3),
  emptel varchar(8)
);

insert into emptbl values('나사장', null, '0000');
insert into emptbl values('김재무', '나사장', '2222');

select A.emp as '부하직원', B.emp as '직속상관'
  from emptbl A
    join emptbl B
      on A.manager = B.emp
        where A.emp = '김재무'

 

 

union

 

단순히 두 쿼리의 결과를 합치는 것입니다.

union : 중복행을 제거 후 출력

union all : 중복행까지 출력

 

select stdName, addr from stdtbl
 union all 
   select clubName, roomNo from clubtbl;

 

union의 결과를 보면 select의 결과물들이 단순히 합쳐진 것을 볼 수 있습니다.

대부분 엉뚱한 결과물이기 때문에 대용량 테이블을 분리시킨 경우 이후 활용 시에 합치기 위한 용도 정도로 사용합니다.

 

 

in/not in

select name, concat(mobile1, mobile2) as 'contact'
  from usertbl
    where name not in (select name from usertbl where mobile1 is null);