[sqlite3] JOIN으로 서로 다른 테이블의 컬럼들 붙이기(내부 조인, 외부 조인)

우리는 SELECT 문을 이용할 때 보통 하나의 테이블에서 어떤 데이터를 조회합니다. 그런데 어떤 경우에는 두 개의 테이블을 결합시켜서 읽는 것이 좀 더 정보성(informative)이 있습니다. 왜냐하면 하나의 테이블만으로는 충분한 정보를 얻기 힘들 때가 많기 때문입니다.

 

다음과 같은 두 개의 테이블이 있다고 가정하겠습니다. 첫번째 테이블에는 선수의 아이디와 이름, 그리고 소속된 팀의 아이디가 담겨 있습니다. 그리고 두번째 테이블에는 팀의 아이디와 이름이 담겨 있습니다. 

 

player 테이블

 

team 테이블

 

참고로 첫번째 테이블과 두번째 테이블은 각각 다음과 같은 쿼리로 생성했습니다. 

 

sqlite> CREATE TABLE player (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT, team_id INT);

sqlite> CREATE TABLE team (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT);

 

첫번째 테이블만 조회해서는 추신수 선수가 무슨 팀 소속인지 알 수가 없습니다. team_id가 9라고 하는데 9가 어떤 팀을 의미하는지 알 수가 없기 때문입니다. 두번째 테이블도 참고해야만, team_id 9가 SSG 팀을 의미하는 것을 알 수 있습니다. 즉, 첫번째 테이블에서 team_id라는 열이 두번째 테이블의 id와 매칭되는 것입니다. 

 

이런 경우에는 선수 이름과 소속된 팀 이름이 하나의 테이블의 형태로 보이도록 조회하는 것이 좋을 것입니다. 그것을 가능하게 하는 것이 바로 JOIN입니다. SQLite에는 세가지 유형의 JOIN이 있습니다.

 

1) INNER JOIN, 간단히 JOIN이라고 부르기도 함

2) LEFT OUTER JOIN, 간단히 LEFT JOIN으로 부르기도 함

3) CROSS JOIN

 

자주 사용되는 빈도로 따지면, INNER JOIN > LEFT OUTER JOIN > CROSS JOIN 순이라고 보시면 됩니다. 

 

INNER JOIN

sqlite> SELECT player.id, player.name, player.team_id, team.id, team.name FROM player INNER JOIN team ON player.team_id=team.id;

 

SELECT 테이블명.컬럼명, 테이블명.컬럼명,...,테이블명.컬럼명 FROM 테이블명1

INNER JOIN 테이블명2

ON 테이블명1.컬럼명1 = 테이블명2.컬럼명2

 

위와 같은 쿼리를 주면 다음과 같은 내용이 조회됩니다. 

 

 

이제는 각 선수의 소속팀을 한 눈에 알 수 있게 되었습니다. 그런데 첫번째 테이블에서 team_id 값이 없었던 심교훈 선수의 정보는 누락되었습니다. 이렇게 INNER JOIN은 비교하는 두 컬럼의 값이 일치하지 않는 경우에는 그 행을 무시합니다.

 

여기서 team_id 컬럼과 id 컬럼을 안보이게 해주면 좀 더 보기 좋아지겠네요. 

 

sqlite> SELECT player.id, player.name, team.name FROM player INNER JOIN team ON player.team_id=team.id;

 

 

군더더기 없어졌죠?

 

만약 수도권에 있는 팀에서 뛰고 있는 선수들에 대해서만 조회를 하고 싶다면 다음과 같이 조건을 추가해주면 됩니다. 

 

sqlite> SELECT player.id, player.name, team.name FROM player INNER JOIN team ON player.team_id=team.id WHERE team.name IN ('두산', 'KT', 'LG', '키움', 'SSG');

 

 

LEFT OUTER JOIN

만약 소속된 팀이 없어서 매칭되지 않았던 심교훈 선수의 관한 행도 함께 보여주고 싶다면 LEFT OUTER JOIN을 사용하면 됩니다. 

 

sqlite> SELECT player.id, player.name, player.team_id, team.id, team.name FROM player LEFT OUTER JOIN team ON player.team_id=team.id;

 

SELECT 테이블명.컬럼명, 테이블명.컬럼명,...,테이블명.컬럼명 FROM 테이블명1

LEFT OUTER JOIN 테이블명2

ON 테이블명1.컬럼명1 = 테이블명2.컬럼명2

 

바뀐 것은 INNER JOIN이 LEFT OUTER JOIN이 된 것 밖에 없습니다. 위 쿼리를 주면 다음과 같은 내용이 조회됩니다.

 

 

이번에는 잠실에 있는 두 팀에서 뛰고 있는 선수들만 조회해보겠습니다.

 

sqlite> SELECT player.id, player.name, team.name FROM player LEFT OUTER JOIN team ON player.team_id=team.id WHERE team.name IN ('두산', 'LG');

 

 

CROSS JOIN

cross join은 두 개의 테이블로 나올 수 있는 모든 경우의 수를 보여준다고 생각하시면 됩니다.

 

sqlite> SELECT player.name, team.name FROM player CROSS JOIN team;

 

SELECT 테이블명.컬럼명, 테이블명.컬럼명,...,테이블명.컬럼명 FROM 테이블명1

CROSS JOIN 테이블명2

 

 

CROSS JOIN은 이런 식으로 두 테이블의 행들을 조합해서 나올 수 있는 모든 경우의 수들을 보여줍니다. 

 

(이 글은 2021-10-17에 마지막으로 수정되었습니다)

 

참고자료

[1] araikuma.tistory.com/724, devkuma, "[SQLite] 데이터 조인(Join) - 내부 조인 (INNER JOIN 절)"

[2] https://www.techonthenet.com/sqlite/joins.php, TechOnTheNet, "SQLite: Joins"