MySQL - 테이블 합치기

테이블 합치기

JOIN 사용하기

{
    SELECT 필드명 FROM 테이블명 엘리어스 JOIN 연결할 테이블명 엘리어스 ON(조건문)

    SELECT m.youName, m.youEmail, r.youCont, r.regTime FROM myMember m JOIN myReview r ON(m.memberID = r.memberID);
}

{
    CREATE TABLE myMember (
        memberID int(10) unsigned auto_increment,
        youEmail varchar(40) NOT NULL,
        youName varchar(20) NOT NULL,
        youPass varchar(20) NOT NULL,
        youBirth int(20) NOT NULL,
        youAge int(20) NOT NULL,
        youPhone int(20) NOT NULL,
        regTime int(11) NOT NULL,
        PRIMARY KEY (memberID)
    ) charset=utf8;
    
    CREATE TABLE myReview (
        reviewID int(10) unsigned auto_increment,
        memberID int(10) unsigned,
        youCont tinytext NOT NULL,
        regTime int(11) NOT NULL,
        PRIMARY KEY (reviewID)
    ) charset=utf8;
    
    INSERT INTO myReview (memberID, youCont, regTime) VALUES('4', '정말 감사합니다', '12345');
    INSERT INTO myReview (memberID, youCont, regTime) VALUES('1', '유익하네요', '12345');
    INSERT INTO myReview (memberID, youCont, regTime) VALUES('2', '이거 정말 사야해', '12345');
    INSERT INTO myReview (memberID, youCont, regTime) VALUES('3', '너무 좋아요!', '12345');
    INSERT INTO myReview (memberID, youCont, regTime) VALUES('5', '감사합니다', '12345');
    INSERT INTO myReview (memberID, youCont, regTime) VALUES('7', '가성비 좋네요', '12345');
    INSERT INTO myReview (memberID, youCont, regTime) VALUES('9', '정말 감사합니다', '12345');
    INSERT INTO myReview (memberID, youCont, regTime) VALUES('10', '정말 감사합니다', '12345');
    INSERT INTO myReview (memberID, youCont, regTime) VALUES('13', '정말 감사합니다', '12345');
    INSERT INTO myReview (memberID, youCont, regTime) VALUES('16', '정말 감사합니다', '12345');
}

필드명 바꾸기

{
    SELECT 필드명 AS 기존 필드명 대신 출력할 필드명 FROM 테이블명

    SELECT m.youName, m.youEmail, r.youCont, r.regTime AS reviewTime FROM myMember m JOIN myReview r ON(m.memberID = r.memberID);
}

필드 내용이 없는 경우 표시

{
    LEFT JOIN

    SELECT m.youName, m.youEmail, r.youCont, r.regTime FROM myMember m LEFT JOIN myReview r ON(m.memberID = r.memberID);
}