러닝 SQL-(5) 다중 테이블 쿼리

김동환·2021년 5월 12일

조인

아래와 같은 client와 address 두 테이블에서 데이터를 검색하기 위해서는 조인이 필요합니다.

MariaDB [sakila]> desc client;
+-------------+----------------------+------+-----+---------------------+-------------------------------+
| Field       | Type                 | Null | Key | Default             | Extra                         |
+-------------+----------------------+------+-----+---------------------+-------------------------------+
| client_id   | smallint(5) unsigned | NO   | PRI | NULL                | auto_increment                |
| store_id    | tinyint(3) unsigned  | NO   | MUL | NULL                |                               |
| first_name  | varchar(45)          | NO   |     | NULL                |                               |
| last_name   | varchar(45)          | NO   | MUL | NULL                |                               |
| email       | varchar(50)          | YES  |     | NULL                |                               |
| address_id  | smallint(5) unsigned | NO   | MUL | NULL                |                               |
| active      | tinyint(1)           | NO   |     | 1                   |                               |
| create_date | datetime             | NO   |     | NULL                |                               |
| last_update | timestamp            | NO   |     | current_timestamp() | on update current_timestamp() |
+-------------+----------------------+------+-----+---------------------+-------------------------------+
9 rows in set (0.004 sec)

MariaDB [sakila]> desc address;
+-------------+----------------------+------+-----+---------------------+-------------------------------+
| Field       | Type                 | Null | Key | Default             | Extra                         |
+-------------+----------------------+------+-----+---------------------+-------------------------------+
| address_id  | smallint(5) unsigned | NO   | PRI | NULL                | auto_increment                |
| address     | varchar(50)          | NO   |     | NULL                |                               |
| address2    | varchar(50)          | YES  |     | NULL                |                               |
| district    | varchar(20)          | NO   |     | NULL                |                               |
| city_id     | smallint(5) unsigned | NO   | MUL | NULL                |                               |
| postal_code | varchar(10)          | YES  |     | NULL                |                               |
| phone       | varchar(20)          | NO   |     | NULL                |                               |
| last_update | timestamp            | NO   |     | current_timestamp() | on update current_timestamp() |
+-------------+----------------------+------+-----+---------------------+-------------------------------+
8 rows in set (0.004 sec)

데카르트 곱

조인을 위한 가장 쉬운 방법은 custoemr 및 address 테이블을 쿼리의 from 절에 모두 주고 조인하는 것입니다. 총 599명의 고객과 603명의 주소로 이루어진 테이블을 아래와 같이 조인하면 자그마치 361,197개의 행이 결과로 나옵니다. 이는 쿼리가 어떻게 두 테이블을 조인해야 할지 지정하지 않았기 때문에, 두 테이블의 모든 순열을 생성했기 때문입니다. 이러한 유형의 조인을 교차 조인이라고 하며, 거의 사용되지 않습니다.

MariaDB [sakila]> SELECT c.first_name, c.last_name, a.address
    -> FROM client c JOIN address a;
+------------+-----------+----------------------+
| first_name | last_name | address              |
+------------+-----------+----------------------+
| MARY       | SMITH     | 47 MySakila Drive    |
| PATRICIA   | JOHNSON   | 47 MySakila Drive    |
| LINDA      | WILLIAMS  | 47 MySakila Drive    |  
| BARBARA    | JONES     | 47 MySakila Drive    |
| ELIZABETH  | BROWN     | 47 MySakila Drive    |
| JENNIFER   | DAVIS     | 47 MySakila Drive    |
| MARIA      | MILLER    | 47 MySakila Drive    |
...
| TERRANCE   | ROUSH     | 1325 Fukuyama Street |
| RENE       | MCALISTER | 1325 Fukuyama Street |
| EDUARDO    | HIATT     | 1325 Fukuyama Street |
| TERRENCE   | GUNDERSON | 1325 Fukuyama Street |
| ENRIQUE    | FORSYTHE  | 1325 Fukuyama Street |
| FREDDIE    | DUGGAN    | 1325 Fukuyama Street |
| WADE       | DELVALLE  | 1325 Fukuyama Street |
| AUSTIN     | CINTRON   | 1325 Fukuyama Street |
+-------------+--------------+------------------+
361197 rows in set (0.084 sec)

내부 조인

교차 조인처럼 모든 순열을 반환하지 않고 각 고객에 대해 단일 행만 반환하도록 하기 위해서는, 두 테이블의 관계를 나타내는 정보를 from 절의 하위에 추가해야 합니다. 아래는 이에 대한 쿼리인데 이 때, 한쪽 테이블에만 값이 존재할 경우 조인이 실패하고 해당 행이 결과에서 제외됩니다. 이러한 유형의 조인을 내부 조인이라고 합니다. 쿼리에서 특별히 표시하지 않고 JOIN이라고만 표기하면 데이터베이스 서버는 내부 조인을 수행하지만, 명시적으로 나타내기 위해 INNER JOIN으로 명확히 표기하는 것이 좋습니다.

