[SQL][MYSQL] JOIN

https://opentutorials.org/course/195/1409

 

<Before> 테이블 분할

데이터 규모가 커지면 하나의 테이블로 정보를 담으면 너무 복잡해 져서

테이블을 분할하고 테이블 간의 관계성을 부여하는 것이 일반적임.

 

<기존 Student 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');

address 와 distance 가 연관되어 있음

근데 김정인씨의 distance가 잘못되었다는 것을 알고 수정을 했는데

똑같이 제주도에 사는 김경진씨의 distance를 바꾸지 못하는 일이 발생할 수도 있다

(또한 제주, 400 이라는 동일한 정보가 중복되어 비효율적이다.)

 

address와 distance를 묶어줘 보자 (새로운 table을 만듬)

그러고 관계성을 부여해 보자

 

<변경 Student Table>

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');

location id에 주목

 

<location Table>

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

여기의 location table의 id의 값이  student Table에 location_id에 저장이 된다

그 다음 이 두 테이블을 id, location_id 를 Join해서 한 테이블처럼 사용

 

<Join>

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

 

Outer Join : 매칭되는 행이 없어도 결과를 가져오고 매칭되는 행이 없는 경우 NULL로 표시
(Left Join 과 Right Join 존재)
Inner Join : 조인하는 두개의 테이블 모두에 데이터가 존재하는 행에 대해서만 결과를 가져옴

 

Ex) 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;

SELECT s.name, s.location_id, l.name AS address, l.distance  

SELECT s.name, s.location_id, l.name AS address, l.distance  이런식으러 해석

=> s? l? (아래에서 나옴)

-> location table에 name column을 address라 하겠다

 

    FROM student AS s

=> student 테이블에서 정보를 뽑아와라

=> AS = alias(별명)

=> 즉 student 다 치기 귀찮으니깐 s라 할래

   

    LEFT JOIN

=> 완쪽에 있는 테이블 기준 (student Table 기준으로)

 

    location AS l

=>location 다 치기 귀찮으니깐 l라 할래

    ON s.location_id = l.id;

=> ON : 결합의 조건 (student table의 location_id 와 location table의 id 가 같을 때만)

=> 즉, Left join이므로 student Table을 기준으로 location_id = id 인 location table의 행들을 student에 붙여서 출력

 

이런식으로 활용 가능

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
    WHERE l.distance=10;

 

<Outer Join vs Inner Join>

비교를 위해 location에서 제주를 삭제해보자

DELETE FROM location WHERE name='제주';

 

 

[Outer 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;

박경호를 보면 제주도가 없으니 Null 로 표현

(둘 중에 하나라도 있으면 가져옴 그러고 NULL 처리)

 

[Inner join]

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;

검색결과에서 박경호가 아예 없음(둘 중에 하나라도 없으면 안가져옴)

 

 

 

추가학습자료

https://opentutorials.org/course/3884

SQL Join - 생활코딩