准备工作
- 文件JoinDB.sql
- 表如下
等价于如下
select * from student s inner join class c on s.class_id = c.class_id
自连接
```sql
select s1.id,s1.class_id, s1.stu_name,s2.stu_name as 班长 from student s1,student s2 where s1.manager_id = s2.id
外连接
左联接:是以左表为基准,将b.stu_id = s.id的数据进行连接,然后将左表没有的对应项显示,右表的列为NULL
select * from book as b left join student as s on b.stu_id = s.id
右连接:是以右表为基准,将b.stu_id = s.id的数据进行连接,然以将右表没有的对应项显示,左表的列为NULL
select * from book as b right join student as s on b.stu_id = s.id
完整链接(全连接):完整外部联接返回左表和右表中的所有行。当某行在另一个表中没有匹配行时,则另一个表的选择列表列包含空值。如果表之间有匹配行,则整个结果集行包含基表的数据值。
select * from book as b full outer join student as s on b.stu_id = s.id
交叉连接
也叫做笛卡尔积
select * from book cross join student