postgreSQL JOIN

유튜브 채널 생활코딩의 JOIN 강의 영상들을 참고한 글입니다.

---

0. JOIN이란?

관계형 DB에서 여러개의 테이블에 분산된 정보를 묶어서 하나의 테이블로 만드는 것.

표를 만들다 보면 중복에 의한 비효율과 여러가지 문제들이 발생한다.

이 문제를 해결하기 위해서 정규화 과정을 통해 아래와 같이 테이블을 여러개로 분리시킨다.

 

모든 표(테이블)는 하나의 주제(테마)만 가져야한다

 

하나의 주제를 찾는게 쉽지는 않지만. 합쳤을 때 중복이 발생하고, 합쳐진 정보가 다른 테이블에서도 사용이 되는 등 어떤 징후가 보인다면 표를 분리시킬 필요가 있는 타이밍인 것이다.

 

그렇다고 이 표가 나쁜건 아니다. 사람이 보기에 편하니까.

반대로 표를 나눴을 때는 보는데에는 불편하지만 수정하기에는 편하다.

한쪽이 편하면 한쪽이 불편한 트레이드 오프가 있는 것.

그런데 이런 문제를 JOIN으로 해결할 수 있다.

---

이번엔 분리된 표들을 어떻게 JOIN으로 합치는지 알아보자.

JOIN은 상당히 다양한 종류가 있다.

 

유용한 사이트

SQL Joins Visualizer

 

1. LEFT JOIN (LEFT OUTER JOIN)

가장 많이 사용되는 JOIN

 

아래의 SQL로 만들어진 두 코드를 보자.

강의에서 mysql을 사용하는데 postgresql 문법으로 바꿨다.

그리고 topic테이블의 author_id가 varchar(45)로 되어있었는데 int가 맞는 것 같아서 int로 바꿨다.

DROP TABLE IF EXISTS  author ;
CREATE TABLE  author  (
   aid  int NOT NULL,
   name  varchar(10) DEFAULT NULL,
   city  varchar(10) DEFAULT NULL,
   profile_id  int DEFAULT NULL,
  PRIMARY KEY ( aid )
)  ;
INSERT INTO  author  VALUES (1,'egoing','seoul',1),(2,'leezche','jeju',2),(3,'blackdew','namhae',3);

DROP TABLE IF EXISTS  profile ;
CREATE TABLE  profile  (
   pid  int NOT NULL,
   title  varchar(10) DEFAULT NULL,
   description  text,
  PRIMARY KEY ( pid )
)  ;
INSERT INTO  profile  VALUES (1,'developer','developer is ...'),(2,'designer','designer is ..'),(3,'DBA','DBA is ...');

DROP TABLE IF EXISTS  topic ;
CREATE TABLE  topic  (
   tid  int NOT NULL,
   title  varchar(45) DEFAULT NULL,
   description  text,
   author_id  int DEFAULT NULL,
  PRIMARY KEY ( tid )
)  ;
INSERT INTO  topic  VALUES (1,'HTML','HTML is ...','1'),(2,'CSS','CSS is ...','2'),(3,'JavaScript','JavaScript is ..','1'),(4,'Database','Database is ...',NULL);

좌 topic 우 author 테이블

topic 테이블 4번째 항목의 author_id는 NULL이다.

그리고 author 테이블의 3번 항목은 topic 테이블에서 사용되지 않았다.

이런 경우에 LEFT JOIN을 하느냐 INNER JOIN을 하느냐 OUTER JOIN을 하느냐에 따라서 결과가 미묘하게 달라진다.

그 미묘함 때문에 여러가지 JOIN이 존재하는 것.

 

select * from topic left join author on topic.author_id = author.aid;

left join 결과

A(topic)의 모든 값이 나오고, 그 중 author_id가 B(author)의 aid 와 일치하는건 오른쪽에 덧붙여서 나온다. 일치하는게 없다면 NULL이 붙어서 나온다. 이게 LEFT JOIN.

 

2개의 표를 붙여봤는데, 끝에 profile_id가 있으니 만들어진 표에 또 profile 표도 붙여보자.

select * from topic
left join author on topic.author_id = author.aid
left join profile on author.profile_id = profile.pid;

잘 된다!

id같이 필요없고 중복되는걸 제거하기 위해 select * 부분을 바꿔주면 훨씬 더 깔끔하게 볼 수 있다. 컬럼의 이름이 겹치면 어떤 테이블의 컬럼인지 네임스페이스로 명시해줘야한다. 그리고 as를 사용해서 컬럼의 이름을 바꿔줄 수도 있다.

select tid, topic.title, topic.description, author.name as author
, author.city, profile.title as job_title, profile.description from topic
left join author on topic.author_id = author.aid
left join profile on author.profile_id = profile.pid;

그리고 이중에서 내가 egoing (author.aid = 1) 의 행만 가져오고싶다면 끝에 where aid = 1 만 덧붙여주면 된다.

select tid, topic.title, topic.description, author.name as author
, author.city, profile.title as job_title, profile.description from topic
left join author on topic.author_id = author.aid
left join profile on author.profile_id = profile.pid where aid = 1;

 

2. RIGHT JOIN (RIGHT OUTER JOIN)

동작 방법은 LEFT와 똑같은데 방향만 바뀐 JOIN

select * from topic right join author on topic.author_id = author.aid;

어느쪽이 NULL이 되느냐만 차이가 난다.

select tid, topic.title, topic.description, author.name as author
, author.city, profile.title as job_title, profile.description from topic
right join author on topic.author_id = author.aid
right join profile on author.profile_id = profile.pid where aid = 1;

 

3. JOIN (INNER JOIN)

INNER JOIN은 양쪽 다 존재하는 경우에만 출력된다. 널 행이 존재하지 않는다.

일반적으로 성능이 더 좋다.

select * from topic join author on topic.author_id = author.aid;

 

4. FULL OUTER JOIN

LEFT와 RIGHT를 합친 느낌의 JOIN (중복되는건 한번만 출력됨) 이런게 있긴 한데 거의 쓰이지 않는다고 한다.

mysql은 지원하지 않는 기능. postgresql에서는 지원한다. 지원하지 않는 DB에서는 아래와 같이 구현할 수 있다.

-- postgres
select * from topic full outer join author on topic.author_id = author.aid;
-- mysql
(select * from topic left join author on topic.author_id = author.aid)
UNION (select * from topic right join author on topic.author_id = author.aid);

 

5. EXCLUSIVE JOIN

특정 테이블에만 있는 행을 가져오는 JOIN. 마찬가지로 거의 쓰이지 않는다고 한다.

LEFT JOIN에서 where 조건으로 B에서 NULL인 부분만 가져오면 된다.

select * from topic left join author on topic.author_id = author.aid where author.aid is null;

 

6. EXCLUSIVE JOIN

강의에서 성능부분은 다루지 않았는데, JOIN의 목적은 똑같지만 실현 방식은 제품마다 다르다. 그래서 어떤 제품을 쓰느냐에 따라 성능을 높이기 위해 사용자가 신경써야 할 부분이 다를 수 있다.

성능이 느려질 때 JOIN이 의심된다면 explain 을 select 앞에 붙여서 병목지점을 확인해볼 수 있다.

 

그리고 강의에서 테이블 결합 방법은 알려줬지만, 어떻게 분리하면 좋을지는 알려주지 않았다. 이 부분은 정규화 를 공부하도록 하자.