MariaDB [sakila]> SELECT c.first_name, c.last_name, a.address
    -> FROM client c INNER JOIN address a
    ->   ON c.address_id = a.address_id
+-------------+--------------+--------------------------+
| first_name  | last_name    | address                  |
+-------------+--------------+--------------------------+
| MARY        | SMITH        | 1913 Hanoi Way           |
| PATRICIA    | JOHNSON      | 1121 Loja Avenue         |
| LINDA       | WILLIAMS     | 692 Joliet Street        |
| BARBARA     | JONES        | 1566 Inegl Manor         |
| ELIZABETH   | BROWN        | 53 Idfu Parkway          |
...
| TERRENCE    | GUNDERSON    | 844 Bucuresti Place      |
| ENRIQUE     | FORSYTHE     | 1101 Bucuresti Boulevard |
| FREDDIE     | DUGGAN       | 1103 Quilmes Boulevard   |
| WADE        | DELVALLE     | 1331 Usak Boulevard      |
| AUSTIN      | CINTRON      | 1325 Fukuyama Street     |
+-------------+--------------+--------------------------+
599 rows in set (0.003 sec)

이 때 조인에 사용하는 열의 이름이 동일할 경우 아래와 같이 using 절을 이용할 수 있습니다.

SELECT c.first_name, c.last_name, a.address
FROM client c INNER JOIN address a
  USING (address_id);

ANSI 조인 문법

이 글에서 사용하는 조인 문법은 SQL92 스펙을 따릅니다. 다만, 데이터베이스 서버들은 아래와 같은 SQL92 스펙 이전의 쿼리도 이해합니다.

SELECT c.first_name, c.last_name, a.address
FROM client c, address a
WHERE c.address_id = a.address_id;

이러한 이전 문법과 비교할 때 SQL92 스펙의 ANSI 조인 문법은 다음과 같은 이점을 가집니다.

조인 조건과 피터 조건이 이해하기 쉽게 두 개의 다른 절(on, where)로 구분
조인 조건이 on 절에 포함되어 있어서 조인 조건이 누락될 가능성이 낮아짐
쿼리가 표준화되어 있어 데이터베이스 서버 간에 이식이 용이

아래는 동일한 쿼리를 이전 문법과 SQL92 스펙을 적용한 쿼리에 대해서 사용한 예시를 보여줍니다.

/* 이전 문법: 조인 조건과 필터 조건이 모두 WHERE문 안에 포함되어 있음*/
SELECT c.first_name, c.last_name, a.address
FROM client c, address a
WHERE c.address_id = a.address_id
  AND a.postal_code = 52137;
  
/* SQL92: 조인 조건은 ON으로 필터 조건은 WHERE로 분리되어 있음 */
SELECT c.first_name, c.last_name, a.address
FROM client c INNER JOIN address a
  ON c.address_id = a.address_id
WHERE a.postal_code = 52137;

세 개 이상 테이블 조인

아래와 같은 city 테이블까지 포함하여 고객의 도시를 반환하는 쿼리를 작성할 수 있습니다.

MariaDB [sakila]> desc city;
+-------------+----------------------+------+-----+---------------------+-------------------------------+
| Field       | Type                 | Null | Key | Default             | Extra                         |
+-------------+----------------------+------+-----+---------------------+-------------------------------+
| city_id     | smallint(5) unsigned | NO   | PRI | NULL                | auto_increment                |
| city        | varchar(50)          | NO   |     | NULL                |                               |
| country_id  | smallint(5) unsigned | NO   | MUL | NULL                |                               |
| last_update | timestamp            | NO   |     | current_timestamp() | on update current_timestamp() |
+-------------+----------------------+------+-----+---------------------+-------------------------------+
4 rows in set (0.004 sec)

위의 address 테이블 정의에 city_id를 포함하고 있으므로 고객과 주소 테이블을 연결한 후, city_id를 이용하여 주소와 도시 테이블을 연결하면 원하는 결과를 얻을 수 있습니다.

MariaDB [sakila]> SELECT c.first_name, c.last_name, ct.city
    -> FROM client c
    ->   INNER JOIN address a
    ->   ON c.address_id = a.address_id
    ->   INNER JOIN city ct
    ->   ON a.city_id = ct.city_id
