MySQL 기초

JOIN

여러개의 테이블 사용하기

데이터의 규모가 커지면서 하나의 테이블로 정보를 수용하기가 어려워지면 테이블을 분활하고 테이블 간의 관계성을 부여한다.

예제

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는 distnace와 관련되어 있기 때문에 location이라는 별도의 테이블로 분할 할 수 있다.

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;

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

JOIN

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

JOIN의 종류

  • OUTTER JOIN : 매칭되는 행이 없어도 결과를 가져오고 매칭되는 행이 없는 경우 NULL로 표시한다.
    LEFT JOIN과 RIGHT JOIN이 있다.
  • INNER JOIN : 조인하는 두개의 테이블 모두에 데이터가 존재하는 행에 대해서만 결과를 가져온다.

예제

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; 


OUTTER JOIN과 INNER JOIN의 차이

Location에서 제주를 삭제 후 OUTTER JOIN(LEFT JOIN)과 INNER JOIN의 차이를 비교

DELETE FROM location WHERE name='제주'; 

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  FROM student AS s INNER JOIN location AS l ON s.location_id = l.id; 

 아래 이미지는 JOIN의 종류에 따른 결과를 보여준다. (출처)

 

댓글

댓글 본문
작성자
비밀번호
  1. 근데 foreign key 는 강의 안한거 같은데? 빠진거 아닌가요?
    내가 못봤나?
  2. 자료가 수만개가 되면 location id 를 일일이 수작업으로 넣어주는 것도 보통일 아니겠네요.
  3. 감사합니다
  4. JustStudy
    고맙습니다
  5. 블루벨
    마침 full outer join이 왜 오류가 뜨는지 궁금했는데 감사합니다. 검색해 볼 생각은 못했네요 ㅎㅎ
    대화보기
    • 툰아미
      그림에 있는 JOIN을 모두 해 보려고 하니, OUTER JOIN에서 실행이 되지 않는 문제가 있네요. 찾아보니까 MySQL에서는 FULL OUTER JOIN을 지원하지 않는다고 하네요. 그래서 UNION을 이용해서 해야 한다고 하네요.
      http://stackoverflow.com......sql

      위 링크를 이용해서 작성해 본 쿼리문이에요. 차이는 NULL값이 결과로 반환되느냐, 그렇지 않느냐의 차이에요.
      (student 테이블을 보면 영동이나 광주에 사는 학생이 없지만, name과 location_id가 NULL로 출력되는지 여부의 차이에요.)

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

      SELECT s.name, s.location_id, l.name AS address, l.distance FROM student AS s LEFT OUTER JOIN location AS l ON s.location_id=l.id
      UNION
      SELECT s.name, s.location_id, l.name AS address, l.distance FROM student AS s RIGHT OUTER JOIN location AS l ON s.location_id=l.id WHERE l.name IS NULL;
    • 이주환
      2016. 04. 12
      정독했습니다.
      정말 이해하기가 쉽네요.
      도움 많이되고 있습니다.
      고급과정까지 마무리한 후에 db연동을 통해 게시판을 구상해봐야겠습니다.
    • 김지훈
      SELECT STRAIGHT_JOIN * FROM
      tbl__a
      left join
      tbl__b
      on
      (tbl__a.id = tbl__b.id)
      where
      tbl_a.id < 30;

      위와 같은 쿼리가 있을 경우
      조인이 where절 보다 먼저 실행이 되죠?
      만약 조인이 where절 보다 먼저 실행이 된다면
      where 절을 조인보다 먼저 실행하게 할 수 있을까요?
    • 정말 감사합니다! 입문자에게 이보다 더 좋은 강의는 없는 것 같습니다.
    • 노마드
      좋은강의 감사합니다. 저는 MS오피스에 있는 엑세스라는 프로그램으로 DB를 처음다뤄봤는데, 그게 테이블을 join을 시각적으로 표시하는데 좋았던것 같습니다. 입문자분들께는 도움이 될것 같습니다.
    • 준맹
      좋은 강의 감사합니다.
    • Rrrr
      저도 공부중인 초보입니다만,

      말씀하신 구문에서는 's.' 이 부분이 뒤에 FROM 이후에 테이블 이름을 약자로 만들어준부분을 보여주는것입니다.

      그러니까, s.location_id 는 구문에서 student AS s 라고 했으니, student 테이블의 location_id 컬럼을
      의미하는 거고, l.name 은 location AS l 라고 했으니, location 테이블의 name 컬럼을 의미합니다.
      대화보기
      • WayneKing
        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;

        이부분 이해가 안돼서 ㅠㅠ

        저 처럼 모르는 분 있나요?
        제가 이해한 부분은 ex)
        SELECT 칼럼이름 AS 가명, 칼럼이름 AS 가명 from 테이블명;
        AS는 alias 로 가명이라는 뜻 입니다....
        아직 배우는 중이라 s.location_id 하고 name 이해가 안돼네요.
      • 닥눈삼
        아마추어가 배우는데 프로가와서 주저리주저리 ㅋ
      • 조신부리
        감사합니다
      • 현상태
        짱이에요
      • 엔터를 땅치면 요롷케
        정말 꿀 강의 감사합니다.~~~
      • Guest
        죄송합니다. 정리를 잘 해야 했는데 주저리 ^^;리플리케이션 쪽에서 모르는거 많이 있는데 많은 도움 됩니다. Java 자료 찾다가 발견한 사이트 인데 모르는 지식이 많네요 다 무료 인듯 하니 ^^; 내일 실무에 적용 해봐야 겠습니다 백업도 복구도 PHP 쉘스크립트 랑 bash 로 짜서 Cron에 돌리고 파일은 rsync 로 하고 있었거든요 대용량이 아니기 때문에 증분 백업 복구 같은건 필요 없었지만.좋은 사이트 새로 발견했으니 다른 코딩 쪽에도 열심히 보겠습니다.나중에 도움이 될수 있으면 좋겠군요 .
        대화보기
        • egoing
          고맙습니다. 주옥 같네요. 저도 천천히 정독하겠습니다 ^^
          2013년 3월 1일 금요일에 Disqus님이 작성:
          대화보기
          • facehan
            -- 아 그리고 FROM 절의 테이블의 FIELD 와 JOIN 절의 테이블의 필드가 1:1 관계라면 서브쿼리를 쓰는것 보다 LEFT JOIN 을 쓰는게 더 낫겠죠. -- 두개 테이블을 비교해서 특정 키가 NULL 인것을 지정하면 한쪽에 있는 데이타만 표시 할수 있겠죠 SELECT * FROM table1 as t1 LEFT JOIN table2 as t2 ON t2.id = t1.id WHERE t2.id IS NULL여러 테이블을 조인하여 DELETE 나 UPDATE 할수도 있어요
            UPDATE table1 as t1LEFT JOIN table2 as t2 ON t1.id = t2.idSET t1.field1 = t2.field2
            WHERE ..... 조건들....
            이건 업데이트 조건에 맞게 일치하는 레코드를 찾아서 업데이트 하는거에요.INNER JOIN 도 쓸수 있는데 이건 일치 하는 데이타가 있을때 업데이트 하는거고LEFT JOIN 이면 일치하는 데이타가 없어도 업데이트 하게 됩니다. 테스트로 SELECT 로 만들어서 해보심 되요.
            DELETE t1,t2, FROM table1 as t1 LEFT JOIN table2 as t2 USING (id)LEFT JOIN table3 as t3 USING (id)WHERE 조건들..이렇게 주면 id 가 일치하는 레코드중 table1 table2 의 레코드만 지워지게 되요. DELETE 랑 FROM 사이에 엘리어스를 주면 그 테이블만 레코드가 삭제 되는데요
            MyISAM은 관계형 DB가 아니라서 저는 3개의 테이블이 동일 ID가 있을때 모조리 지울때 LEFT JOIN 대신에 INNER JON 을 주고 USING 을 이용 해서 USING (id) == ON t1.id = t2.id 이죠. ㅎㅎ.
            DELETE t1,t2,t3 이런식으로 줘서 명령을 때리면 3개의 테이블에 레코드가 다 있으면
            싹 지워지게 됩니다. 3개 테이블의 자료가 만약 3개중에 일치되는게 없다면 3개다 안지워져용... 이럴땐 LEFT JOIN 을 쓰면 되는데요.관계형 DB라면 제약조건을 걸면 상위를 지우면 cascade 였다 그 옵션으로 싹지워지게 할수 있지면 MyISAM의 경우 저는 이런식으로 삭제를 하거든요 제약조건에 걸리면못 지워지게 하는 목적 인거죠.. 오라클이나 관계형 DB에서 제약조건은..
            좋은 사이트에서 좋은 자료를 보면서 저도 아는걸 주저리 적어봤습니다.
            대화보기
            • facehan
              LEFT JOIN 대신에 RIGHT JOIN 을 쓸수 있는데요
              LEFT JOIN 인경우 두개의 테이블을 비교해서 FROM 절에서 지정한 테이블을 우선적으로 표시하고
              LEFT JOIN 에 지정한 테이블을 검색한후 값이 있다면 표시하고 없다면 NULL로 표시 해준다고 생각 하면 됩니다. RIGHT JOIN 은 뒤 바꼈다고 생각 하면 되요 어쩔수없이 FROM 절을 쓰는 경우가 있는데요
              다른 테이블에서 LEFT JOIN 으로 데이타를 검색 해서 가져 왔는데 또 다른 테이블을 조인해서 표시를 하는 경우 그 테이블의 자료를 우선적으로 검색 해서 표시 할 필요가 있을때 사용하거든요.
              조인하는 테이블 에 행이 여러개 이면 여러개의 테이블이 표시될수 있으므로
              제 경험상 FROM 절에서 테이블의 레코드수를 그대로 유지하고 조회만 하는경우 JOIN 하는 테이블의 ON 절에 연결에 하나의 레코드만 나오도록 KEY를 UNIQUE으로 해주는게 좋아요.
            • Starmomo
              주옥같은 예제
              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;
              1. 컬럼의 이름이 다르면 AS를 사용하여 이름을 바꿀 수 있고, 같으면 l.distance처럼 지정하여 갖고온다.>> l.name AS address, l.distance // l.name는 address에 오고, l.distance는 distance로 온다.2. 축약어 사용: AS를 사용하며 긴 문자열을 약자로 변환하여 사용한다.>> student AS s >> location AS l3. LEFT JOIN에 가져올 테이블명, ON 컬럼에 조인 테이블.컬럼을 갖고온다.>> LEFT JOIN 조인할 테이블명 ON s.location_id = l.id;
            버전 관리
            egoing
            현재 버전
            선택 버전
            graphittie 자세히 보기