즐겁게 배우는 SQL 2. JOIN

본 포스트는 박재호님의 유튜브 강의(링크)를 보고 일부 발췌하여 정리한 내용입니다.
사용된 자료, 샘플 데이터 등은 모두 SQLite Tutorial에서 확인할 수 있습니다.

1. JOIN

join은 RDBMS에서 테이블 사이의 논리적 연결을 가능하게 만드는 핵심 기능이다.

위와 같은 테이블에서 앨범과 아티스트는 N:1 관계인 것을 알 수 있다.

여기서 앨범 이름과 아티스트를 함께 출력하려면 어떻게 해야 할까?
우선 SELECT 쿼리를 두 번 실행할 수 있을 것이다.
하지만 이 방법으로는 트랜잭션과 관련된 문제가 발생할 수 있으므로 추천되지는 않는다.
그래서 사용하는 것이 join인데, 이 join의 기본은 곱집합(cartesian product)을 활용한 것이다.

여기서 조건을 줌으로써 확장, 제약을 하여 left, right, inner join 등이 나오게 된다.

join을 학습할 때 중요한 것은, 집합이라는 개념(벤다이어그램을 사용한..)에 너무 얽매여 생각하지 말라는 것이다.
집합에서는 '무엇을 기준으로' 라는 개념이 없어서 깊이 데이터를 파고들수록 join을 이해하는데 오히려 방해가 될 수 있기 때문이다.

2. INNER JOIN

SELECT m, A.f, B.f, n
FROM A
INNER JOIN B on B.f = A.f;

위의 쿼리문만 봐서는 sql을 잘 모른다면 무엇을 뜻하는지 알기 어려울 수 있다.
그림으로 이를 나타내면 다음과 같다.

테이블A를 기준으로 테이블B와 join하는데 그 조건이 f컬럼의 값이 테이블A의 것과 같은 데이터만 가져오겠다는 뜻이다.

꼭 테이블이 두 개가 아니더라도, 그 이상의 경우에도 inner join이 가능하다.

위 테이블에서는 tracks는 albums와 N:1, albums는 artists와 N:1의 관계를 가지고 있다.
이 경우 다음처럼 albums 테이블을 join한 후 artists 테이블을 join해서 가수의 앨범과 그 안의 수록곡들을 가져올 수 있다.

SELECT trackid,
         tracks.name AS Track,
         albums.title AS Album,
         artists.name AS Artist
FROM tracks
INNER JOIN albums
    ON albums.albumid = tracks.albumid
INNER JOIN artists
    ON artists.artistid = albums.artistid;

또한 WHERE를 사용해 특정 가수의 앨범과 수록곡만을 가져올 수도 있다.

SELECT trackid,
         tracks.name AS Track,
         albums.title AS Album,
         artists.name AS Artist
FROM tracks
INNER JOIN albums
    ON albums.albumid = tracks.albumid
INNER JOIN artists
    ON artists.artistid = albums.artistid
WHERE artists.artistid = 10;

3. LEFT JOIN

SELECT
	a,
	b
FROM
	A
LEFT JOIN B ON A.f = B.f
WHERE search_condition;

left join을 그림으로 나타내면 다음과 같다.

결과를 보면 inner join과는 다른 점을 찾을 수 있을 것이다.
left join은 테이블A의 값들과 일치하는 값을 테이블B가 가지고 있지 않은 경우에는 NULL로 처리해서라도 가져온다는 점이 다르다.

실제 데이터를 가지고 살펴보자.

위 테이블 간의 관계에서, artists에는 데이터가 있는데 albums에는 데이터가 없는 경우는 존재하지만 그 반대 경우는 성립하지 않는다. (가수는 있지만 아직 앨범은 내지 않은 경우)
따라서 쿼리문을 수행할 때는 artists테이블을 기준으로 수행한다.

SELECT artists.ArtistId,
         albumId
FROM artists
LEFT JOIN albums
    ON albums.artistid = artists.artistid
ORDER BY  albumid;

4. CROSS JOIN

cross join의 경우 테이블A,B 사이에서 가능한 모든 경우를 다 가져오는 방법이다.
cross join 쿼리를 작성하는 방법은 여러가지가 있다.

SELECT *
FROM A JOIN B;

SELECT *
FROM A
INNER JOIN B;

SELECT *
FROM A
CROSS JOIN B;

SELECT * 
FROM A, B;

사실 cross join은 데이터가 많아질수록 가져오는 양이 기하급수적으로 늘어나기 때문에 잘 쓰이지는 않는다.

5. SELF JOIN

지금까지 했던 join은 두 개 이상의 테이블에서 Foreign Key와 다른 테이블의 Primary Key를 사용해 왔다.
그런데 한 테이블 안에서 계층적인 관계가 있을 때는 self join을 통해 자기참조로 데이터를 쿼리할 수도 있다.

위 테이블을 보면 employeeId가 있고 reportsTo가 있는데 reportsTo에는 직원이 보고해야 할 상사의 employeeId가 있을 것이다.
이 때 employees테이블에서 상사의 이름을 Manager필드, 보고하는 직원의 이름을 Direct report 필드로 하는 쿼리문은 다음과 같다.

SELECT m.firstname || ' ' || m.lastname AS 'Manager',
       e.firstname || ' ' || e.lastname AS 'Direct report' 
FROM employees e
INNER JOIN employees m ON m.employeeid = e.reportsto
ORDER BY manager;

위처럼 결과가 나오게 되는데, 이 때 생각해 볼 점이 하나 있다.
직원중 가장 높은 사람(CEO)는 어떻게 찾을 수 있을까?
CEO의 경우 자신이 보고할 사람이 없기 때문에 NULL이 나와야 한다. 하지만 inner join에서는 매칭되는 데이터만 가져오기 때문에 이 경우가 생략된 것이다.

따라서 정확한 데이터를 보려면 left join을 사용해야 한다.
left join을 사용한 결과는 다음과 같다.

이번에는 같은 도시에 사는 직원끼리 묶고자 한다면 어떻게 쿼리를 작성해야 할까?
이 경우에도 자기참조를 통해 inner join으로 도시가 같은 직원끼리 묶을 수 있다.

SELECT DISTINCT
	e1.city,
	e1.firstName || ' ' || e1.lastname AS fullname
FROM
	employees e1
INNER JOIN employees e2 ON e2.city = e1.city 
   AND (e1.firstname <> e2.firstname AND e1.lastname <> e2.lastname)
ORDER BY
	e1.city;

같은 도시끼리 묶여서 나온 것을 볼 수 있다.

6. FULL OUTER JOIN

full outer join은 두 테이블을 각각 left join, right join 한 후 중복되는 inner join을 한 번 뺀 결과이다.
그래서 테이블A의 모든 결과와 테이블B의 모든 데이터, A,B가 inner join 된 결과가 나오게 된다.

sqlite에서는 full outer join과 right join이 없어, left join을 한 번 하고 테이블 위치를 바꾸어 다시 left join을 한 것을 union all로 합쳐야 하는데 쿼리문은 다음과 같다.

SELECT d.type,
         d.color,
         c.type,
         c.color
FROM dogs d
LEFT JOIN cats c USING(color)
UNION ALL
SELECT d.type,
         d.color,
         c.type,
         c.color
FROM cats c
LEFT JOIN dogs d USING(color)
WHERE d.color IS NULL;

여기서 WHERE 절을 제거하면 inner join한 결과가 중복되어 나타난다.