SQL连接类型详解-CSDN博客

本文详细介绍了SQL中的连接类型，包括内连接（等值连接）、左外连接、右外连接及全外连接，并通过实例展示了如何使用子查询来获取关联表中的信息。

1.内连接(等值连接 )

等值连接

select * from t_class c,t_teacher t where c.class_teacher = t.tea_id

内连接

select * from t_class c inner join t_teacher t on c.class_teacher = t.tea_id

2.外连接

左外

select * from t_class c left join t_teacher t on c.class_teacher = t.tea_id

右外

select * from t_teacher t right join t_class c on c.class_teacher = t.tea_id

全外（左 || 右）将永远的返回主(左)表以及从(右)表当中的所有记录

select * from t_class c full outer join t_teacher t on c.class_teacher = t.tea_id

select * from t_class c  left join  t_teacher t on c.class_teacher = t.tea_id
union 
select * from t_class c  right  join  t_teacher t on c.class_teacher = t.tea_id

3.使用子查询

select c.class_id,c.class_code,c.class_name,c.class_open_date,

(select t.tea_name  from t_teacher t where t.tea_id = c.class_teacher) as teacher_name,
(select t.tea_sex  from t_teacher t where t.tea_id = c.class_teacher) as teacher_sex,
(select t.tea_phone from t_teacher t where t.tea_id = c.class_teacher) as teacher_phone 

from  t_class c