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

⇒ 코드만 보면 상당히 복잡한데,

맨 왼쪽에 있는 기본 테이블(여기선 comments)의 오른쪽으로 필요한 데이터를 연결해 붙인다고 생각하면 이해가 쉬워진다.