MYSQL - 테이블 합치기(JOIN)

테이블 합치기

JOIN 사용하기

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

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 comment "회원 정보 테이블";

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('7', '이거 정말 사야돼!!', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('1', '이거 가방 오나전 별루예요.. 사진빨', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('9', '이거 좋다! 좋다! 좋다!', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('12', '마라탕 먹고싶다.', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('3', '오늘은 정말 마라탕 먹고싶다.', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('16', '가성비 짱입니다. 당장 사세요.', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('19', '안녕하세요. 안녕하세요.', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('2', '안녕못해요. 안녕못해요.', '12345');
INSERT INTO myreview(memberID, youCont, regTime) VALUES('14', '빨리빨리빨리.', '12345');

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

필드명 바꾸기

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