(SQLite3) JOIN을 통한 테이블 연결

지난 글에서 SQLite3에서 SELECT 명령의 사용방법에 대해서 살펴보았는데, FROM을 통해서 단일 테이블 혹은 단일 테이블 내의 범위를 부분적으로 얻어내는 서브 쿼리를 통해서 보다 정교한 범위의 데이터를 얻고, 또 WHERE절을 사용해서 결과를 필터링 하는 방법에 대해서도 살펴보았다. 그외에 GROUP BY나 그외 aggregation 연산을 통한 쿼리 방법에 대해서는 자세하게 다루지 않았는데, 그 전에 JOIN에 대해서 간단하게 짚고 넘어가고자 한다.

JOIN 이란 무엇인가

JOIN은 간단히 정의하자면 “두 개 이상의 그리드를 (가로 방향으로) 연결해서 하나의 그리드처럼 만드는” 것이다. 테이블은 말 그대로 표이며, JOIN은 두 개의 표를 옆으로 이어붙이는 것이라 이해하면 된다. 하지만 표가 그려진 두 장의 종이를 나란히 둔다고 해서 표가 결합하는 것은 아니다. 논리적으로 두 개의 표를 옆으로 이어붙이기 위해서는, 두 표가 이치에 맞게 연결되도록 하는 일종의 ‘접합점’이 필요하다. 이는 각 표의 행들이 연결될 수 있어야 함을 의미하는데, 보통은 공통적으로 가지고 있는 필드값을 그 접점으로 하게 된다.

예를 들어 발매된 음반의 정보를 담고 있는 albums라는 테이블과 수록곡이 등재된 tracks라는 테이블이 있다고 가정하자. tracks 테이블에는 각각의 트랙이 수록된 앨범 정보를 나타내는 album_id 라는 칼럼이 있고, 이 칼럼은 albums 테이블의 id 칼럼을 참조하는 외래 키(foreign key)로 설정되어 있다고 하자. (물론 JOIN을 위해서 꼭 외래키로 지정되어 있을 필요는 없다. 그저 공통된 값이 있으면 된다.) 그렇다면 tracks 테이블의 album_id 칼럼과 albums 테이블의 id 칼럼을 접합점으로 한다면, 두 테이블을 연결할 수 있다. 이렇게 연결된 테이블을 사용하면 특정한 노래에 대해서 노래가 수록된 앨범의 제목이나 앨범의 발매 년도 등, 앨범 관련한 정보를 함께 망라한 표를 얻게 된다.

SQLite3는 세 가지 조인 방식을 지원하고 있는데, INNER JOIN, LEFT JOIN, CROSS JOIN 이 그것이다. 보다 큰 규모의 다른 DBMS에서는 RIGHT JOIN이나 OUTER JOIN, FULL OUTER JOIN 등의 옵션도 지원하는 경우가 있다. 여기서는 SQLite3의 JOIN 방식에 대해서 알아보자.

JOIN의 일반적인 문법은 다음과 같다.

SELECT {col1, col2, alias1.col3...} FROM {tab1} AS {alias1}
{INNER/LEFT/CROSS} JOIN {tab2} as {alias2} ON {결합조건}

-- ON 대신 USING을 쓸 수 있다.
{INNER/LEFT/CROSS} JOIN USING {결합칼럼}

JOIN은 두 표를 ‘옆으로 ‘ 이어 붙이는 연산임을 잊지 말자.

  • FROM 절에 쓰이는 테이블 명이 왼쪽에 오는 그리드가 된다. as 를 통해서 별칭을 붙여줄 수 있다.
  • JOIN의 타입과 오른쪽에 올 그리드를 지정한다. 역시 테이블 명 뒤에는 as를 통해서 별칭을 붙여줄 수 있다.
  • 두 테이블이 접합에 사용하는 칼럼 외에도 같은 이름의 칼럼을 가지고 있다면 테이블명.칼럼명 이나 별칭.칼럼명의 형태로 지정할 수 있다.
  • 통상 특정 칼럼의 값이 같아야 하니 ON table1.colA = table2.colB 와 같은 식으로 조건을 정의한다.
  • 만약 두 테이블의 공통 칼럼이 이름이 같고, 그 칼럼의 값이 같은 행끼리 연결한다면 USING (col) 로 축약해서 쓸 수 있다.

JOIN으로 연결한 결과는 하나의 테이블처럼 보게되며, 따라서 SELECT 문의 WHERE, ORDER BY LIMIT 구문에서 연결된 그리드로서 조작하면 된다.

예를 들어 각 노래의 제목과 그 노래가 수록된 앨범명을 보고 싶다면 다음과 같이 쿼리를 작성할 수 있다.

