외부조인이란?
- 외부 조인은 내부 조인과는 조금 다릅니다.
- 내부 조인은 조인에 참여하는 두 테이블을 기준으로 조인 칼럼의 값이 같은 건(로우)만 조회합니다.
- 그런데 외부 조인(outer join)은 기본으로 조인 칼럼의 값이 같은 건을 조회하면서 한쪽 테이블에 조인 칼럼의 값이 같지 않은 건이 있으면 이 건까지 함께 조회할 수 있습니다.
SELECT a.continent, COUNT(*)
FROM country a
GROUP BY a.continent;
country 테이블에서 대륙별로 몇 개 국가가 있는지를 구하는 쿼리입니다. 결과를 보면 총 7개 대륙의 국가 수가 조회됐습니다.
SELECT a.continent, COUNT(*)
FROM country a
INNER JOIN city b
ON a.code = b.countrycode
GROUP BY a.continent;
city 테이블과의 내부 조인을 추가했습니다. city 테이블과 조인했으니 COUNT() 함수가 반환하는 숫자는 해당 대륙에 속한 국가의 도시 수 합계가 되겠네요.
결과를 보면 총 6건이 조회됐습니다. 이전 쿼리보다 1건이 적습니다. 대륙명이 ‘Antarctica’인 건이 빠져 있는데, 왜 이 건이 빠진 채 조회됐을까요?
원인은 내부 조인 때문입니다. Antarctica는 남극을 뜻합니다. 남극에는 몇몇 국가의 연구시설 외에는 사람이 거주하는 곳이 없습니다. 따라서 남극에 속한 도시는 없다고 볼 수 있습니다. 내부 조인은 두 테이블 간에 조인 칼럼의 값이 같은 건이 조회되는데, 남극은 city 테이블에 해당 데이터가 없어 조회되지 않았습니다.
그런데 결과에서 남극이 빠져 있어 자칫 country 테이블에는 남극 대륙 정보가 없다고 오해할 수 있습니다. 그렇다면 남극 대륙을 포함해서 조회할 방법은 없을까요? 물론 남극에는 도시가 없으므로 COUNT() 함수가 반환하는 도시 수는 0으로 보여 주는 것이죠. 바로 이럴 때 외부 조인을 사용합니다.
외부 조인을 하면 조인 조건을 만족하는 건은 물론 조인 조건을 만족하지 않은 건까지 추가로 조회할 수 있습니다. 외부 조인에는 LEFT 조인과 RIGHT 조인, 두 가지 방법이 있는데 순서대로 살펴보겠습니다.
left join 의 이해
SELECT a.continent, COUNT(*)
FROM country a
LEFT OUTER JOIN city b
ON a.code = b.countrycode
GROUP BY a.continent;
SELECT a.continent, COUNT(*) 전체건수, COUNT(b.name) 도시건수
FROM country a
LEFT OUTER JOIN city b
ON a.code = b.countrycode
GROUP BY a.continent;
도시 수를 의미하는 COUNT() 함수가 반환하는 숫자가 남극을 보면 5입니다. 분명히 앞에서 남극에 속한 국가에 해당하는 데이터가 city 테이블에 존재하지 않는 것을 확인했는데 말이죠.
사실 COUNT의 매개변수로 를 넘겼기 때문에 결과로 반환하는 숫자는 조회된 로우 수입니다. 남극을 제외한 다른 대륙은 모두 city 테이블에 데이터가 있으므로 실질적으로 COUNT()가 반환하는 숫자는 도시 수로 보는 것이 맞습니다. 하지만 LEFT 조인으로 country 테이블에 있는 남극 데이터가 조회됐습니다. 즉, 조회된 5는 country 테이블에 담긴 남극 대륙에 속한 국가 건수를 의미합니다. 만약 정확한 도시 수를 구하고 싶다면 다음과 같이 쿼리를 작성하면 됩니다.
COUNT(*)는 전체 건수를, COUNT(b.name)는 도시 건수를 반환합니다. COUNT() 함수의 매개변수로 city 테이블의 도시명 칼럼(name)을 넘기니 남극 대륙은 0을 반환했습니다. 남극 대륙에 속한 도시가 없음을 확실하게 확인할 수 있습니다.
right join 의 이해
SELECT a.continent, COUNT(*) 전체건수, COUNT(b.name) 도시건수
FROM city b
RIGHT OUTER JOIN country a
ON a.code = b.countrycode
GROUP BY a.continent;
문제)
아프리카(Africa) 대륙에 속한 국가 중 사용 언어가 없는 국가가 있습니다. country와 countrylanguage 테이블을 외부 조인해서 이 국가의 이름이 무엇인지 찾는 쿼리를 작성하세요.
SELECT a.name, COUNT(b.language)
FROM country a
LEFT JOIN countrylanguage b
ON a.code = b.countrycode
WHERE a.continent = 'Africa'
GROUP BY a.name
HAVING COUNT(b.language) = 0;
해설 FROM 절에 country, LEFT JOIN 다음에 countrylanguage 테이블을 명시하고, ON 절에 조인 칼럼인 code와 countrycode의 값이 같은 조인 조건을 기술합니다. 그리고 아프리카(Africa) 대륙에 속한 국가만 조회하므로 WHERE 절에 조회 조건을 줘서 continent 값이 ‘Africa’인 건을 걸러냅니다. 그리고 GROUP BY 절에 a.name을 명시해 국가명별로 그룹화하고, SELECT 절에서 COUNT(b.language)로 각 국가에서 사용하는 언어의 수를 구합니다. 이때 사용 언어가 없는 국가만 조회해야 하므로 HAVING 절에 COUNT(b.language)가 반환하는 값이 0인 값을 넣어 골라 냅니다. 쿼리를 실행하면 사용 언어가 없는 아프리카 대륙의 국가로 ‘British Indian Ocean Territory’가 조회됩니다.
