内连接、外连接、全连接、where的区别

    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
          全连接 是把所有的数据都展现出来、不管有没有关联出来数据