多表连接

# 多表连接

内连接（inner join）：比较运算符根据每个表的通用列的值匹配两个表中的行

a. select * from emp e,dept d
where e.deptno=d.deptno;

# 等值连接

b. select * from emp e
inner join dept d
on e.deptno=d.deptno;

 

# 非等值连接（一般不用）

select * from emp e
inner join dept d
on e.deptno!=d.deptno;

# 外连接

（1）左外连接（left join）:以左表为基准（左表数据全部显示），去匹配右表数据，若匹配成功则显示全部显示；
匹配不成功，显示部分（无数据部分用NULL填补）

a.
select * from emp e
left outer join dept d
on e.deptno = d.deptno;

 

b.Oracle独有
select * from emp e,dept d
where e.deptno = d.deptno(+);

 

（2）右外连接（right join）

a.
select * from emp e
right outer join dept d
on e.deptno = d.deptno;

 

b.Oracle独有
select * from emp e,dept d
where e.deptno(+) = d.deptno;

 

（3）全外连接：左外+右外-去重

# 内连接
--查询员工编号，姓名及所在部门名称
--笛卡尔积：返回两张表中所有匹配的结果，没有意义，所以多表查询，必写关联条件

select e.id 员工编号,first_name 姓名,name 部门名称 from s_emp e,s_dept d
where e.dept_id = d.id

--查询部门编号以及该部门负责的区域

select d.id,d.name dname,r.name rname from s_dept d,s_region r
where d.region_id = r.id;

# 三表连接

--查询学生成绩单

select studentname,subjectname,studentresult from student s,subject su,result r
where r.studentno = s.studentno
and r.subjectid = su.subjectid;

--查询年级编号为1的年级名称、科目名称及学时

select gradename,subjectname,classhour from grade g,subject s
where g.gradeid = s.gradeid
and s.gradeid = 1;

--查询学生学号、姓名、考试科目名称及成绩 重点：多表关联的条件，其中的一列必须是唯一的。必须有一张表的列唯一，才能关联。

select s.studentno,studentname,subjectname,studentresult from student s,subject su,result r
where s.studentno = r.studentno
and su.subjectid = r.subjectid;

--查询参加"JavaSE"的学生姓名、成绩、考试日期

select studentname,studentresult,examdate from student s,result r,subject su
where s.studentno = r.studentno
and r.subjectid = su.subjectid
and subjectname = 'JavaSE';

 

***多表关联的条件，其中一列必须是唯一的***

--非等值连接
--每个员工的工资级别

select first_name,salary,grade from s_emp e,salgrade g
where e.salary between losal and hisal;

 

select first_name,salary,grade from s_emp e,salgrade g
where e.salary >= losal and e.salary <= hisal;

 

--自连接：一张表中，有多层的业务含义 ――> 将一张表，通过别名 "视为" 不同的表
----谁是领导？
--将s_emp表看成两张相同的表，分别是员工表e,领导表m

select distinct m.id,m.firstname,m.salary from s_emp e,s_emp m
where e.mangager_id = m.id;

 

--自连接比较费性能 emp ――> e,m 如何优化？

--层次连接(优化)

select level,empno,ename,mgr from emp
connect by prior empno=mgr
start with mgr is null
order by level;

 

--子查询范例：

SQL> select empno 第一列,ename 第二列 ,(select job from emp where empno=7369) 第三列 from emp;

 

--查询工资比30号部门 任意其中一个员工高的员工信息

--"只需要满足一个即可，存在一个就可以"――>any

select * from emp where sal> any(select sal from emp);
select * from emp where sal>(select min(sal) from emp);

--"所有、全部"――>all

select * from emp where sal>all(select sal from emp);
select * from emp where sal>(select max(sal) from emp);

------------------------------------------------------------------------------
--注意：子查询中不能存在 not in...范围有NULL值；否则查不出数据（不要有NULL）
--NULL：自身特性――>若!=NULL则无法查询出任何数据
--一般不在子查询中，进行排序

--自连接用法不多，可以用子查询替代

select * from s_emp where id in (
select distinct m.firstname,m.salary from s_emp e,s_emp m
);

--内连接

select * from testa,testb where testa.id = testb.id; --深入人心
select * from testa inner join testb on testa.id = testb.id; --想要推广

--外连接:只会将主表中的数据全部查询，而从表中，只查询匹配，如果不存在则用null代替
----左外连接：左边的表即为主表

select * from testa left join testb on testa.id = test.id;

----右外连接：右边的表即为主表

select * from testa right join testb on testa.id = test.id;