2.6. 테이블 JOIN

지금까지 하나의 쿼리에서 하나의 테이블만을 사용했었지요. 지금부터는 하나이상의 테이블 - 물론 정확히 말해서 테이블이 아닌 뷰가 될 수도 있고, SELECT 구문으로 만들어지는 특정 row들일 수도 있고, 심지어 서버 함수의 리턴값으로 사용된 row들일 수도 있습니다 - 을 같이 연결(JOIN)해서 자료를 뽑아내는 것을 다룰 것입니다. 이것을 RDBMS 용어로 테이블 JOIN이라고 합니다. 지금까지 사용한 테이블들을 예를 들어, 특정 도시의 위치와 날씨를 함께 나열하려면, 가장 단순히 생각해서, 일단 weather 테이블의 내용을 하나씩 나열하면서, 그 옆에 weather 테이블에 있는 city 칼럼값을 가지고, cities 테이블을 조회해서 location 칼럼값을 같이 보여주면 되겠지요. 

아무튼 위에서 원하는 결과를 만들어 내려면 다음과 같은 구문을 사용합니다:

SELECT *
    FROM weather, cities
    WHERE city = name;

     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
---------------+---------+---------+------+------------+---------------+-----------
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
(2 rows)

윗 결과에서 다음 두가지를 주의 깊게 보셔야합니다:

• 이 결과에서는 Hayward 도시 정보가 빠져있습니다. 이것은 cities 테이블에 이 도시에 대한 정보가 빠져있기 때문입니다. 즉, 윗 구문에서는 where 절 다음에 나오는 조건에 일치하는 자료들만 보이게 됩니다. 물론 이글 아래에서 이 문제는 다른 방법으로 보완할 것입니다.

• 다른 하나는 도시 이름이 두개가 보이는 것입니다. 잘못된 결과가 아니라, 출력 칼럼을 지정하는 곳에서 * 문자를 사용해서 모든 칼럼을 보겠다고 했으니, weather 테이블과 cities 테이블에 있는 각각의 도시 이름이 모두 보이게 된 것입니다. 이 문제는 다음과 같이 보고 싶은 칼럼만 지정함으로 해결 할 수 있습니다:

  SELECT city, temp_lo, temp_hi, prcp, date, location
      FROM weather, cities
      WHERE city = name;

지금까지는 사용된 칼럼 이름이 각각 고유한 이름들이 있었기 때문에, 별 문제없이 자동으로 각각의 칼럼이 있는 테이블에서 자료를 가져왔습니다. 하지만, 만일 같은 칼럼 이름이 여러 테이블에 똑 같이 있다면, 윗 구문처럼 사용하면, 서버는 어떤 테이블에서 그 칼럼을 참조해야 하는 지를 몰라 오류를 냅니다. 그래서, 일반적으로 join 구문을 작성할 때는 다음과 같이 칼럼 이름 앞에 테이블 명을 함께 지정해서 사용합니다:

SELECT weather.city, weather.temp_lo, weather.temp_hi,
       weather.prcp, weather.date, cities.location
    FROM weather, cities
    WHERE cities.name = weather.city;

위의 JOIN 구문은 다음과 같이 다른 표현 방식으로 사용될 수도 있습니다:

SELECT *
    FROM weather INNER JOIN cities ON (weather.city = cities.name);

물론 윗 구문은 흔하게 사용되는 구문은 아니지만, 다음 OUTER JOIN 구문을 이해하는데 도움을 주기에 잠깐 언급했습니다.

이제 앞에서 이야기한 Hayward 문제를 풀어봅시다. 윗 작업의 원인이 바로 두 테이블간에 도시이름이 일치하는 자료만 뽑혀진다는 것인데, 원래 의도했던 결과는 한 테이블에 해당 도시가 없으면, 그 구해진 자료만이라도 나타났으면 하는 것입니다. 이런 경우에 바로 OUTER JOIN 구문을 사용합니다:

SELECT *
    FROM weather LEFT OUTER JOIN cities ON (weather.city = cities.name);

     city      | temp_lo | temp_hi | prcp |    date    |     name      | location
---------------+---------+---------+------+------------+---------------+-----------
 Hayward       |      37 |      54 |      | 1994-11-29 |               |
 San Francisco |      46 |      50 | 0.25 | 1994-11-27 | San Francisco | (-194,53)
 San Francisco |      43 |      57 |    0 | 1994-11-29 | San Francisco | (-194,53)
(3 rows)

윗 구문을 left outer join 구문이라고 합니다. 이것은 왼쪽 자료를 기준으로 오른쪽 자료가 만족하는 것이 없으면, 그 값은 null로 출력됩니다. outer join을 처리하는 방식은 left, right, full 세가지가 있습니다. 이 처리 방식의 지정은 outer join 앞에 지정합니다. 윗 예제처럼.

하나의 테이블과 다른 하나의 테이블을 join 할때, 그 다른 하나의 테이블이 바로 그 테이블이 될 수도 있습니다. 이것은 self join 이라고 합니다. 예를 들어, 다음 쿼리는 현재 row의 최저기온이 다른 row의 최저기온보다 더 낮고, 최고 기온이 다른 row의 최고기온보다 더 높을 경우 그 row를 출력하는 것입니다 (기온차가 심한 도시를 다른 도시 (또는 같은 도시일 수도 있겠지요)들의 기온차와 함께 비교하려고 한 것이네요): 

SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
    W2.city, W2.temp_lo AS low, W2.temp_hi AS high
    FROM weather W1, weather W2
    WHERE W1.temp_lo < W2.temp_lo
    AND W1.temp_hi > W2.temp_hi;

     city      | low | high |     city      | low | high
---------------+-----+------+---------------+-----+------
 San Francisco |  43 |   57 | San Francisco |  46 |   50
 Hayward       |  37 |   54 | San Francisco |  46 |   50
(2 rows)

여기서 W1, W2 는 각각 weather table의 별명(alias)입니다. 이렇게 같은 테이블을 join 할 때, 서로 구분하기 위해서 이런 별명이 사용되기도 하고, 다음과 같이 테이블이 서로 틀리지만, 구문을 좀더 쓰기 편하게 하기 위해서 사용되기도 합니다:

SELECT *
    FROM weather w, cities c
    WHERE w.city = c.name;

가장 일반적인 join 구문 표현 방법입니다. 주로 테이블의 약식표기를 사용하지요.