+------------+-----------+----------------+
| first_name | last_name | city           |
+------------+-----------+----------------+
| MARY       | SMITH     | Sasebo         |
| PATRICIA   | JOHNSON   | San Bernardino |
| LINDA      | WILLIAMS  | Athenai        |
| BARBARA    | JONES     | Myingyan       |
| ELIZABETH  | BROWN     | Nantou         |
...
| TERRENCE   | GUNDERSON | Jinzhou        |
| ENRIQUE    | FORSYTHE  | Patras         |
| FREDDIE    | DUGGAN    | Sullana        |
| WADE       | DELVALLE  | Lausanne       |
| AUSTIN     | CINTRON   | Tieli          |
+-------------+--------------+------------+
599 rows in set (0.003 sec)

이 때, SQL은 비절차적 언어이므로 client/address/city 중 어느 테이블이 from 절에서 먼저 나타나더라도 쿼리의 결과는 같습니다. 만약 순서가 중요할 경우 아래와 같이 straight_join 키워드를 사용하면 됩니다.

/* city 테이블을 드라이빙 테이블로 사용 */
SELECT STRAIGHT_JOIN c.first_name, c.last_name, ct.city
FROM city ct
  INNER JOIN address a
  ON a.city_id = ct.city_id
  INNER JOIN client c
  ON c.address_id = a.address_id

서브쿼리 사용

캘리포니아에 있는 모든 주소(address테이블의 district 값이 California)인 고객 정보만 얻기 위해서는 앞의 절에서 사용한 쿼리에 WHERE a.district = 'California' 절을 추가해도 되지만 아래처럼 서브쿼리를 활용하면 성능 및 가독성 측면에서 유리할 수 있습니다.

MariaDB [sakila]> SELECT c.first_name, c.last_name, addr.address, addr.city
    -> FROM client c
    ->   INNER JOIN
    ->   (SELECT a.address_id, a.address, ct.city
    ->    FROM address a
    ->      INNER JOIN city ct
    ->      ON a.city_id = ct.city_id
    ->    WHERE a.district = 'California'
    ->   ) addr
    ->   ON c.address_id = addr.address_id;
+------------+-----------+------------------------+----------------+
| first_name | last_name | address                | city           |
+------------+-----------+------------------------+----------------+
| PATRICIA   | JOHNSON   | 1121 Loja Avenue       | San Bernardino |
| BETTY      | WHITE     | 770 Bydgoszcz Avenue   | Citrus Heights |
| ALICE      | STEWART   | 1135 Izumisano Parkway | Fontana        |
| ROSA       | REYNOLDS  | 793 Cam Ranh Avenue    | Lancaster      |
| RENEE      | LANE      | 533 al-Ayn Boulevard   | Compton        |
| KRISTIN    | JOHNSTON  | 226 Brest Manor        | Sunnyvale      |
| CASSANDRA  | WALTERS   | 920 Kumbakonam Loop    | Salinas        |
| JACOB      | LANCE     | 1866 al-Qatif Avenue   | El Monte       |
| RENE       | MCALISTER | 1895 Zhezqazghan Drive | Garden Grove   |
+------------+-----------+------------------------+----------------+
9 rows in set (0.003 sec)

테이블 재사용

영화를 나타내는 film 테이블, 배우를 나타내는 actor 테이블, 그리고 특정 영화에 나온 배우를 나타내는 film_actor 테이블이 있다고 했을 때, 두 명의 배우가 모두 출연한 영화만 검색하려면 어떻게 해야 할까요? 이를 위해서는 서버가 각각의 절에서 참조하는 항목을 알 수 있도록 film_actor 및 actor 테이블을 두 번 포함하는 형태로 쿼리를 작성해야 합니다.

MariaDB [sakila]> SELECT f.title FROM film f
    -> INNER JOIN film_actor fa1
    -> ON f.film_id = fa1.film_id
    -> INNER JOIN actor a1
    -> ON fa1.actor_id = a1.actor_id
    -> INNER JOIN film_actor fa2
    -> ON f.film_id = fa2.film_id
    -> INNER JOIN actor a2
    -> ON fa2.actor_id = a2.actor_id
    -> WHERE (a1.first_name = 'CATE' AND a1.last_name = 'MCQUEEN')
    ->   AND (a2.first_name = 'CUBA' AND a2.last_name = 'BIRCH');
+------------------+
| title            |
+------------------+
| BLOOD ARGONAUTS  |
| TOWERS HURRICANE |
+------------------+
2 rows in set (0.003 sec)

셀프 조인

자기 참조 외래 키가 존재하는 테이블의 경우는 테이블을 자기 자신과 조인할 필요가 있습니다. 이 때 실행하는 조인을 셀프 조인이라고 합니다.

참고

이 글 및 이와 관련된 앞으로의 글의 모든 내용은 한빛미디어의 러닝 SQL의 내용을 공부하며 요약한 것입니다.

김동환

개발을 통해 다양한 세상을 경험하는 것을 즐기는 개발자입니다.