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

 

 

student表:

ID	NAME
1	bobo
2	sisi
3	gugu
4	mimi

 

 

 

 

 

 

 

 

 room表:

ROOM_ID	STUDENT_ID	ROOM_TYPE
r1	1	musicRoom
r2	2	musicRoom
r3	3	musicRoom
r4	1	englishRoom
r5	2	englishRoom

 

 

 

 

 

 

 

 

 

 

teacher表:

TEACHER_ID	STUDENT_ID	TEACHER_TYPE
m1	1	musicTeacher
m2	2	musicTeacher
m3	1	englishTeacher
m4	2	englishTeacher
m5	3	englishTeacher

 

 

 

 

 

 

 

 

 

 

左外连接sql语句一

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

 

左外连接sql语句二

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

 

查询结果集如下:

ID	NAME	ROOM_ID	STUDENT_ID	ROOM_TYPE	TEACHER_ID	STUDENT_ID	TEACHER_TYPE
1	bobo	r1	1	musicRoom	m1	1	musicTeacher
2	sisi	r2	2	musicRoom	m2	2	musicTeacher

 

 

 

 

 

两个左外连接sql语句性能没有差别

 

 

 

.