JOIN 종류와 사용(2)-LEFT&RIGHT JOIN

왼쪽 또는 오른쪽(외부) 조인

이전글[새창으로 열기]에서 살펴보았던 내부조인은 양쪽 테이블 모두에서 대응되는 행들만 보여준다.
외부 조인도 마찬가지로 대응되는 것을 보여주지만, 다른 테이블 내에서 대응되지 않는 행도 보여준다.
이 절에 대해서 대부분의 예제들은 LEFT JOIN을 사용하는데,
이것은 왼쪽 테이블 안에있는 행 중에서 오른쪽 테이블에 의해 대응되지 않는 행들을 식별한다.
RIGHT JOIN은 테이블의 역할만 바뀐 것 외에는 LEFT JOIN과 동일하다.

LEFT JOIN은 다음과 같이 작동 한다.
두 테이블 내에서 대응하는 행들에 대해 사용될 칼럼을 지정한다.
왼쪽 테이블에 있는 어떤 행이 오른쪽 테이블에 있는 어떤 행에 대응될 때, 행들의 내용이 출력 행으로 선택되어진다.
왼쪽의 어떤 행이 대응되는 것이 없으면, 이것도 여전히 출력을 위해 선택되지만,
오른쪽 테이블 내의 모든 칼럼이 NULL로 설정된 것으로 가정한 "가짜"행과 조인되는 것이다.

다시 말하면, 오른쪽 테이블 안에 대응되는 것의 여부를 떠나 강제로 결과 세트에
왼쪽 테이블에 있는 모든 행이 포함되도록 하는 것이다.
대응되지 않는 왼쪽 테이블의 행이 오른쪽 테이블의 모든 칼럼이 NULL 이라는 것에 의해서 식별될 수 있다.
이러한 결과에 의한 행은 어떤 행들이 오른쪽 테이블에서 빠졌는지를 알려준다.

어떤 고객이 평가 대표에서 누락되었다면? 어떤 발명 아이템이 팔리지 않은 것으로 기록되었다면? 가까운 예를 살펴보자.
어떤 학생이 특정 시험을 치르지 않은 것으로 되었다면? 어떤 학생들이 absense(결석) 테이블 행에 포함되지 않았다면 (즉, 어떤 학생들이 개근한 경우라면)?

내부 조인에서 사용했던 두 개의 테이블 t1과 t2를 다시 한 번 생각 해 보자.

t1			t2
i1	c1		i2	c2
1	a		2	c
2	b		3	b
3	c		4	a

내부 조인을 사용해서 t1.i1과 t2.i2에 대해 두 테이블을 대응시키면
값 2와 3에 대한 출력만 얻게 되는데, 이것이 두 테이블 모두에 나타나는 값들인 것이다.

SELECT t1.*, t2.* FROM t1 INNER JOIN t2 ON t1.i1 = t2.i2;
SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2;
/*
SELECT t1.*, t2.* FROM t2 LEFT JOIN t1 ON t1.i1 = t2.i2;
*/

INNER JOIN					LEFT (RIGHT) JOIN
i1	c1	i2	c2		i1	c1	i2	c2
2	b	2	c		1	a	NULL	NULL
3	c	3	b		2	b	2	c
					3	c	3	b

LEFT JOIN에 대해서 주의해야할 점은 오른쪽 칼럼이 not null로 정의되어 있지 않다면 문제가 발생할 수 있는 행을 가질 수 있게 된다.
예를 들면 오른쪽 테이블이 NULL인 칼럼을 가진다면, 이렇게 NULL인 값을 행이 매칭되지 않아서 NULL인 경우와 구분할 수 없게 된다.

LEFT JOIN은 오른쪽 테이블에 대응되지 않는 왼족 테이블 행들만 찾고자 할 때 특히 유용하게 사용할 수 있다.
이 작업은 WHERE 절을 추가하면 되는데, WHERE 조건절에서 오른족 테이블의 값이 NULL인 행을 찾도록 지정한다.
즉, 다시 말하면 한쪽에만 존재하는 행인 것이다.

SELECT t1.*, t2.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2 WHERE t2.i2 IS NULL;
/*
SELECT t1.*, t2.* FROM t2 LEFT JOIN t1 ON t1.i1 = t2.i2 WHERE t1.i1 IS NULL;
*/

i1	c1	i2	c2
1	a	NULL	NULL

보통, 이렇게 쿼리를 작성할 때, 관심있는 것은 왼쪽 테이블 안에있는 대등되지 않는 행들이다.
오른쪽 테이블에 있는 NULL 칼럼들은 표시할 목적에는 별로 부합되는 것이 아니므로,
출력 칼럼 목록에서는 이것들을 제외한다.

SELECT t1.* FROM t1 LEFT JOIN t2 ON t1.i1 = t2.i2 WHERE t2.i2 IS NULL;

i1	c1
1	a

