sungyup's.

PostgreSQL / SQL Basics / 2.3 Joins

2.3Joins

Queries With Joins and Aggregations

  1. Joins
    1. Produces values by merging together rows from different related tables
    2. Use a join most times that you’re asked to find data that involves multiple resources
    3. 관계를 갖고 있는 여러 테이블들의 열들 모아야 할 때
  2. Aggregation
    1. Looks at many rows and calculates a single value
    2. Words like ‘most’, ‘average’, ‘least’ are a sign that you need to use an aggregation
    3. 여러 열들을 통해 값 계산할 때

이번 Chapter의 예시에선 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
);

Joining Data from Different Tables

  1. 각 커멘트 내용과 해당 커멘트를 친 유저명을 보이기
  • contents와 username이 필요한데, username은 comments의 user_id를 users의 id와 연결하며 얻을 수 있다.
  • comments 테이블을 크게 늘린다고 생각하면(comments with users), username이라는 column을 추가하고
    • username에는 user_id를 이용해 users 테이블의 id와 결합해 username을 반환한다.
sql
SELECT contents, username
FROM comments
JOIN users ON users.id = comments.user_id;
  1. 각 커멘트 내용과 해당 커멘트가 달린 사진 url을 보이기
sql
SELECT contents, url
FROM comments
JOIN photos ON photos.id = comments.photo_id;

Notes on Joins

  • FROM과 JOIN사이의 테이블 순서가 영향을 종종 미침
    • 위의 예시에서 contents와 url를 바꾸는건 상관이 없지만
    • 예를들어 url과 username을 선택하는 등 일대다 관계에서 photos with users로 photo를 메인으로 하는 것과, user with photo로 user를 메인으로 하는 것은 다른 결과를 불러옴
  • 두 테이블에 같은 이름의 column이 있다면, 합쳤을 때 collision(ambiguous)이 발생하므로 context를 줘야함.
    • table명.column명 이런 식으로
  • 테이블 이름은 AS 키워드로 바꿀 수 있음
    • 예를 들면 아래와 같이
sql
SELECT comments.id AS comment_id, p.id
FROM photos AS p
JOIN comments ON p.id = comments.photo_id;
  • Join에는 여러 종류가 있음
    • Inner Join : INNER JOIN 또는 그냥 JOIN으로 씀

      • FROM에 있는 테이블에서 필요한 연결 데이터만 JOIN 테이블에서 가져오고, JOIN 테이블과 엮이지 않는 FROM 테이블의 데이터는 가져오지 않음

    • Left Outer Join : LEFT JOIN으로 씀

      • FROM 테이블 전부를 가져오고, JOIN 테이블 중에선 FROM 테이블에 엮이는 데이터만 가져옴

    • Right Outer Join: RIGHT JOIN으로 씀

      • JOIN 테이블에서 모든걸 가져오고, FROM 테이블에 없는 데이터는 빈 값으로 채워둠

    • Full Join : FULL JOIN으로 씀

      • 없는 데이터가 있어도 그냥 양 테이블의 모든 데이터를 다 합침

  1. 모든 사진 url과 해당되는 유저 이름 표시

⇒ Left Outer Join을 써야 모든 사진을 포함

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

WHERE with Joins

  1. 유저들이 자신이 올린 사진에 커멘트를 달 수 있을 때, 사진 url과 그에 달린 커멘트들 조회

⇒ 커멘트에는 user_id와 photo_id가 있고, photos에는 user_id가 있으니 커멘트의 user_id와 photos의 user_id가 같은 필터를 걸어야함

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

Three Way Joins

  1. 자기 사진에 커멘트를 단 유저들의 사진 url과 커멘트 내용, 그리고 유저명

⇒ 커멘트에 user_id와 photo_id, contents가 있고 photos에 photo_id, url이 있고 users에 username이 있으니 3개의 테이블을 JOIN해야함

bash
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) 옆으로 해당되는 테이블들의 데이터를 연결해 붙인다고 생각할 것!

PreviousWorking with Tables
PostgreSQL
NextAggregation and Grouping
PostgreSQL