多表查询的分类: 联合条件
1、内联接(查询两张表之间相同数据)
2、外联接(查询 两张表中的数据,一张表显示所有数据(主),另外一张表只显示满足条件的数据(从),没有对应的数据以null填充)
2.1、左外联接(左侧表为主表,右侧表为从表(null))
2.2、右外联接(右侧表为主表,左侧为从表(null))
2.3、完全外联接(了解:)
3、自联接
--查询两表中都满足联接条件的数据
语法:
1、 select 列名列表
from 表1,表2,表3..表n
where 联接条件
and 查询条件
2、select 列名列表
from 表1
inner join 表2 on 联接条件
inner join 表3 on 联接条件
。。。。
where 查询条件
注意:联接条件的个数=表的个数-1
*/
--查询员工的信息(编号,姓名,职位,工资,部门名称)
select e.empno,e.ename,e.job,e.sal,d.dname
from emp e,dept d
where e.deptno = d.deptno;
--
select e.empno,e.ename,e.job,e.sal,d.dname
from emp e inner join dept d on e.deptno = d.deptno;
--salgrade表 工资等级
--查询员工工资等级(编号,姓名,职位,工资,所属工资等级)(emp,salgrade)
select e.empno,e.ename,e.job,e.sal,sg.grade
from emp e,salgrade sg
where e.sal between sg.losal and sg.hisal;
--
select e.empno,e.ename,e.job,e.sal,sg.grade
from emp e inner join salgrade sg on e.sal between sg.losal and sg.hisal;
--查询工资高于3000的员工信息(编号,姓名,职位,工资,部门名称)
select e.empno,e.ename,e.job,e.sal,d.dname
from emp e,dept d
where e.deptno = d.deptno and e.sal>3000;
--
select e.empno,e.ename,e.job,e.sal,d.dname
from emp e inner join dept d on e.deptno = d.deptno
where e.sal>3000;
/*
using 当两个表的关联列的名称相同时使用,并且等值比较
--注意:与using关联的列,在使用时不能添加限定词。
如果没有使用using,那两表中相同的列名在使用时,必须添加限定词
*/
--查询员工的信息(编号,姓名,职位,工资,部门名称)
select e.empno,e.ename,e.job,e.sal,e.deptno,d.dname
from emp e inner join dept d on e.deptno = d.deptno;
--
select e.empno,e.ename,e.job,e.sal,deptno,d.dname
from emp e inner join dept d using(deptno);
---------------------------------------------------外连接
/*
--左外连接
---右外连接
*/
--查询所有部门的员工信息(显示所有部门,编号,姓名,部门编号,部门名称)(dept 主,emp 从)
--左外:
select d.deptno,d.dname,e.empno,e.ename
from emp e,dept d
where d.deptno =e.deptno(+)
order by d.deptno;
--left join
select d.deptno,d.dname,e.empno,e.ename
from dept d left join emp e on d.deptno = e.deptno
order by d.deptno;
--右外
select d.deptno,d.dname,e.empno,e.ename
from emp e,dept d
where e.deptno(+) =d.deptno
order by d.deptno;
--right join
select d.deptno,d.dname,e.empno,e.ename
from emp e right join dept d on d.deptno = e.deptno
order by d.deptno;
--查询没有员工的部门信息(部门编号,部门名称)
select d.deptno,d.dname
from dept d left join emp e on d.deptno = e.deptno
where e.empno is null
order by d.deptno;
--
select deptno ,dname
from dept
where deptno not in(select distinct deptno from emp);
---------------完全外连接
/*
select 列名列表
from 表1 full join 表2 on 关联条件
表1和表2相匹配的数据显示,不匹配的数据也显示,匹配不上的数据以null填充。
*/
create table A
(
aid int primary key
,anm varchar2(20) not null,
bid int not null
);
insert into a values(&aid,'&anm',&bid);
commit;
select * from a;
create table b
(
bid int primary key,
bnm varchar2(20) not null);
insert into b values(&bid,'&bnm');
commit;
select * from b;
--左外(a 主)
select a.aid,a.anm,a.bid,b.bid,b.bnm
from a left join b on a.bid=b.bid;
--左外(b 主)
select a.aid,a.anm,a.bid,b.bid,b.bnm
from b left join a on a.bid=b.bid;
--完全外连接 full join
select a.aid,a.anm,a.bid,b.bid,b.bnm
from a full join b on a.bid = b.bid;
--------------------------------------------------自连接
--查询员工的信息(编号,姓名,领导姓名,工资,职位)
select e.empno,e.ename,me.ename mname,e.sal,e.job
from emp e inner join emp me on e.mgr = me.empno;
注 左右连接是有区别的用的时候注意
左连接是已左边表中的数据为基准,若左表有数据右表没有数据,则显示左表中的数据右表中的数据显示为空。 左联接的结果集包括 LEFT 子句中指定的左表的所有行,而不仅仅是联接列所匹配的行。如果左表的某行在右表中没有匹配行,则在相关联的结果集行中右表的所有选择列表列均为空值。 右联接是左向外联接的反向联接。将返回右表的所有行。如果右表的某行在左表中没有匹配行,则将为左表返回空值。例子
两个表:
A(id,name)
数据:(1,张三)(2,李四)(3,王五)
B(id,name)
数据:(1,学生)(2,老师)(4,校长)
左连接结果:
select A.*,B.* from A left join B on A.id=B.id;
1 张三 1 学生
2 李四 2 老师
3 王五 NULL NULL
右链接结果:
select A.*,B.* from A right join B on A.id=B.id;
1 张三 1 学生
2 李四 2 老师
NULL NULL 4 校长
****************
补充:下面这种情况就会用到外连接
比如有两个表一个是用户表,一个是交易记录表,如果我要查询每个用户的交易记录就要用到左外外连接,因为不是每个用户都有交易记录。
用到左外连接后,有交易记录的信息就会显示,没有的就显示NULL,就像上面我举得例子一样。
如果不用外连接的话,比如【王五】没有交易记录的话,那么用户表里的【王五】的信息就不会显示,就失去了查询所有用户交易记录的意义了。
****************
看一下结果就能明白左右连接的区别了。