두개의 테이블을 Join 할 때 기준이 되는(코드) 테이블을 기준으로 join 하는 테이블(데이터)에 데이터가 없을 경우 inner join을 사용하면 기준이 되는 테이블의 행이 표시되지 않는다. 이 때 데이터가 없을 경우 0으로 표시하고 싶다면 outer join을 사용해야 한다.
예를 들어 특정 문자열이나 에러코드, 감사로그 코드의 일별 통계를 뽑을 때 한건도 없는 경우 일반적인 Inner Join을 하게 되면 건수가 0인 날짜는 출력되지 않는다. 이럴 때 해당 일자에 0건으로 출력되도록 하는 경우에 outer join을 사용할 수 있다.
다음의 두 테이블에서 예를 들어 설명하면…
table name : t_student
no name
01 홍길동
02 김철수
03 이영희
table name : t_score
code subject grade
01 math A
01 social A
02 math C
03 social B
03 english A
위의 테이블에서 아래의 결과처럼 각 이름별로 등급이 ‘A’ 인 갯수를 구하고 A가 없는 학생의 경우 0 으로 출력하고자 한다면 어떻게 해야할까?
SELECT A.no, A.name, count(B.grade)
from t_student as A left join t_score as B on A.no = B.no
where B.grade = ‘A’ group by A.no;
로 하면 될 듯 하지만 안된다. -.- 0으로 출력되기를 원했던 학생들은 하나도 출력되지 않는다.
아~ 좌절했다… 시간이란… 알고 있다고 생각했던 것도 모두 지워버리는 매우 특별한 능력을 갖고 있다. 하지만 인간의 뇌는 잊었던 것을 다시 공부하면 훨씬 빠르게 되살릴 수 있는 능력 또한 갖고 있다.
이런 저련 자료를 찾다보니 outer join 할 때 데이터가 있는 테이블에 WHERE 절로 조건을 주면 안되고 sub query를 써서 FROM 절에서 걸러내고 걸러낸 데이터셋에 outer join을 걸어야 한다는 이야기가 있었다.
즉… 위의 SQL은 아래와 같이 바뀌어야 한다는 이야기다.
SELECT A.no, A.name, count(B.grade)
FROM t_student as A LEFT OUTER JOIN (
SELECT no, COUNT(grade) AS grade
FROM t_score
WHERE grade = ‘A’
GROUP BY no) as B on (A.no = B.no)
GROUP BY A.name