1、where条件
with student as(
select '1' id,'张三' name,'1' tid from dual union all
select '2' id,'李四' name,'2' tid from dual union all
select '3' id,'王五' name,'1' tid from dual union all
select '4' id,'赵六' name,'5' tid from dual
),
teacher as(
select '1' id,'李老师' name from dual union all
select '2' id,'张老师' name from dual union all
select '3' id,'郑老师' name from dual
)
select * from student s,teacher t where s.tid=t.id
ID NAME TID ID NAME
1 张三 1 1 李老师
2 李四 2 2 张老师
3 王五 1 1 李老师
2、内连接
with student as(
select '1' id,'张三' name,'1' tid from dual union all
select '2' id,'李四' name,'2' tid from dual union all
select '3' id,'王五' name,'1' tid from dual union all
select '4' id,'赵六' name,'5' tid from dual
),
teacher as(
select '1' id,'李老师' name from dual union all
select '2' id,'张老师' name from dual union all
select '3' id,'郑老师' name from dual
)
select * from student s join teacher t on s.tid=t.id
ID NAME TID ID NAME
1 张三 1 1 李老师
2 李四 2 2 张老师
3 王五 1 1 李老师
3、外连接
3.1 左外连接
with student as(
select '1' id,'张三' name,'1' tid from dual union all
select '2' id,'李四' name,'2' tid from dual union all
select '3' id,'王五' name,'1' tid from dual union all
select '4' id,'赵六' name,'5' tid from dual
),
teacher as(
select '1' id,'李老师' name from dual union all
select '2' id,'张老师' name from dual union all
select '3' id,'郑老师' name from dual
)
select * from student s left join teacher t on s.tid=t.id
或者select * from student s left join teacher t on s.tid=t.id(+)
ID NAME TID ID NAME
3 王五 1 1 李老师
1 张三 1 1 李老师
2 李四 2 2 张老师
4 赵六 5
3.2 右外连接
with student as(
select '1' id,'张三' name,'1' tid from dual union all
select '2' id,'李四' name,'2' tid from dual union all
select '3' id,'王五' name,'1' tid from dual union all
select '4' id,'赵六' name,'5' tid from dual
),
teacher as(
select '1' id,'李老师' name from dual union all
select '2' id,'张老师' name from dual union all
select '3' id,'郑老师' name from dual
)
select * from student s right join teacher t on s.tid=t.id
或者select * from student s left join teacher t on s.tid(+)=t.id
ID NAME TID ID NAME
1 张三 1 1 李老师
2 李四 2 2 张老师
3 王五 1 1 李老师
3 郑老师
4、全连接
with student as(
select '1' id,'张三' name,'1' tid from dual union all
select '2' id,'李四' name,'2' tid from dual union all
select '3' id,'王五' name,'1' tid from dual union all
select '4' id,'赵六' name,'5' tid from dual
),
teacher as(
select '1' id,'李老师' name from dual union all
select '2' id,'张老师' name from dual union all
select '3' id,'郑老师' name from dual
)
select * from student s full join teacher t on s.tid=t.id
ID NAME TID ID NAME
1 张三 1 1 李老师
2 李四 2 2 张老师
3 王五 1 1 李老师
4 赵六 5
3 郑老师
总结:where条件和内连接查询出来的数据是一样的,只查询出来能关联上的数据
外连接 是按照主表的数据多少展现出来的。如左外连接主表就是student;右外连 接主表就是teacher
全连接 是把所有的数据都展现出来、不管有没有关联出来数据
内连接、外连接、全连接、where的区别
最新推荐文章于 2023-11-15 11:15:39 发布