오라클 데이터베이스 SQL 36강-Outer Join을 이용한 게시글 목록 쿼리

내부 조인 연습

조인된 테이블 쿼리하기

그림1

*JOIN을 다룰 때 문제가 되는 몇가지*

 

JOIN이라는 얘기는 컬럼을 합친다는 것이고 컬럼을 합칠 때 위 그림에는 컬럼의 이름이 충돌날 일이 없다. 근데 컬럼을 합칠 때 식별이 애매한 경우가 있을 수 있다.

 

게시글의 번호, 제목 작성자이름을 조회하시오.

SELECT SEQ,TITLE,NAME

FROM MEMBER INNER JOIN NOTICE ON MEMBER."ID" = NOTICE.WRITER_ID

 

필드 이름의 충돌문제

그림2

테이블의 식별자로 ID를 쓰는 경우가 많다.

 

게시글의 컬럼명을 식별하기 위해 테이블명을 사용

SELECT ID, NAME, NAME FROM

MEMBER

INNER JOIN NO MEMBER.ID = NOTICE.WRITER_ID

=> 어느 쪽의 ID를 달라는 건지, 어느 쪽의 NAME을 달라는 건지 헷갈릴 수 있다.

 

SELECT NOTICE.ID, NOTICE.NAME, MEMBER.NAME FROM

MEMBER

INNER JOIN NOTICE ON MEMBER.ID = NOTICE.WRITER_ID

=> 어느 테이블의 컬럼인지 명시화 한다.

 

컬럼명이 같은 테이블을 합칠때 그것을 SELECT 할 경우에는 식별자가 애매해서 ERROR가 발생한다. 그래서 많은 사람들이 컬럼명을 지을때 예)테이블명_ID 이런식으로 컬럼명에 테이블이름을 포함시킨다. 하지만 이것은 좋은 방법이아니다.

 

컬럼을 지정하는 식별자를 줄이기 위해서 테이블의 별칭을 사용

SELECT N.ID, N.WRITER_ID, M.NAME

FORM MEMBER M INNER JOIN NOTICE N

ON M.ID =  N.WRITER_ID

=> 이렇게 해서 INNER JOIN을 하던 OUTER JOIN을 하던 JOIN할 때 기본적인 칼럼명의 충돌 문제를 확인할 수 있다.

 

 

ID, NAME 그리고 회원별 작성한 게시글 수를 조회하시오.

일반적으로 초급자는 INNER JOIN을 쓰는 경우가 많다. INNER JOIN의 결과가 잘맞는지 안맞는지 테스트 해보지 않고, 잘돌아 간다고 생각하고 넘어가는 경우가 많다.

그림3

JOIN해서 그림3 같은 결과를 만들고 싶다. MEMBER 테이블과 NOTICE테이블이 있다면, MEMBER의 ID와 NAME 그리고 NOTICE에서 MEMBER 별로 쓴 게시물을 COUNT하고 싶다. 집계를 하기위해서는 NOTICE라는 테이블을 가져와야한다. 가져와서 집계하기위한 COUNT를 따로 만들어야 한다.

 

SELECT M.ID, M.NAME, COUNT(N.ID) FORM

MEMBER.M INNER JOIN NOTICE N

ON M.ID = N.WRITER_ID

GROUP BY M.ID,M.NAME

=>FROM 절에서 MEMBER와 NOTICE를 JOIN한다. JOIN한 결과에서 NOTICE의 ID를 가지고 집계를 한다. 집계를 하는데, 그 집계할 때 여러개의 레코드가 하나로 합쳐지는 바람에 M.ID와 M.NAME을 따로 출력 할 수 가 없다. 근데 만약에 MEMBER ID와 NAME 이라는 것을 묶어서, 집계하는 기준값 분류기준으로 삼았다고 하면, GROUP BY 절 뒤에 M.ID와 M.NAME을 써준다. 그렇니까 MEMBER 레코드 별로 집계 개수를 적은 것이다. 그렇게 하면 그림 3과 같이 나온다.

 

이렇게 하면 대충봐서는 문제가 없는 것 같다. JOIN해서 MEMBER 별로 집계개수를 집계했고,그렇게 해서 결과를 뽑아냈으니까 하지만 치명적인 문제가 있다. 그림3과 같이 회원이 2만 있을까? 멤버별 집계수를 알고 싶었는데, 멤버별이라고 하면 모든 멤버를 대상으로 삼는 것이다. 그럼 MEMBER중에 글을 등록하지 않은 사람이 하나도 없다는 얘기일까?

그림4

그림4와 같은 결과물을 집계했다는 건데 정말  아예 글을 쓰지 않는 MEMBER는 존재하지 않을까?

다시 말해 MEMBER중에는 OUTER가 없었을까?

작성 한 게시글이 0인 회원이 하나도 없다....?

 

외부 조인 연습

회원별 작성한 게시글 수를 조회하시오.

그림5

 

위에 INNER JOIN을 하는 바람에 버그가 만들어 진 것이다. 사실은 글을 등록하지 않은 유재석이라는 회원이 있다.

즉 INNER JOIN을 하면 OUTER 레코드가 빠져서 0개로 집계되어야 하는 회원의 집계가 빠지는 오류가 발생한다.

 

사실 프로그램을 만들다 보면 주인공 하나를 잡아야 한다. 지금 "ID, NAME 그리고 회원별 작성한 게시글 수를 조회하시오"에서 뽑아 내고자 하는 주인공은 회원이다. 회원은 모두다 출력하는 것이다. NOTICE 테이블과 관계가 있든 없든 회원이면 다 출력해야한다. 그리고 그 결과물에 게시물 수 를 집계해야 한다.

 

SELECT M.ID, M.NAME, COUNT(N.ID) FROM

MEMBER M LEFT OUTER JOIN NOTICE N

ON M.ID = N.WRITER

GROUP BY M.ID,M,NAME

=>주인공이 MEMBER니까 MEMBER를 주인공으로 하는 LEFT OUTER JOIN하면 된다.

 

OUTER JOIN이라는 것은 결국 주인공을 잡는 것이다. 왼쪽과 오른쪽 테이블이 있을때 지금 왼쪽 테이블이 주인공이니까 왼쪽에 있는 것 다 출력하기, 그리고 주인공을 기준으로 관계있는 것 합치기 이런 것들이 JOIN의 기본이다.

 

주인공은 하나!!

그림6

 

이렇게 테이블이 구성이 되어 있다고 가정할 때 MEMBER하고 NOTICE모두 주인공이 될 수 있다. NOTICE를 관리할 때는  NOTICE가 주인공이다. NOTICE는 다 출력하고, 필요에 따라 MEMBER 테이블에서 컬럼 몇개를 가져올 수도 있는 것이다. 또는 자식(COMMENT)는 NOTICE의 행들 마다 각각 몇개 가졌는지 집계 할 수도 있다. 또는 거글러서 ROLE의 칼럼을 가져올 수도 있는 것이다. NOTICE가 주인공이면 NOTICE는 다 출력한다.

 

업무를 다루다 보면 주인공이 되는 테이블이 하나씩 있다. 그리고 그 테이블을 기준으로 OUTER JOIN하는 것이다.

 

[출처-https://youtu.be/NAuHvZDU15M]