sql中的表的关联有哪几种类型？

sql中的join示例:

测试数据准备:
create table test_student(
s_id string,
s_name string,
s_age int
);

insert into test_student values ('s1','张三',15); 
insert into test_student values ('s2','李四',16); 
insert into test_student values ('s3','王五',17); 
select * from test_student;
+--------------------+----------------------+---------------------+
| test_student.s_id  | test_student.s_name  | test_student.s_age  |
+--------------------+----------------------+---------------------+
| s1                 | 张三                 | 15                  |
| s2                 | 李四                 | 16                  |
| s3                 | 王五                 | 17                  |
+--------------------+----------------------+---------------------+
3 rows selected (0.406 seconds)

create table test_score(
s_id string,
s_core int
);

insert into test_score values ('s1',70); 
insert into test_score values ('s2',71); 
insert into test_score values ('s4',72); 
insert into test_score values ('s1',80); 
select * from test_score;
+------------------+--------------------+
| test_score.s_id  | test_score.s_core  |
+------------------+--------------------+
| s1               | 70                 |
| s2               | 71                 |
| s4               | 72                 |
| s1               | 80                 |
+------------------+--------------------+
4 rows selected (0.357 seconds)

1. left join (返回左表所有记录)
select 
 t1.s_id
,t1.s_name
,t1.s_age
,t2.s_core
from test_student t1 
left join test_score t2 
on t1.s_id=t2.s_id;
+----------+------------+-----------+------------+
| t1.s_id  | t1.s_name  | t1.s_age  | t2.s_core  |
+----------+------------+-----------+------------+
| s1       | 张三       | 15        | 70         |
| s1       | 张三       | 15        | 80         |
| s2       | 李四       | 16        | 71         |
| s3       | 王五       | 17        | NULL       |
+----------+------------+-----------+------------+
4 rows selected (32.111 seconds)


2. inner join (返回左表与右表关联上的记录)
select 
 t1.s_id
,t1.s_name
,t1.s_age
,t2.s_core
from test_student t1 
inner join test_score t2 
on t1.s_id=t2.s_id;

+----------+------------+-----------+------------+
| t1.s_id  | t1.s_name  | t1.s_age  | t2.s_core  |
+----------+------------+-----------+------------+
| s1       | 张三       | 15        | 70         |
| s2       | 李四       | 16        | 71         |
| s1       | 张三       | 15        | 80         |
+----------+------------+-----------+------------+
3 rows selected (33.368 seconds)

3. full join (返回左表与右表的所有记录)
select 
 t1.s_id
,t1.s_name
,t1.s_age
,t2.s_core
from test_student t1 
full join test_score t2 
on t1.s_id=t2.s_id;
+----------+------------+-----------+------------+
| t1.s_id  | t1.s_name  | t1.s_age  | t2.s_core  |
+----------+------------+-----------+------------+
| s1       | 张三       | 15        | 80         |
| s1       | 张三       | 15        | 70         |
| s2       | 李四       | 16        | 71         |
| s3       | 王五       | 17        | NULL       |
| NULL     | NULL       | NULL      | 72         |
+----------+------------+-----------+------------+
5 rows selected (34.969 seconds)

4. left semi join 
①select后面只能跟左表的字段
②右表只有joinkey参与计算
③等同于in或exists语句
select 
 t1.s_id
,t1.s_name
,t1.s_age
from test_student t1 
left semi join test_score t2 
on t1.s_id=t2.s_id;
+----------+------------+-----------+
| t1.s_id  | t1.s_name  | t1.s_age  |
+----------+------------+-----------+
| s1       | 张三       | 15        |
| s2       | 李四       | 16        |
+----------+------------+-----------+
2 rows selected (33.405 seconds)

select 
 t1.s_id
,t1.s_name
,t1.s_age
from test_student t1 
where t1.s_id in (select s_id from test_score t2);
+----------+------------+-----------+
| t1.s_id  | t1.s_name  | t1.s_age  |
+----------+------------+-----------+
| s1       | 张三       | 15        |
| s2       | 李四       | 16        |
+----------+------------+-----------+
2 rows selected (50.038 seconds)

select 
 t1.s_id
,t1.s_name
,t1.s_age
from test_student t1 
where exists (select * from test_score t2 where t1.s_id=t2.s_id);
+----------+------------+-----------+
| t1.s_id  | t1.s_name  | t1.s_age  |
+----------+------------+-----------+
| s1       | 张三       | 15        |
| s2       | 李四       | 16        |
+----------+------------+-----------+
2 rows selected (34.306 seconds)