[Re:Django] 2. JOIN 기본 개념

참고 사이트
JOIN의 종류설명 및 사용법 & 예제
[MySQL] 7장 조인 : JOIN (INNER, LEFT, RIGHT)
생활코딩 - SQL Join

JOIN의 필요성

관계형 데이터베이스에서는 중복 데이터를 피하기 위해서 데이터를 쪼개 여러 테이블로 나눠서 저장한다. 이렇게 분리되어 저장된 데이터에서 원하는 결과를 도출하기 위해서는 여러 테이블을 조합할 필요가 있다.

JOIN이란?

두 개 이상의 테이블이나 데이터베이스를 연결하여 데이터를 검색하는 방법. 자신이 검색하고 싶은 컬럼이 다른 테이블에 있을 경우 주로 사용하며 여러개의 테이블을 마치 하나의 테이블인 것처럼 활용하는 방법이다. 보통 Primary Key 혹은 Foreign key로 두 테이블을 연결한다. 테이블을 연결하려면 적어도 하나의 칼럼은 서로 공유되고 있어야한다.

참고 사이트
눈으로 보는 Join
SQL Joins Visualizer

이제부터 생활코딩 SQL Join에 나온 예시를 가지고 연습을 해보자.
먼저 데이터베이스를 만들어서 테이블들을 생성 및 데이터 삽입을 해놔야한다.
생활코딩에 나와있는대로 만들어보자.