아래 쿼리는 https://www.sqlitetutorial.net/tryit/ 에서 테스트 해 볼 수 있다.

SELECT name, title FROM tracks 
-- name은 tracks에서 곡 제목을, 
-- title은 albums에서 앨범 제목을 나타내는 칼럼이다.
INNER JOIN albums ON tracks.almubid = albums.albumid;

다중 조인 적용하기

JOIN은 그리드를 왼쪽에서 오른쪽으로 붙여나간다고 하였다. 따라서 필요하다면 아래와 같이 JOIN 절을 계속 이어 쓰는 방식으로 2개, 3개, 4개의 테이블을 연이어 조인할 수 있다.

-- 각 트랙 제목과 이 곡이 수록된 앨범명, 아티스트 명을 출력한다.
-- 트랙정보는 tracks에, 앨범은 albumbs에, 아티스트는 artists에 있으므로
-- 세 테이블을 모두 조인해야 한다.
SELECT tracks.name, title, artists.name
FROM tracks
INNER JOIN albums USING (albumid)
INNER JOIN artists USING (artistid)
WHERE trackid < 200;

INNER JOIN

INNER JOIN은 가장 흔하게 쓰이는 JOIN 방식으로 두 개의 테이블이 공통된 칼럼을 가지고 있고, 해당 칼럼의 값이 같은 레코드끼리 연결한다. 이 때 두 그리드가 연결된 결과는 맞물리는 칼럼에 대한 교집합과 비슷하다. 만약 트랙 테이블의 데이터 중 앨범값이 앨범 테이블에 없거나, 반대로 앨범 테이블에는 있지만, 그 수록곡이 트랙 테이블에 없다면 이런 데이터들은 결과에서 제외된다.

LEFT JOIN

INNER JOIN에서는 접합 칼럼을 기준으로 조건에 맞는 행이 없는 경우는 모두 탈락시킨다고 하였다. LEFT JOIN은 말 그대로 접합부의 왼쪽 그리드를 기준으로 삼는 JOIN 방식이다. 따라서 INNER JOIN과는 달리 일치하는 값이 없는 경우라도 접합점의 왼쪽에 있는 테이블의 행은 알 수 없는 값을 모두 NULL로 채워서 결과를 만든다. 따라서 기준이 되는 테이블에서는 누락되는 행 없이 결과를 조회할 수 있다.

음반 관련 데이터베이스에는 artists라는 테이블이 있다. 여기에는 앨범을 발매한 가수 혹은 작곡가로 참여한 사람의 고유 식별자와 이름이 기재된다. 이 때 전업 작곡가의 경우에는 artists 테이블에는 이름이 기록되지만, 발매한 앨범이 없기 때문에 INNER JOIN으로 albums 테이블과 조인하면 이들의 이름이 포함되지 않는다. 사람의 이름을 기준으로 앨범 발매 여부와 상관없이 앨범 타이틀과 아티스트명을 조회하려면 다음과 같이 한다.

-- 아티스트와 그 아티스트가 발매한 앨범
SELECT name, title FROM artists
LEFT JOIN albums ON artists.artistid = albums.artistid;

두 테이블 A와 B 가 있을 때, INNER JOIN의 경우 어느쪽을 왼쪽으로 (FROM 에 쓸 것인지)는 결과에 영향을 주지 않는다. 어차피 접합점이 되는 칼럼을 기준으로 양쪽 모두 데이터가 있어야 결과에 포함되기 때문이다. 하지만 LEFT JOIN은 왼쪽에 위치하는 테이블을 기준으로 결과의 행이 정의된다. 왼쪽에만 있는 행은 유지, 오른쪽에만 있는 행은 삭제된다.

SELECT count(*) FROM albums
LEFT JOIN artists USING (artistid);  -- 347

SELECT count(*) FROM artists
LEFT JOIN albums USING (artistid); -- 418

LEFT JOIN의 다중적용

INNER JOIN은 양쪽에 모두 데이터가 있는 행들만 추려내기 때문에, 3개 이상의 테이블을 LEFT 조인하면 가장 왼쪽에 있는 테이블의 행들만 유지된다.

테이블 A, B, C가 있을 때 A와 B는 INNER JOIN으로 연결하고, 다시 C를 LEFT JOIN으로 연결하면 먼저 A와 B에 공통적으로 데이터가 있는 행들로 (즉 교집합으로) 테이블의 ‘왼쪽’이 만들어진다. 여기에 C를 LEFT JOIN으로 연결하면 A⋂B에 존재하는 행만을 기준으로 C가 연결된다. (이 시점에선 C와 A에 공통으로 있는 행이 무시될 수 밖에 없다.) 따라서 JOIN 방식이 혼합되는 경우, JOIN의 순서가 결과에 영향을 미칠 수 있다는 것이다.

