sql内连接、外连接和⾃连接查询
⼀. 前⾔:
通常在项⽬中对表的查询都是关联多张表,多表查询就涉及到sql的内连接、外连接和⾃连接查询。本篇⽂章将简单的介绍这些sql连接的使⽤,希望对⼤家有所帮助。
⼆. 数据准备:
先准备两张表:
1. 学⽣表:student
select * from student;
2. 教师表:teacher
select * from teacher;
三. 关联查询:
1. 内连接:在每个表中出符合条件的共有记录。[x inner join ]
第⼀种写法:(只使⽤where)
acher_name, s.student_name from teacher t,student s where t.id = s.teacher_id;
第⼆种写法:(join .. on.. )
sql left join 多表连接acher_name, s.student_name from teacher t join student s on t.id = s.teacher_id;
第三种写法:(inner join .. on.. )
acher_name, s.student_name from teacher t inner join student s on t.id = s.teacher_id;
2. 外连接
外连接有三种⽅式:左连接,右连接和全连接。
2.1 左连接:根据左表的记录,在被连接的右表中出符合条件的记录与之匹配,如果不到与左表匹配的,⽤null表⽰。[x left [outer] join
第⼀种写法:(left join .. on ..)
acher_name, s.student_name from teacher t left join student s on t.id = s.teacher_id;
第⼆种写法:(left outer join .. on ..)
acher_name, s.student_name from teacher t left outer join student s on t.id = s.teacher_id;
第三种写法:"(+)" 所在位置的另⼀侧为连接的⽅向
acher_name, s.student_name from teacher t, student s where t.id = s.teacher_id(+);
2.2 右连接:根据右表的记录,在被连接的左表中出符合条件的记录与之匹配,如果不到匹配的,⽤null填充。[x right [outer] join ]第⼀种写法:()
acher_name, s.student_name from teacher t right join student s on t.id = s.teacher_id;
第⼆种写法:
acher_name, s.student_name from teacher t right outer join student s on t.id = s.teacher_id;
第三种写法:"(+)" 所在位置的另⼀侧为连接的⽅向
acher_name, s.student_name from teacher t, student s where t.id(+) = s.teacher_id;
2.3 全连接:返回符合条件的所有表的记录,没有与之匹配的,⽤null表⽰(结果是左连接和右连接的并集)
第⼀种写法:(full join .. on ..)
acher_name, s.student_name from teacher t full join student s on t.id = s.teacher_id;
第⼆种写法:(full outer join .. on)
acher_name, s.student_name from teacher t full outer join student s on t.id = s.teacher_id;
3. ⾃连接
⾃连接,连接的两个表都是同⼀个表,同样可以由内连接,外连接各种组合⽅式,按实际应⽤去组合。
SELECT a.*, b.* FROM table_1 a,table_1 b WHERE a.[name] = b.[name]

版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。