DROP TABLE IF EXISTS `author`;
CREATE TABLE `author` (
  `aid` int(11) NOT NULL,
  `name` varchar(10) DEFAULT NULL,
  `city` varchar(10) DEFAULT NULL,
  `profile_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
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(11) NOT NULL,
  `title` varchar(10) DEFAULT NULL,
  `description` tinytext,
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
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(11) NOT NULL,
  `title` varchar(45) DEFAULT NULL,
  `description` tinytext,
  `author_id` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `topic` VALUES (1,'HTML','HTML is ...','1'),(2,'CSS','CSS is ...','2'),(3,'JavaScript','JavaScript is ..','1'),(4,'Database','Database is ...',NULL);

위에 나와있는대로 명령어를 실행하면 그림과 같은 형태의 테이블들이 생성된다.

LEFT OUTER JOIN, RIGHT OUTER JOIN

LEFT, RIGHT등의 OUTER JOIN은 기준이 되는 테이블이 무엇이냐에 따른다.
결과가 왼쪽 테이블 전체 데이터 대상이라면 LEFT, 오른쪽 테이블의 전체 데이터 대상이라면 RIGHT를 사용한다.
A LEFT JOIN B 와 B RIGHT JOIN A 는 완전히 같은 식이다.

LEFT OUTER JOIN

기준 테이블의 값 + 테이블과 기준테이블의 중복된 값 을 보여준다.
왼쪽 테이블을 기준으로 JOIN 하겠다고 생각하면 된다.
결과값은 A테이블의 모든 데이터와 A테이블과 B테이블의 중복되는 값이 검색된다.

--문법--
SELECT
테이블별칭.조회할칼럼,
테이블별칭.조회할칼럼
FROM 기준테이블 별칭
LEFT OUTER JOIN 조인테이블 별칭 ON 기준테이블별칭.기준키 = 조인테이블별칭.기준키 .....

#생활코딩 예시 및 설명
SELECT *
FROM topic
LEFT JOIN author
ON topic.author_id=author.aid

# topic 테이블을 갖고온다.
# topic 테이블을 왼쪽에 놓고 오른쪽에 author 테이블을 놓겠다.
# 그 때, 데이터베이스야~ topic의 author_id와 author 테이블의 aid의 값이 같을 때 두 개의 테이블을 하나로 만들어줘!

결과는 아래와 같다. 두 테이블을 합치는데 설정한 기준대로 합치게 된다는 것이다.
자기와 링크가 걸려있지 않은 row가 있어도 NULL로 채워서라도 갖고온다.

두 개가 아니라 세 개도 Join이 가능하다.

SELECT * FROM topic
LEFT JOIN author
ON topic.author_id=author.aid
LEFT JOIN profile
ON author.profile_id=profile.pid;

그런데 갖고오는 데이터가 너무 난잡하다. 이것들을 원하는 필드들만 갖고와서 하나의 테이블로 만드는 방법도 있다.

SELECT tid, topic.title, author_id, name, profile.title AS job_title
FROM topic
LEFT JOIN author
ON topic.author_id=author.aid
LEFT JOIN profile
ON author.profile_id=profile.pid;

# SELECT 다음에 남겨둘 필드들을 설정해주면 된다.
# 여기서 title과 같이 테이블마다 겹쳐있는건 모호성을 없애주기위해 표이름. 을 앞에 붙여준다.
# profile.title AS job_title 의 의미는 profile.title을 갖고오는데, job_title이란 별명을 줘서 갖고오게된다.

여기서 egoing 이라는 사람이 작성한 글만을 행으로 갖고오고 싶다면, 이전에 작성한 코드를 그대로 갖고오고서 조건을 하나 더 추가하면 된다.

SELECT tid, topic.title, author_id, name, profile.title AS job_title
FROM topic
LEFT JOIN author
ON topic.author_id=author.aid
LEFT JOIN profile
ON author.profile_id=profile.pid
WHERE aid=1;

RIGHT OUTER JOIN

LEFT OUTER JOIN의 반대이다.
오른쪽 테이블을 기준으로 JOIN 하겠다고 생각하면 된다.
결과값은 B테이블의 모든 데이터와 A테이블과 B테이블의 중복되는 값이 검색된다.

--문법--
SELECT
테이블별칭.조회할칼럼,
테이블별칭.조회할칼럼
FROM 기준테이블 별칭
RIGHT OUTER JOIN 조인테이블 별칭 ON 기준테이블별칭.기준키 = 조인테이블별칭.기준키 .....

INNER JOIN

교집합이라고 생각하면 편하다. 기준 테이블과 JOIN 한 테이블의 중복된 값을 보여준다.
결과값은 A의 테이블과 B의 테이블이 모두 가지고 있는 데이터만 검색된다.
INNER JOIN은 굉장히 엄격하기에 NULL 행이 존재하지 않는다.

--문법--
SELECT
테이블별칭.조회할칼럼,
테이블별칭.조회할칼럼
FROM 기준테이블 별칭
INNER JOIN 조인테이블 별칭 ON 기준테이블별칭.기준키 = 조인테이블별칭.기준키....

핵심은 JOIN 뒤에 있는 ON인데, 두 테이블이 결합하는 조건을 나타낸다.

SELECT *
FROM topic
INNER JOIN author
ON topic.author_id = author.aid;

INNER JOIN 일반적으로 더 성능이 좋다고 평가받기에 사용할 수 있다면 사용하지만, 그렇지 못하다면 LEFT JOIN을 사용하게된다.

INNER JOIN 도 당연히 여러개를 붙일 수 있다.

SELECT * FROM topic
INNER JOIN author
ON topic.author_id = author.aid
INNER JOIN profile
ON profile.pid = author.profile_id;

FULL OUTER JOIN

합집합을 생각하면 된다.
A테이블이 가지고 있는 데이터와 B테이블이 가지고 있는 데이터 모두 검색된다.
기준테이블의 의미가 없다.
결국 LEFT JOIN을 하고 RIGHT JOIN 을 한 다음에 중복되는 row를 지워버린다.
그런데 많은 데이터베이스에서 지원하지 않고(MySQL 등) 잘 사용하지 않는다.

SELECT * FROM topic
FULL OUTER JOIN author
ON topic.author_id = author.aid;

위와 같이 MySQL에서는 FULL OUTER JOIN 를 지원하지 않는다. 그렇지만 다른 방식으로 FULL OUTER JOIN 과 같은 결과를 만들어낼 수 있다.

(SELECT * FROM topic
LEFT JOIN author
ON topic.author_id=author.aid)
UNION DISTINCT
(SELECT * FROM topic
RIGHT JOIN author
ON topic.author_id=author.aid);

위와 같이 LEFT JOIN을 하고 RIGHT JOIN을 한 결과를 UNION DISTINCT 라는 명령어로 합치면서 중복을 제거하면 된다.