[SQL] JOIN

JOIN

- 여러 개의 테이블 사용하기

- 데이터의 규모가 커지면서 하나의 테이블로 정보를 수용하기가 어려워지면,

- 테이블을 분할하고 테이블 간의 관계성을 부여한다.

- 분할된 테이블을 하나로 활용할 수 있는 기능이 JOIN

- 테이블간의 관계성에 따라서 복수의 테이블을 결합, 하나의 테이블인 것처럼 결과를 출력

- OUTER JOIN : 매칭되는 행이 없어도 결과를 가져오고, 매칭되는 행이 없는 경우 NULL로 표시

- OUTER JOIN에 LEFT OUTER JOIN(LEFT JOIN), RIGHT OUTER JOIN(RIGHT JOIN)이 포함됨

- INNER JOIN : 조인되는 두 개의 테이블 모두에 데이터가 존재하는 행에 대해서만 결과를 가져옴

- 아래 그림 출처

JOIN 분류를 잘 설명해 둔 그림

 

[기본 문법]

SELECT table.column...

FROM table1

LEFT JOIN table2

ON table1 = table2;

-- CREATE TABLE
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` tinyint(4) NOT NULL,
  `name` char(4) NOT NULL,
  `sex` enum('남자','여자') NOT NULL,
  `address` varchar(50) NOT NULL,
  `distance` INT NOT NULL,
  `birthday` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
   
INSERT INTO `student` VALUES (2, '박재숙', '남자', '서울',  10, '1985-10-26 00:00:00');
INSERT INTO `student` VALUES (1, '이숙경', '여자', '청주', 200, '1982-11-16 00:00:00');
INSERT INTO `student` VALUES (3, '백태호', '남자', '경주', 350, '1989-2-10 00:00:00');
INSERT INTO `student` VALUES (4, '김경훈', '남자', '제천', 190, '1979-11-4 00:00:00');
INSERT INTO `student` VALUES (8, '김정인', '남자', '제주', 400, '1990-10-1 00:00:00');
INSERT INTO `student` VALUES (6, '김경진', '여자', '제주', 400, '1985-1-1 00:00:00');
INSERT INTO `student` VALUES (7, '박경호', '남자', '영동', 310, '1981-2-3 00:00:00');

-- CHECK TABLE
SELECT * FROM student;

-- Connection : address - distance
-- RE-CREATE
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
  `id` tinyint(4) NOT NULL,
  `name` char(4) NOT NULL,
  `sex` enum('남자','여자') NOT NULL,
  `location_id` tinyint(4) NOT NULL,
  `birthday` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `student` VALUES (1, '이숙경', '여자', 1, '1982-11-16 00:00:00');
INSERT INTO `student` VALUES (2, '박재숙', '남자', 2, '1985-10-26 00:00:00');
INSERT INTO `student` VALUES (3, '백태호', '남자', 3, '1989-2-10 00:00:00');
INSERT INTO `student` VALUES (4, '김경훈', '남자', 4, '1979-11-4 00:00:00');
INSERT INTO `student` VALUES (6, '김경진', '여자', 5, '1985-1-1 00:00:00');
INSERT INTO `student` VALUES (7, '박경호', '남자', 6, '1981-2-3 00:00:00');
INSERT INTO `student` VALUES (8, '김정인', '남자', 5, '1990-10-1 00:00:00');

DROP TABLE IF EXISTS `location`;
CREATE TABLE `location` (
`id`  tinyint UNSIGNED NOT NULL AUTO_INCREMENT ,
`name`  varchar(20) NOT NULL ,
`distance`  tinyint UNSIGNED NOT NULL ,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `location` VALUES (1, '서울', 10);
INSERT INTO `location` VALUES (2, '청주', 200);
INSERT INTO `location` VALUES (3, '경주', 255);
INSERT INTO `location` VALUES (4, '제천', 190);
INSERT INTO `location` VALUES (5, '대전', 200);
INSERT INTO `location` VALUES (6, '제주', 255);
INSERT INTO `location` VALUES (7, '영동', 255);
INSERT INTO `location` VALUES (8, '광주', 255);

-- LEFT JOIN
SELECT s.name
    , s.location_id
    , l.name AS address
    , l.distance
FROM student AS s
LEFT JOIN location AS l
ON s.location_id = l.id;

-- DELETE '제주'
DELETE FROM location WHERE name='제주'; 

-- '제주' NULL
SELECT s.name
    , s.location_id
	, l.name AS address
	, l.distance 
FROM student AS s 
LEFT JOIN location AS l 
ON s.location_id = l.id;

-- INNER JOIN - EXCEPT FOR NULL
SELECT s.name
    , s.location_id
	, l.name AS address
	, l.distance
FROM student AS s 
INNER JOIN location AS l 
ON s.location_id = l.id;

 

+ JOIN 보충 강의

- 관계형 데이터베이스에서 테이블과 테이블의 관계를 이용해서 새로운 테이블을 만들어내는 테크닉인 JOIN

- LEFT JOIN : 기준이 되는 표를 왼쪽에 두고, 이 표를 기준으로 오른쪽 표를 합성해서 하나의 표를 만드는 방법

- RIGHT JOIN은 LEFT JOIN을 반대로 하는 것이고, 기준을 왼쪽으로 잡는 것이 생각하기 편하기 때문에, 거의 안 씀

-- CREATE TABLES
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);

-- Check tables
select * from author;
select * from profile;
select * from topic;

-- LEFT JOIN 1
select * from topic left join author on topic.author_id=author.aid;

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

-- LEFT JOIN 3
select tid
    , topic.title AS title
    , author_id, name
    , profile.title AS job_title 
from topic
left join author
on topic.author_id=author.aid
    left join profile 
    on profile.pid = author.profile_id;
    
-- LEFT JOIN 4
select tid
    , topic.title AS title
    , author_id
    , name
    , profile.title AS job_title
from topic 
left join author 
on topic.author_id=author.aid 
    left join profile 
    on profile.pid = author.profile_id 
    where name like 'egoing';

 

- INNER JOIN : 일반적으로 JOIN이라고 하면 INNER JOIN임. NULL 값으로 연결되는 행이 존재하지 않음

- INNER JOIN은 왼쪽에도 오른쪽에도 있는 교집합만 가져옴.

-- Check tables
SELECT * FROM topic;
SELECT * FROM author;
SELECT * FROM profile;

-- INNER JOIN
select * 
from topic 
inner join author 
on topic.author_id = author.aid;

-- INNER JOIN 2
select * 
from topic 
inner join author 
on topic.author_id = author.aid 
    inner join profile 
    on profile.pid = author.profile_id;

 

- FULL OUTER JOIN : 합집합을 가져오는 것, 왼쪽과 오른쪽에 있는 모든 것(중복 제외)을 가져옴

-- Check tables
SELECT * FROM topic;
SELECT * FROM author;
SELECT * FROM profile;

-- OUTER JOIN : UNION(DISTINCT)
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;

 

- EXCLUSIVE JOIN : 한쪽 표에만 있는 정보로 새로운 표를 만드는 법 (A-B : 차집합)

-- Check tables
SELECT * FROM topic;
SELECT * FROM author;
SELECT * FROM profile;

-- EXCLUSIVE JOIN
select * 
from topic 
left join author 
on topic.author_id = author.aid
where author.aid is NULL;

 

 

* 본 내용은 생활코딩(Coding Everybody)을 참고하여 작성했습니다.

- JOIN

- JOIN 보충 강의