LEFT/RIGHT JOIN 역시 매칭 조건을 지정하기 위해서 ON절, USING()절을 사용하여 작성될 수 있다.
LEFT JOIN은 몇 가자ㅣ 동의어와 변형을 가진다.
LEFT OUTER JOIN == LEFT JOIN 이다.
MySQL은 중괄호와 OJ("outter join")을 사용하여 ODBC 스타일의 LEFT OUTER JOIN을 지원한다.

SELECT t1.* FROM {OJ t1 LEFT OUTER JOIN t2 ON t1.i1 = t2.i2} WHERE t2.i2 IS NULL;

i1	c1
1	a

NATURAL LEFT JOIN은 LEFT JOIN과 같다. 이는 왼쪽과 오른쪽 테이블에서 동일한 이름을 가지는
모든 칼럼을 찾는 LEFT JOIN 동작을 수행한다. (ON과 USING 절에서는 주어지지 않는다.)

예제

아래의 주어진 테이블은 학생들(STUDENT)과 시험이나 퀴즈에 대한 사건(GRADE_EVENT)을 나열하는 테이블이다.
각 사건에 대해서 각 항생들의 성적을 나열(SCORE)하는 테이블도 있다.
하지만, 만일 어떤 학생이 시험이나 퀴즈를 치르는 날에 아파서 결석했다면, 그 score테이블은 해당 사건에 대해
그 학생의 점수는 빠져 있을 것이다. 이러한 학생들이 재시험을 치를 수 있도록 빠진 행들을 찾으려면 어떻게 해야하는가?

SQL문 및 파일

2013.09.06_02.JOIN 종류와 사용(2)-LEFT&RIGHT JOIN.sql

CREATE TABLE STUDENT
(
	NAME VARCHAR(20) NOT NULL,
	GENDER ENUM('F', 'M') NOT NULL,
	STUDENT_ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
	PRIMARY KEY(STUDENT_ID)
)ENGINE=InnoDB;

CREATE TABLE GRADE_EVENT
(
	DATE DATE NOT NULL,
	CATAGORY ENUM('T', 'Q') NOT NULL,
	EVENT_ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
	PRIMARY KEY (EVENT_ID)
)ENGINE=InnoDB;

CREATE TABLE SCORE
(
	STUDENT_ID INT UNSIGNED NOT NULL,
	EVENT_ID INT UNSIGNED NOT NULL,
	SCORE INT NOT NULL,
	PRIMARY KEY (EVENT_ID, STUDENT_ID),
	INDEX (STUDENT_ID),
	FOREIGN KEY(EVENT_ID) REFERENCES GRADE_EVENT(EVENT_ID),
	FOREIGN KEY(STUDENT_ID) REFERENCES STUDENT(STUDENT_ID)
)ENGINE=InnoDB;

INSERT INTO STUDENT VALUES ('Megan','F',NULL), ('Joseph','M',NULL), ('Kyle','M',NULL), ('Katie','F',NULL),
	('Abby','F',NULL), ('Nathan','M',NULL), ('Liesl','F',NULL), ('Ian','M',NULL), ('Colin','M',NULL),
	('Peter','M',NULL), ('Michael','M',NULL), ('Thomas','M',NULL), ('Devri','F',NULL), ('Ben','M',NULL),
	('Aubrey','F',NULL), ('Rebecca','F',NULL), ('Will','M',NULL), ('Max','M',NULL), ('Rianne','F',NULL),
	('Avery','F',NULL), ('Lauren','F',NULL), ('Becca','F',NULL), ('Gregory','M',NULL), ('Sarah','F',NULL),
	('Robbie','M',NULL), ('Keaton','M',NULL), ('Carter','M',NULL), ('Teddy','M',NULL), ('Gabrielle','F',NULL),
	('Grace','F',NULL), ('Emily','F',NULL);

INSERT INTO GRADE_EVENT VALUES ('2012-09-03','Q',NULL), ('2012-09-06','Q',NULL), ('2012-09-09','T',NULL),
	('2012-09-16','Q',NULL), ('2012-09-23','Q',NULL), ('2012-10-01','T',NULL);

