[MySQL] JOIN

원래 내가 쓰던 문법

SELECT STU.STU_NUM,STU_NAME,SCORE
FROM JOIN_STU STU, JOIN_SCORE SCORE
WHERE STU.STU_NUM=SCORE.STU_NUM;

ANSI 문법

모든 데이터베이스에서 사용가능한 문법

• 쉼표 대신 INNER JOIN
• JOIN 조건은 WHERE절이 아니라 ON절에 작성
• INNER JOIN->JOIN으로 대체 가능

SELECT STU.STU_NUM,STU_NAME,SCORE
FROM JOIN_STU STU INNER JOIN JOIN_SCORE SCORE
ON STU.STU_NUM=SCORE.STU_NUM;

JOIN은 INNER JOIN(교집합), OUTER JOIN 두 가지로 구분

• OUTER JOIN : RIGHT OUTER JOIN, LEFT OUTER JOIN

모든 학생에 대한 점수를 조회

LEFT OUTER JOIN

왼쪽 테이블(JOIN_STU)에 있는 데이터가 다 나옴

SELECT STU.STU_NUM,STU_NAME,IFNULL(SCORE,0) SCORE
FROM JOIN_STU STU LEFT OUTER JOIN JOIN_SCORE SCORE
ON STU.STU_NUM=SCORE.STU_NUM;

RIGHT OUTER JOIN

SELECT STU.STU_NUM,STU_NAME,IFNULL(SCORE,0) SCORE
FROM JOIN_STU STU RIGHT OUTER JOIN JOIN_SCORE SCORE
ON STU.STU_NUM=SCORE.STU_NUM;

아래의 문법에서 OUTER JOIN 사용하기

SELECT STU.STU_NUM,STU_NAME,SCORE
FROM JOIN_STU STU, JOIN_SCORE SCORE
-- WHERE STU.STU_NUM=SCORE.STU_NUM; INNER JOIN
-- WHERE STU.STU_NUM=SCORE.STU_NUM(+); LEFT JOIN (오라클에서만 가능)
-- WHERE STU.STU_NUM(+)=SCORE.STU_NUM; RIGHT JOIN (오라클에서만 가능)