(연재) SQLite3 강좌 – 테이블에서 조회하기 2

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

JOIN 이란 무엇인가

JOIN은 간단히 정의하자면 “두 개 이상의 그리드를 연결해서 하나의 그리드처럼 만드는” 것이다. 테이블 하나를 하나의 그리드라고 보면 두 개의 테이블을 옆으로 이어붙이는 것이라 이해하면 된다. 그런데 무턱대고 두 개의 그리드를 이어붙일 수는 없다. (심지어 두 그리드는 행의 개수가 다를 수도 있다) 논리적으로 두 개의 그리드를 옆으로 이어붙이기 위해서는 하나의 ‘접합점’이 필요하다. 이 접합점은 바로 두 테이블에서 같은 값들이 들어가는 공통된 칼럼이다.

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

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

INNER JOIN

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

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

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

JOIN으로 연결한 결과는 하나의 그리드로 보게되며, 따라서 SELECT 문의 WHERE, ORDER BY LIMIT, ㅌ을 그대로 사용할 수 있다. 예를 들어 각 노래의 제목과 그 노래가 수록된 앨범명을 보고 싶다면 다음과 같이 쿼리를 작성할 수 있다.

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

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

각 앨범의 수록곡 개수를 세고 싶다면 albumid 별로 그룹핑해서 각 트랙의 개수를 세면 된다.

-- 앨범별 수록곡 개수
SELECT title, count(name) FROM tracks
INNER JOIN albums USING (albumid)
GROUP BY albumid
ORDER BY title;

JOIN은 그리드를 왼쪽에서 오른쪽으로 붙여나간다고 하였다. 따라서 2개, 3개, 4개의 테이블을 연이어 조인할 수 있다.

-- 각 트랙과 수록된 앨범, 아티스트 명을 출력한다.
SELECT tracks.name, title, artists.name
FROM tracks
INNER JOIN albums USING (albumid)
INNER JOIN artists USING (artistid)
WHERE trackid < 200; -- 결과가 너무 많아져서 붙임

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의 경우 A를 기준으로 B를 접합하는 경우와 B를 기준으로 A를 접합하는 경우의 쿼리는 다르지만 그 결과는 동일하다. 어차피 접합점이 되는 칼럼을 기준으로 결과가 생성되기 때문이다. 따라서 INNER JOIN의 경우 양쪽 테이블 위치에 상관없이 결과 자체는 대칭이라 할 수 있다. 하지만 LEFT JOIN은 왼쪽에 위치하는 테이블을 기준으로 행이 정의되기 때문에 위 쿼리에서 albums를 FROM에 쓴다면 결과가 달라질 것이다.

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

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

참고로 3개 이상의 테이블을 연결하는 것은 앞에서부터 연결된 결과 그리드에 뒤의 테이블을 누적해서 더하는 개념이며 개별 테이블이 더해지는 개념이 아니다. 다음 쿼리는 아티스트와 그 발매 앨범, 앨범별 수록곡의 수를 쿼리하는데, 두 개의 LEFT JOIN이 들어간다. artists – albums – tracks가 연결되는데, albums와 tracks는 albums.albumid = tracks.albumid 로 연결되는데,  (A + B ) + (A + C) 의 개념이 아니라 (A + B) + C 와 같은 식으로 테이블이 합쳐진다고 볼 수 있다.

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

SELF JOIN

특정한 조건에 해당하는 행을 찾기 위해서 가끔 어떤 테이블과 그 테이블 자신을 JOIN 하는 경우가 있다. 이것을 셀프 조인이라고 하는데, 실제로 많이 쓰이는 케이스이다. 예를 들어 employees라는 테이블을 생각해보자. 여기에는 각 직원의 이름과 성, 그리고 이 직원의 직속상사(보고를 받는 사람)가 누구인지에 대한 정보를 가지고 있다고 하자. 이 때 각 직원의 이름과 그 직속상사의 이름을 짝지은 결과를 조회하기 위한 쿼리를 보자. 참고로 두 개의 문자열을 연결하기 위해서는 || 연산자를 사용할 수 있다.

-- 각 직원의 풀네임과 그 직속 상관의 풀네임
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
;

JOIN에서는 그저 두 개의 그리드가 횡방향으로 연결되는 것이기 때문에 셀프 조인은 특별한 것이 아니다. 다만 두 개의 테이블 이름이 똑같기 때문에 혼동을 피하기 위해서 두 개의 그리드 모두에 각각의 별칭을 붙여서 사용한다. 별칭의 경우 as 를 써도 되고 쓰지 않아도 된다. (MySQL등 JOIN시 테이블 별칭을 쓸 때 as을 쓰지 않도록 강제하는 경우도 있으니 참고한다.)

참고로 위에서는 INNER JOIN을 썼는데, LEFT JOIN을 쓰면 어떻게 될까? reportsTo 칼럼이 NULL인 레코드가 검색될 수 있다. 그렇다면 이 사람은 누구에게도 보고하지 않아도되는, CEO가 될 것이다.

CROSS JOIN

CROSS JOIN은 극히 특별한 케이스에서 좀 유용할 수 있다. (사실 왜 특별하게 CROSS JOIN 이라고 이름이 붙었는지는 알 수 없는데…) 두 테이블 A, B에 대해서 JOIN이 수행될 때, ON 이나 USING 으로 한정하는 절이 없으면, 실질적으로 발생하는 데이터는 두 테이블의 데카르트 곱이다. 테이블 A가 m 개 행을 가지고 있고, 테이블 B가 n개 행을 가지고 있다면, 두 테이블의 데카트르 곱은 A의 1행이 B의 1 … n 의 모든 행에 대응되는 결과를 만들고, 다시 A의 2행이 B의 모든 행에 대응되고… 이런식으로 m * n 개의 행을 가진 모든 조합의 경우를 만들어 낼 수 있다. CROSS JOIN은 그 특성상 매우 큰 그리드를 생성하므로 다시 한 번 말하지만, 주의해서 사용해야 한다.

굳이 CROSS JOIN으로 명시하지 않더라도 INNER JOIN, LEFT JOIN을 접합 조건 없이 사용하거나, 단순히 FROM 에서 두 개의 테이블을 컴마로 나열하기만 해도 같은 효과를 얻을 수 있다.

이상으로 SQLite에서 여러 테이블을 조인하는 방법에 대해서 살펴보았다. 다시 한 번 정리하자면 JOIN은 두 개의 그리드를 옆으로 이어붙이는 용도로 사용하며, 두 개 이상의 그리드에 대해서 적용이 가능하다. 그 중에서 INNER JOIN과 LEFT JOIN이 많이 쓰이며, INNER JOIN은 두 그리드의 공통 칼럼 값을 기준으로 결과를 생성하고, LEFT JOIN은 두 그리드 중 왼쪽의 것을 기준으로 결과를 생성한다는 차이가 있다. JOIN을 사용하게 되면 각각의 테이블에 분산되어 있는 데이터를 하나로 묶어서 쿼리할 수 있고, JOIN으로 묶여진 결과는 하나의 큰 테이블처럼 생각할 수 있다.