左(外)连接

--学生与书的关系，每本书只能属于一个学生
create table tb_student(
student_id number,--学号
student_name varchar2(30),
student_sex char(1),--性别 F/M
student_age number,--年龄
student_birthday date,--学生出生日期
constraint pk_student primary key(student_id)
);
insert into tb_student(student_id, student_name, student_sex, student_age, student_birthday)
  values (seq_student.nextval, '阿Q', 'F', '22', to_date('1991-01-09', 'yyyy-MM-dd') );
insert into tb_student(student_id, student_name, student_sex, student_age, student_birthday) 
  values (seq_student.nextval, '高红成', 'F', '26', to_date('1971-02-12', 'yyyy-MM-dd') );
insert into tb_student(student_id, student_name, student_sex, student_age, student_birthday) 
 values (seq_student.nextval, '李艳', 'F','25', to_date('1981-05-09', 'yyyy-MM-dd') );
insert into tb_student(student_id, student_name, student_sex, student_age, student_birthday) 
 values (seq_student.nextval, '高雄', 'M','21', to_date('1981-05-09', 'yyyy-MM-dd') );
insert into tb_student(student_id, student_name, student_sex, student_age, student_birthday) 
 values (seq_student.nextval, '彭传志', 'M','22', to_date('1981-05-09', 'yyyy-MM-dd') );
 
--书
create table tb_book(
book_id number,--书号
book_name varchar2(32),--书名
student_id number,
constraint fk_book_student foreign key(student_id) references tb_student(student_id)
);
insert into tb_book values(seq_book.nextval, '语文', '6');
insert into tb_book values(seq_book.nextval, '语文', '7');
insert into tb_book values(seq_book.nextval, '语文', '8');
insert into tb_book values(seq_book.nextval, '语文', '9');
insert into tb_book values(seq_book.nextval, '语文', '10');

insert into tb_book values(seq_book.nextval, '数学', '6');
insert into tb_book values(seq_book.nextval, '数学', '7');
insert into tb_book values(seq_book.nextval, '数学', '8');
insert into tb_book values(seq_book.nextval, '数学', '9');
insert into tb_book values(seq_book.nextval, '数学', '10');

insert into tb_book values(seq_book.nextval, 'English', '6');
insert into tb_book values(seq_book.nextval, 'English', '7');
insert into tb_book values(seq_book.nextval, 'English', '8');
insert into tb_book values(seq_book.nextval, 'English', '9');
insert into tb_book values(seq_book.nextval, 'English', '10');
--左连接,把tb_book作为主表
--注意结果集分布, t只与b相关，t与s毫无关联。t表中没有匹配的记录行,所有字段都为null
select * from tb_book b left join tb_student s on b.student_id = s.student_id
                        left join tb_student t on b.student_id = t.student_id and 
                                                  t.student_id in ('6', '7', '8');

 结果：