初始化语句
DROP TABLE student;
CREATE TABLE student(ID VARCHAR(50) PRIMARY KEY, NAME VARCHAR(50));
INSERT INTO student VALUES('','bobo');
INSERT INTO student VALUES('','sisi');
INSERT INTO student VALUES('','gugu');
INSERT INTO student VALUES('','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','','musicRoom');
INSERT INTO room VALUES('r2','','musicRoom');
INSERT INTO room VALUES('r3','','musicRoom');
INSERT INTO room VALUES('r4','','englishRoom');
INSERT INTO room VALUES('r5','','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','','musicTeacher');
INSERT INTO teacher VALUES('m2','','musicTeacher');
INSERT INTO teacher VALUES('m3','','englishTeacher');
INSERT INTO teacher VALUES('m4','','englishTeacher');
INSERT INTO teacher VALUES('m5','','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语句性能没有差别
.