외부 조인을 활용한 게시글 수 집계, self 조인. 2021-08-12

일단 저번 포스트에서 보다싶이 나는 member 테이블과 notice 테이블을 만들었다. 이번에는 외부조인이 실질적으로 어떻게 활용되는지 보이겠다.

notice 테이블

 

member 테이블

우선, 내부조인(inner join)을 활용해서 게시물 수를 검색해보자. 

select m.name,count(n.writer_id) 
from member m inner join notice n on m.name=n.writer_id 
group by m.name;
--count함수 안에 집계하는 칼럼으로는 조인된 테이블의 칼럼의 필드중 무엇이든 올수 있다.
--그러나 집계의 기준, 분류의 기준이되는 group by 의 인자로는 무조건 member테이블의 칼럼이 와야한다.
--왜냐하면, 인저사항 별, 그 사람의 게시글수를 집계하는것이 목적이기 때문이다.

어? 그런데 뭔가 이상하다. 분명 member 별 게시글 수를 집계했는데, member테이블에 있는 최명자, 후니씨는 게시글 0 이렇게 나오지 않는다.. 이유는 내부 조인 이라서 에초에 합쳐진 테이블에 최필진씨가 존재하지도 않아서 집계가 안된 것이다.

그렇다면, 외부조인으로 member 테이블을 전부다 합쳐보자.

이제서야 알맞게 아예 게시글을 안 쓴 사람것도 집계가 되었다. 어찌보면 당연한 것이, 에초에 count함수를 쓸때 from 절안에서 조인된 테이블에 outer 레코드들이 없어서 집계가 안된 것이다.

 

즉, outer join은 '주인공'을 정하는 것이라고 생각하면 된다. 자식테이블에서 매치되는 key가 있든없든 무조건 outerjoin을 한 테이블의 모든 레코드는 출력이 된다. 그래서, 그 레코드들을 주인공(주체) 삼아서, 집계나 다른 작업들을 할 수 있는 것이다.

 

다음은 self 조인에 대해서 설명하겠다. 오해하지 말아야 할 것이, 모든 join은 inner와 outer 밖에 없다. 말이 self 조인이지, 그냥 부모테이블, 자식 테이블 이 같은 조인이다. 그럼 어떻게 구별하느냐? 바로 from 절에서 별칭을 붙여서 구별한다. 이런 셀프 조인을 쓰는 대표적인 경우는 인적사항 에서 조직도를 나타낼 때 이다. 즉, 인적사항별로, 그 사람의 보스를 나타내고, 사원들 별로 정렬하려면, 자기 자신의 테이블을 연결시킬 수 밖에 없다.

이런식으로 말이다. 그런데 이럴려면 인적사항을 나타내는 member 테이블에 직속상사를 정할수 있는 새로운 칼럼을 만들어야 한다.

이렇게 DDL 쿼리를 직접 쓰지않고 SQL DEVELOPER 편집기로 직접 새로운 칼럼을 만들었다. PK를 ID로 설정했는데 이것에 대한 포스트는 나중에 쓰겠다.

그리고 각 인적사항별로 직장상사의 ID를 입력했다.

그전까진 insert into member()~ 쿼리를 일일이 다써서 테이블에 데이터를 입력했는데... 이런 방법이 있었다니..!! ㅋㅋㅋ 삽질을 괜히했지만 지금이라도 알아서 좋다. 여기 테이블 편집기에 노란색으로 표시된 칸을 클릭하면, 쿼리가 아닌 직접 칼럼에 값을 추가 할 수 있다.

이렇게 원하는 데이터 값을 다 넣은뒤 새로고침을 눌러주면, SQL DEVELOPER가 쿼리를 자동으로 넣어준다.

 

또 유의해야 할 점은 쿼리를 쓸 떄는 '칼럼명' 은 대소문자 구별을 안하지만, '칼럼 값'은 대소문자를 구별 하기에, 정확한 ID에 맞게 값을 입력해야 한다.

 

입력 한다음 , self 조인 쿼리를 작성해보면,

select m.name,b.name as "상사이름" from member m left outer join member b on m.boss_id=b.id;

이렇다. 또한 부모, 자식 테이블 모두 member란 테이블을 사용한다. 별칭으로 구별을 하였고, 근데 또 주의할 점은 주인공이 누구냐 이다. 이번엔 '인적사항'이 모두 출력되어야 하기에,  m 테이블 쪽으로 outer join을 하였다.

쿼리의 실행결과.

이 self조인은 실전 현업에서 매우 빈번하게 사용된다고 한다. 예를들면 카테고리속에 카테고리가 있는경우라던지, 댓글에 또 덧글을 다는 경우 모두 이 self 조인을 쓴다고 한다. 나중에 내 프로젝트 만들때 오늘 공부 정리한 내용을 다시 돌려보며 복습해야 겠다.

join 쿼리에서 중요한 점: 1.'주인공'이 누구인가. 주인공이라 함은, 어떤 테이블이 다 출력되어야 하는가이다. 즉, 어떤 테이블의 레코드들이 outer가 되면 안돼는지 판단하면 쉽다.
2.outer join을 통해 주인공 테이블 쪽으로 left/right 키워드를 사용한다.

 

공부시간 2시간 30분.

순공시간 1시간.

 

오늘은 점심때 요양원에서 단체로 코로나 검사 맡으러 선별 검사소에 갔다.

그리고 역시 나 직접 코딩하면서 공부하니 집중이 잘 되어서 오늘도 그럭저럭 잘 공부 한 거 같다.