선생님을 위한 프로그래밍 수업

JOIN

토픽 선생님을 위한 프로그래밍 수업 > 웹 애플리케이션 만들기 > MySQL

여러개의 테이블 사용하기

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

예제

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. SangYong Park
    판다스랑 많이 비슷하네요.
    답글
  2. ggyuker
    2022-01-13 수강완료
    답글
  3. 익명
    2번째 예제에서 17번줄 ;; 오타네요. ; 한번만 써야 에러 안 납니다. ^^
    답글수정삭제
  4. kaonmir
    감사합니닷!
    답글
  5. sheis
    잘 봤어요^^
    답글
  6. 워너
    이고잉님 항상 감사드립니다.
    언제나 느끼지만 어려워하는 학생들의 눈높이에서 설명해주셔서 많은 도움받고 있습니다
    답글수정삭제
  7. CARIN
    고급수업은 어디에있나요?? 안보이는데..
    답글
  8. NamJin Kim
    감사합니다
    답글
  9. angel
    근데 foreign key 는 강의 안한거 같은데? 빠진거 아닌가요?
    내가 못봤나?
    답글수정삭제
  10. angel
    자료가 수만개가 되면 location id 를 일일이 수작업으로 넣어주는 것도 보통일 아니겠네요.
    답글수정삭제
  11. 민
    감사합니다
    답글수정삭제
  12. JustStudy
    고맙습니다
    답글
  13. 블루벨
    마침 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 절을 조인보다 먼저 실행하게 할 수 있을까요?
      답글
    • cham
      정말 감사합니다! 입문자에게 이보다 더 좋은 강의는 없는 것 같습니다.
      답글수정삭제
    • 노마드
      좋은강의 감사합니다. 저는 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;
              답글