1.4Joins

Joins와 Aggregation을 활용한 쿼리

복잡한 데이터 조회를 하려면 테이블 간 관계를 잘 활용해야 한다. 이번 포스팅에서는 JOIN 관련 부분을 살펴보고, 다음 포스팅에서 AGGREGATION을 살펴보자.

1. Join

서로 관련된 여러 테이블의 데이터를 합쳐서 조회할 때 사용한다.

서로 연결된 테이블들 사이에서 데이터를 병합해 새로운 결과를 만든다.
어떤 데이터를 조회할 때, 하나 이상의 테이블에서 정보가 필요하다면 대부분 JOIN을 써야 한다.

2. Aggregation (집계 함수)

여러 개의 행(row)을 대상으로 하나의 값을 계산할 때 사용한다.

'가장 많은', '평균', '총합' 같은 단어가 들어가는 값을 조회하려고 하면 Aggregation을 써야 할 가능성이 높다.
- 예: 가장 많은 사진을 올린 유저 찾기, 평균 댓글 수 계산 등

이번 예시에선 users, photos, comments 테이블로 구성된 사례로 JOIN 구문을 살펴보자:

sql
CREATE TABLE users(
  id SERIAL PRIMARY KEY,
  username VARCHAR(50)
);
 
CREATE TABLE photos (
  id SERIAL PRIMARY KEY,
  url VARCHAR(200),
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);
 
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  contents VARCHAR(240),
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  photo_id INTEGER REFERENCES photos(id) ON DELETE CASCADE
);

서로 다른 테이블의 데이터 JOIN 하기

1. 각 댓글 내용과 해당 댓글을 작성한 유저명 조회

sql
SELECT contents, username
FROM comments
JOIN users ON users.id = comments.user_id;

contents와 username이 필요한데, username은 comments의 user_id를 users의 id와 연결하며 얻을 수 있다.
comments 테이블을 옆에 이어 붙여 크게 늘린다고 생각하면(comments with users), username이라는 column을 추가하고 username에는 user_id를 이용해 users 테이블의 id와 결합해 가져온다.

2. 각 댓글 내용과 해당 댓글이 달린 사진의 URL 조회

sql
SELECT contents, url
FROM comments
JOIN photos ON photos.id = comments.photo_id;

comments에는 photo_id가 있으므로 photos 테이블과 연결해서 URL을 조회한다.

JOIN 관련 팁들

FROM과 JOIN의 순서는 결과에 영향을 종종 미친다.
- 위의 예시에서 contents와 url를 바꾸는건 상관이 없지만,
- 예를들어 url과 username을 선택하는 등 일대다 관계에서 photos with users로 photo를 메인으로 하는 것과, user with photo로 user를 메인으로 하는 것은 다른 결과를 불러옴
두 테이블에 같은 이름의 column이 있다면 합쳤을 때 collision(ambiguous)이 발생하므로 명시적으로 테이블명.컬럼명 형식으로 context를 줘야한다.
AS 키워드로 테이블이나 컬럼에 별칭(alias)를 줄 수 있다.

sql
SELECT comments.id AS comment_id, p.id
FROM photos AS p
JOIN comments ON p.id = comments.photo_id;

다양한 종류의 JOIN

1. Inner Join

INNER JOIN 또는 그냥 JOIN으로 쓴다.

sql
SELECT ...
FROM A
JOIN B ON ...

FROM 테이블과 JOIN 테이블에서 연결 조건이 만족되는 행만 조회된다.

2. Left Outer Join

LEFT JOIN으로 쓴다.

sql
SELECT ...
FROM A
LEFT JOIN B ON ...

FROM 테이블의 모든 데이터를 가져오고,
JOIN 테이블의 데이터가 없으면 NULL로 채운다.

3. Right Outer Join

RIGHT JOIN으로 쓴다.

sql
SELECT ...
FROM A
RIGHT JOIN B ON ...

JOIN 테이블에서 모든걸 가져오고, FROM 테이블에 없는 데이터는 NULL로 채운다.

4. Full Join

FULL JOIN으로 쓴다.

sql
SELECT ...
FROM A
FULL JOIN B ON ...

양쪽 테이블의 모든 행을 가져오고, 없는 값은 NULL로 채운다.
- 즉, 데이터가 없어도 다 합친다.

예를 들어, 모든 사진 url과 해당되는 유저 이름을 표시하는 케이스를 살펴보자. 이럴 때는 Left Outer Join을 써야 모든 사진을 조회할 수 있다.

sql
SELECT url, username
FROM photos
LEFT JOIN users ON users.id = photos.user_id;

JOIN + WHERE

유저들이 자신이 올린 사진에 커멘트를 달 수 있을 때, 자기 자신에 댓글을 단 경우만 조회하기

sql
SELECT url, contents
FROM comments
JOIN photos ON photos.id = comments.photo_id
WHERE comments.user_id = photos.user_id

contents와 photos를 합치고, comments.user_id와 photos.user_id인 경우로 필터링한다.

세 테이블 JOIN하기(Three-Way Join)

자기 사진에 커멘트를 단 유저들의 사진 url과 커멘트 내용, 그리고 유저명을 조회하기

sql
SELECT url, contents, username
FROM comments
JOIN photos ON photos.id = comments.photo_id
JOIN users ON users.id = comments.user_id 
AND users.id = photos.user_id