INSERT INTO SCORE VALUES (1,1,20), (3,1,20), (4,1,18), (5,1,13), (6,1,18), (7,1,14), (8,1,14),
	(9,1,11), (10,1,19), (11,1,18), (12,1,19), (14,1,11), (15,1,20), (16,1,18), (17,1,9),
	(18,1,20), (19,1,9), (20,1,9), (21,1,13), (22,1,13), (23,1,16), (24,1,11), (25,1,19),
	(26,1,10), (27,1,15), (28,1,15), (29,1,19), (30,1,17), (31,1,11), (1,2,17), (2,2,8),
	(3,2,13), (4,2,13), (5,2,17), (6,2,13), (7,2,17), (8,2,8), (9,2,19), (10,2,18), (11,2,15),
	(12,2,19), (13,2,18), (14,2,18), (15,2,16), (16,2,9), (17,2,13), (18,2,9), (19,2,11),
	(21,2,12), (22,2,10), (23,2,17), (24,2,19), (25,2,10), (26,2,18), (27,2,8), (28,2,13),
	(29,2,16), (30,2,12), (31,2,19), (1,3,88), (2,3,84), (3,3,69), (4,3,71), (5,3,97),
	(6,3,83), (7,3,88), (8,3,75), (9,3,83), (10,3,72), (11,3,74), (12,3,77), (13,3,67),
	(14,3,68), (15,3,75), (16,3,60), (17,3,79), (18,3,96), (19,3,79), (20,3,76), (21,3,91),
	(22,3,81), (23,3,81), (24,3,62), (25,3,79), (26,3,86), (27,3,90), (28,3,68), (29,3,66),
	(30,3,79), (31,3,81), (2,4,7), (3,4,17), (4,4,16), (5,4,20), (6,4,9), (7,4,19), (8,4,12),
	(9,4,17), (10,4,12), (11,4,16), (12,4,13), (13,4,8), (14,4,11), (15,4,9), (16,4,20), (18,4,11),
	(19,4,15), (20,4,17), (21,4,13), (22,4,20), (23,4,13), (24,4,12), (25,4,10), (26,4,15),
	(28,4,17), (30,4,11), (31,4,19), (1,5,15), (2,5,12), (3,5,11), (5,5,13), (6,5,18), (7,5,14),
	(8,5,18), (9,5,13), (10,5,14), (11,5,18), (12,5,8), (13,5,8), (14,5,16), (15,5,13), (16,5,15),
	(17,5,11), (18,5,18), (19,5,18), (20,5,14), (21,5,17), (22,5,17), (23,5,15), (25,5,14), (26,5,8),
	(28,5,20), (29,5,16), (31,5,9), (1,6,100), (2,6,91), (3,6,94), (4,6,74), (5,6,97), (6,6,89),
	(7,6,76), (8,6,65), (9,6,73), (10,6,63), (11,6,98), (12,6,75), (14,6,77), (15,6,62), (16,6,98),
	(17,6,94), (18,6,94), (19,6,74), (20,6,62), (21,6,73), (22,6,95), (24,6,68), (25,6,85), (26,6,91),
	(27,6,70), (28,6,77), (29,6,66), (30,6,68), (31,6,76);

해답 및 해설

두 칼럼의 조합을 찾고 있는 중이다. 학생/사건의 모든 조합이 되는데, STUDENT 테이블과 EVENT 테이블을 조인해서 만들어진다.
FROM STUDENT INNER JOIN GRADE_EVENT

그 다음에 그 조인의 결과를 취하고, score테이블을 가지고 left join을 수행하여 학생 ID/ 이벤트 ID쌍에 대응되는 것을 찾는다.
FROM STUDENT INNER JOIN GRADE_EVENT LEFT JOIN SCORE ON STUDENT.STUDENT_ID = SCORE.STUDENT_ID AND GRADE_EVENT.EVENT_ID = SCORE.EVENT_ID

ON 절은 SCORE 테이블 내의 행들이 다른 테이블 내의 대응에 따라 조인되는 것을 허용하는 것에 주목하기 바란다.
이것이 이러한 문제를 해결하는 열쇠가 된다.
LEFT JOIN은 대응되는 SCORE테이블 행이 없는 경우일지라도, STUDENT 테이블과 GRADE_EVENT테이블의
크로스 조인에 의해 생기는 각 행에 대한 행이 생성되도록 한다.

누락된 점수를 찾고 있으므로 WHERE절을 넣는다.
WHERE SCORE.SCORE IS NULL
ORDER BY절을 사용하여 결과를 순서대로 정렬할 수 있다.
각 학생당 사건 또는 각 사건당 학생의 순서대로 정렬하는 것이 두개의 가장 논리적인 정렬이 된다.
본인은 전자를 택했다.
ORDER BY STUDENT.STUDENT_ID, GRADE_EVENT.EVENT_ID;

SELECT
	STUDENT.NAME, STUDENT.STUDENT_ID,
	GRADE_EVENT.DATE, GRADE_EVENT.EVENT_ID, GRADE_EVENT.CATEGORY
FROM
	STUDENT INNER JOIN GRADE_EVENT
	LEFT JOIN SCORE ON STUDENT.STUDENT_ID = SCORE.STUDENT_ID
					AND GRADE_EVENT.EVENT_ID = SCORE.EVENT_ID
WHERE
	SCORE.SCORE IS NULL
ORDER BY
	STUDENT.STUDENT_ID, GRADE_EVENT.EVENT_ID;

NAME	STUDENT_ID	DATE	EVENT_ID	CATEGORY
Megan	1	2012-09-16	4	Q
Joseph	2	2012-09-03	1	Q
Katie	4	2012-09-23	5	Q
Devri	13	2012-09-03	1	Q
Devri	13	2012-10-01	6	T
Will	17	2012-09-16	4	Q
Avery	20	2012-09-06	2	Q
Gregory	23	2012-10-01	6	T
Sarah	24	2012-09-23	5	Q
Carter	27	2012-09-16	4	Q
Carter	27	2012-09-23	5	Q
Gabrielle	29	2012-09-16	4	Q
Grace	30	2012-09-23	5	Q

출처: Paul DuBois, 김형훈 역, 2009. 한국어판 MySQL 4th ed., chapter2 section8.