左外连接、右外连接的区别???
外连接: 要把不匹配的记录也要找出来(一个都不能少)
会使用一方表中的所有记录去和另一表中的记录按条件匹配,空值也会匹配,这个表中的所有记录都会显示,数据库会模拟出记录去和那些不匹配的记录匹配。
语法:
左外连接:
outer join:
from t1 left join t2
on t1.c1=t2.c2
右外连接:
from t1 right join t2
on t1.c1=t2.c2
对于外连接(left)时,t1必须做驱动表
对于外连接 (right) ,t2做驱动表
没有匹配记录的,会依然补一个null,然后将其放入结果集中
外连接的结果集包含内连接,他的结果集是内连接的结果集+匹配不上的记录
一、左外连接:在这里e表是驱动表,m表是匹配表,将前面的e表(驱动表)中所有列全都显示出来
如果换成(+)形式的写法,(+)放在匹配表一方,是要将驱动表中的所有记录全都列出来
select e.first_name employee, m.first_name manager
from s_emp e left join s_emp m
on e.manager_id = m.id
1 select e.first_name employee, m.first_name manager
2 from s_emp e , s_emp m
3 where e.manager_id = m.id(+);
二、右外连接:
1 select e.ename,e.deptno,d.deptno
2 from emp e right join dept d
3* on e.deptno = d.deptno;
d是驱动表,e是匹配表,将d表(驱动表)的所有记录全都显示出来
select e.ename,e.deptno,d.deptno
from emp e,dept d
where e.deptno(+)=d.deptno;
在这里d表是驱动表,在匹配表后面加(+)
d是驱动表,e是匹配表,将d表(驱动表)的所有记录全都显示出来
匹配前过滤?匹配后过滤?
如果加and e.ename(+)='SMITH'
select d.dname
from emp e,dept d
where e.deptno(+)=d.deptno
and e.ename(+)='SMITH'
and e.empno is null;
这两个过滤条件,如果有(+)在连接前做,没有的在连接后做,相对于where
内连接:匹配
外连接用来解决什么问题?与不匹配有关系的时候就想到外连接
总结:右外连接使用right join 。。。on,(+)放在=的左边
左外连接使用 left join。。。on,(+)放在=的右边
三、外连接+where is null,解决的是空记录的问题,因为,有的时候,我们就是要使用空的这条记录
1 select e.ename,e.deptno,d.deptno
2 from emp e right join dept d
3 on e.deptno = d.deptno
4* where e.empno is null
注意:4* where e.empno is null,一定是匹配表中的字段
例:右外连接查询员工和领导
1 select e.first_name,m.first_name
2 from s_emp e right join s_emp m
3* on e.manager_id = m.id
结果:
FIRST_NAME FIRST_NAME
------------------------- -------------------------
Audry Carmen
Mark Carmen
Midori Carmen
LaDoris Carmen
Antoinette LaDoris
Ben LaDoris
Roberta LaDoris
Molly LaDoris
Marta LaDoris
Yasmin Midori
Andre Midori
FIRST_NAME FIRST_NAME
1 select e.ename,e.deptno,d.deptno
2 from emp e right join dept d
3* on e.deptno = d.deptno;
d是驱动表,e是匹配表,将d表(驱动表)的所有记录全都显示出来
select e.ename,e.deptno,d.deptno
from emp e,dept d
where e.deptno(+)=d.deptno;
在这里d表是驱动表,在匹配表后面加(+)
d是驱动表,e是匹配表,将d表(驱动表)的所有记录全都显示出来
匹配前过滤?匹配后过滤?
如果加and e.ename(+)='SMITH'
select d.dname
from emp e,dept d
where e.deptno(+)=d.deptno
and e.ename(+)='SMITH'
and e.empno is null;
这两个过滤条件,如果有(+)在连接前做,没有的在连接后做,相对于where
内连接:匹配
外连接用来解决什么问题?与不匹配有关系的时候就想到外连接
总结:右外连接使用right join 。。。on,(+)放在=的左边
左外连接使用 left join。。。on,(+)放在=的右边
三、外连接+where is null,解决的是空记录的问题,因为,有的时候,我们就是要使用空的这条记录
1 select e.ename,e.deptno,d.deptno
2 from emp e right join dept d
3 on e.deptno = d.deptno
4* where e.empno is null
注意:4* where e.empno is null,一定是匹配表中的字段
例:右外连接查询员工和领导
1 select e.first_name,m.first_name
2 from s_emp e right join s_emp m
3* on e.manager_id = m.id
结果:
FIRST_NAME FIRST_NAME
------------------------- -------------------------
Audry Carmen
Mark Carmen
Midori Carmen
LaDoris Carmen
Antoinette LaDoris
Ben LaDoris
Roberta LaDoris
Molly LaDoris
Marta LaDoris
Yasmin Midori
Andre Midori
FIRST_NAME FIRST_NAME
------------------------- -------------------------
Mai Midori
Henry Midori
Colin Midori
Mark
Audry
Elena Molly
George Molly
Vikram Roberta
Akira Roberta
Chad Ben
Alexander Ben
FIRST_NAME FIRST_NAME
------------------------- -------------------------
Radha Antoinette
Eddie Antoinette
Sylvie Marta
Bela Marta
Colin
Henry
Yasmin
Mai
Andre
Elena
George
Mai Midori
Henry Midori
Colin Midori
Mark
Audry
Elena Molly
George Molly
Vikram Roberta
Akira Roberta
Chad Ben
Alexander Ben
FIRST_NAME FIRST_NAME
------------------------- -------------------------
Radha Antoinette
Eddie Antoinette
Sylvie Marta
Bela Marta
Colin
Henry
Yasmin
Mai
Andre
Elena
George
FIRST_NAME FIRST_NAME
------------------------- -------------------------
Akira
Vikram
Chad
Alexander
Eddie
Radha
Bela
Sylvie
41 rows selected.
where 。。。is null。
select e.first_name,m.first_name
2 from s_emp e right join s_emp m
3 on e.manager_id = m.id
4* where e.id is null
------------------------- -------------------------
Akira
Vikram
Chad
Alexander
Eddie
Radha
Bela
Sylvie
41 rows selected.
where 。。。is null。
select e.first_name,m.first_name
2 from s_emp e right join s_emp m
3 on e.manager_id = m.id
4* where e.id is null
SQL> /
FIRST_NAME FIRST_NAME
------------------------- -------------------------
Mark
Audry
Colin
Henry
Yasmin
Mai
Andre
Elena
George
Akira
Vikram
FIRST_NAME FIRST_NAME
------------------------- -------------------------
Chad
Alexander
Eddie
Radha
Bela
Sylvie
17 rows selected.
隐式数据转换:
SQL> select first_name,nvl(manager_id,'Boss')
2 from s_emp;
select first_name,nvl(manager_id,'Boss');//会报错,因为隐式数据转换,boss不能被转为数字,所有要:
FIRST_NAME FIRST_NAME
------------------------- -------------------------
Mark
Audry
Colin
Henry
Yasmin
Mai
Andre
Elena
George
Akira
Vikram
FIRST_NAME FIRST_NAME
------------------------- -------------------------
Chad
Alexander
Eddie
Radha
Bela
Sylvie
17 rows selected.
隐式数据转换:
SQL> select first_name,nvl(manager_id,'Boss')
2 from s_emp;
select first_name,nvl(manager_id,'Boss');//会报错,因为隐式数据转换,boss不能被转为数字,所有要:
1 select first_name,coalesce(to_char(manager_id),'Boss')
2* from s_emp
使用to_char来显示转换
将Carmen的manger的名字用boss填充
1 select e.first_name employee, nvl(m.first_name,'Boss') manager
2 from s_emp e left join s_emp m
3* on e.manager_id = m.id
2* from s_emp
使用to_char来显示转换
将Carmen的manger的名字用boss填充
1 select e.first_name employee, nvl(m.first_name,'Boss') manager
2 from s_emp e left join s_emp m
3* on e.manager_id = m.id