또 한가지 LEFT JOIN에서 주의할 것은 LEFT JOIN은 1:1로 행이 결합하는 것이 아니라 1:N으로 결합한다. 사실 이것은 모든 JOIN에서의 결합 방법이다. 데이터베이스에서 테이블의 조인은 각 테이블의 행들의 데카르트 곱에서 시작한다. 즉 A의 모든 행에 대해서 B의 모든행에 대한 매치가 만들어지고, 그 중에서 조건에 맞는 행들이 선택된다.

예를 들어 왼쪽 테이블 A의 값에 a, b, c, d 가 있고 오른쪽 테이블 B에 a1, a2, b1, b2, d1, d2, e1, e2가 있다고 가정하자. 그러면 이 둘의 LEFT JOIN 결과는 기본적으로 4 x 10 개행을 가진 테이블로 뻥튀기 된다. 그 중 a-a1, a-a2가 올바른 매치이고, b-b1, b-b2와 같이 매치가 가능한 값들은 모두 결과에 포함된다. c는 올바른 연결될 오른쪽 행이 없으므로 c-NULL 로 1개의 행이 만들어진다. e1, e2는 연결 가능한 행이 만들어질 수 없으므로 결과에 포함되지 않고 무시된다.

따라서 아티스트, 앨범, 수록곡의 테이블에 대해서 아티스트별, 앨범과 그 수록곡을 조회하기 위해서는 아티스트 -> 앨범 -> 수록곡의 순서대로 LEFT JOIN 한다. 아티스트를 찾지 못한 앨범이나, 앨범을 찾지 못한 트랙들은 제외되겠지만, 아티스트만 있는 앨범은 트랙이 NULL인 상태로 1개 레코드로 추가될 것이다.

-- 아티스트의 각 앨범과 앨범 수록곡의 수
SELECT a.name, title, b.name
  FROM artists AS a
  LEFT JOIN albums USING (artistid)
  LEFT JOIN tracks AS b USING (albumid)
ORDER BY a.name, title, trackid

SELF JOIN

SELF JOIN은 JOIN의 한 형태라기 보다는, 그저 테이블 자기 자신을 JOIN하는 경우이다. 이는 레코드의 특정한 필드가 같은 테이블의 다른 레코드를 참조하는 경우, 조회 결과에서 참조되는 데이터의 값을 표현하도록 확장하는데 사용될 수 있다.

예제 데이터베이스 파일의 employees 테이블은 직원 정보를 담고 있는데, 그 사람의 직속 상관이 누구인지를 알려주는 reportsto필드가 있다. 이 때 어떤 직원의 직속 상사 이름을 결과에 바로 표기하기 위해서는 그 자신을 join하여 표시하면 된다.

-- 각 직원의 풀네임과 그 직속 상관의 풀네임
SELECT a.firstname || ' ' || a.lastname AS fullname,
       b.firstname || ' ' || b.lastname AS directReportsTo
FROM employees a
INNER JOIN employees b ON a.reportsto = b.employeeid
;

참고로 이름이 같은 두 개의 테이블이 하나의 쿼리에 쓰이는 경우, 이름을 서로 구분하기 위해서 별칭을 붙이게 된다. SQLite3에서는 별칭을 만들 때 as 를 써도 되고 쓰지 않아도 된다. (일부 데이터베이스 엔진들은 as를 허용하지 않는 경우도 있으니 유의하자.)

참고로 위 예제에서는 INNER 조인을 사용했다. 그런데 어떤 누군가(사장님)는 직속 상관이 없을 것이다. 따라서 LEFT 조인을 사용한다면, 직속 상관이 없는 그 사람도 포함될 수 있다.

CROSS JOIN

CROSS JOIN은 왜 있는지는 잘 모르겠는데, 접합 조건이 없는 결합방법이다. 앞서 테이블을 조인할 때에는 왼쪽테이블의 모든행과 오른쪽 테이블의 모든행을 각각 조합한 데카르트 곱에서 출발한다고 했다. CROSS JOIN은 두 테이블의 데카르트 곱을 만드는 조인이다. 테이블 A에 1, 2, 3의 값이 있고, 테이블 B에 7, 8, 9 의 값이 있다면 크로스 조인은 (1, 7), (1, 8), (1, 9), (2, 7), (2, 8), (2, 9), (3, 7), (3, 8), (3, 9)의 조합을 만들어낸다.

사실 SELECT * FROM A, B;와 똑같은 기능을 수행한다.