sql三表左外链接的2种写法【原】

DROP TABLE student;
CREATE TABLE student(ID VARCHAR(50) PRIMARY KEY, NAME VARCHAR(50));
INSERT INTO student VALUES('1','bobo');
INSERT INTO student VALUES('2','sisi');
INSERT INTO student VALUES('3','gugu');
INSERT INTO student VALUES('4','mimi');

DROP TABLE room;
CREATE TABLE room(room_id VARCHAR(50) PRIMARY KEY, student_id VARCHAR(50),room_type VARCHAR(50));
TRUNCATE TABLE room;
INSERT INTO room VALUES('r1','1','musicRoom');
INSERT INTO room VALUES('r2','2','musicRoom');
INSERT INTO room VALUES('r3','3','musicRoom');
INSERT INTO room VALUES('r4','1','englishRoom');
INSERT INTO room VALUES('r5','2','englishRoom');
create INDEX r_student_id on room (STUDENT_ID);
create INDEX room_type on room (ROOM_TYPE);
SELECT * FROM room;

DROP TABLE teacher;
CREATE TABLE teacher(teacher_id VARCHAR(50) PRIMARY KEY, student_id VARCHAR(50),teacher_type VARCHAR(50));
TRUNCATE TABLE teacher;
INSERT INTO teacher VALUES('m1','1','musicTeacher');
INSERT INTO teacher VALUES('m2','2','musicTeacher');
INSERT INTO teacher VALUES('m3','1','englishTeacher');
INSERT INTO teacher VALUES('m4','2','englishTeacher');
INSERT INTO teacher VALUES('m5','3','englishTeacher');
create index t_student_id on teacher (STUDENT_ID);
create index teacher_type on teacher (TEACHER_TYPE);

SELECT * FROM student s LEFT OUTER JOIN teacher t ON s.id = t.student_id LEFT OUTER JOIN room r ON s.id = r.student_id WHERE t.teacher_type = 'musicTeacher' AND r.room_type = 'musicRoom';

SELECT * FROM (SELECT* FROM student s LEFT OUTER JOIN teacher t ON s.id = t.student_id WHERE t.teacher_type = 'musicTeacher' ) t1 LEFT OUTER JOIN room r ON t1.id = r.student_id WHERE r.room_type = 'musicRoom';