【Oracle】第6天 表连接

联合查询/表连接

将多个表中的数据拼接到一起，将其结果作为一个新的临时的数据源（表） 即表连接；
–只在当前的查询语句中生效

联合查询时过滤条件放在ON之后和放在WHERE之后的区别

有两个表，A表和B表，我们经常会通过一些关键字段来联合查询两张表里的数据，如:

select * 

from A 

left join B

on A.bizNo = B.bizNo

如果我们想要在上述条件上再增加一些过滤条件，比如B.name = ‘XXX’。

那么我们有两种写法:

写法1:

select * 

from A 

left join B

on A.bizNo = B.bizNo and B.name = 'XXX'

写法2:

select * 

from A 

left join B

on A.bizNo = B.bizNo 

where  B.name = 'XXX'

这两种写法的区别在于，过滤条件放在ON的后面是在联合之前就进行过滤，放在WHERE后面是在联合之后的结果集上进行过滤。

如果A的记录在B中都能够查到数据的话，那么两种写法的结果是一样的。

否则会有差别，假如A中有两条记录a1,a2，其中a1可以在B中查到记录，a2无法查到记录。

那么在写法1的情况下，最终的结果集会有两条记录如下:

a1 b1

a2 null

在写法2的情况下，最终的结果集只有一条记录:

a1 b1

内连接:

SELECT COLUMN1,COLUMN2... FROM TABLE1 A [INNER]JOIN TABLE2 B ON A.COLUMN=B.COLUMN;

多表连接：

SELECT * FROM EMP E 
	INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO 
	INNER JOIN SALGRADE S ON E.SAL BETWEEN S.LOSAL AND S.HISAL;

外连接:

SELECT COLUMN1,COLUMN2... FROM TABLE1 A LEFT [OUTER] JOIN TABLE2 B ON A.COLUMN=B.COLUMN;
SELECT COLUMN1,COLUMN2... FROM TABLE1 A RIGHT [OUTER] JOIN TABLE2 B ON A.COLUMN=B.COLUMN;
SELECT COLUMN1,COLUMN2... FROM TABLE1 A FULL [OUTER] JOIN TABLE2 B ON A.COLUMN=B.COLUMN;

——全外连接经常用于排查哪
里有错误是不是数据未匹配是不是数据问题

内连接和外连接的区别：
内连接求交集，外连接求并集
左外连接以左表数据为主，右表列数据较大可能为空

笛卡尔连接:

SELECT COLUMN1,COLUMN2... FROM TABLE1 CROSS JOIN TABLE2;

指数级增长，容易造成数据库崩溃

JOIN 声明连接方式 ON + CONDITION 声明连接时的关联条件，一个表一个字段名，中间用连接符连接组成条件

应用：通过DEPTNO内连接EMP表和DEPT表
→

SELECT * FROM EMP INNER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;

→

总结：内连接按照关联条件匹配数据，匹配不上的数据舍去

应用：通过DEPTNO左外连接DEPT表和EMP表
→

SELECT * FROM DEPT LEFT JOIN EMP ON EMP.DEPTNO=DEPT.DEPTNO;

→

总结：左外连接按照关联条件匹配数据，且左表为主表，右表匹配不上的数据置为空
全外连接显示两表全部的信息，哪里匹配不成功哪里就置为空

应用：笛卡尔连接EMP表和DEPT 表
→

SELECT * FROM EMP CROSS JOIN DEPT;

→

特殊连接方式

自连接：

同一张表做内/外连接

• 示例：

-- 查询EMP表中所有的员工信息以及该员工的领导编号和姓名
SELECT E.*,S.ENAME FROM EMP "E" LEFT JOIN EMP "S" ON E.MGR= S.EMPNO; 

自然连接：

语法：

SELECT * FROM EMP NATURAL JOIN DEPT;

功能：当左右表有且仅有一列列名是相同的时候，可以自动地将该列作为ON条件执行内连接
当没有任何列是相同的时候，会执行笛卡尔连接
当不仅仅有一列列名是相同的时候，会选择其中一列作为ON条件执行内连接

小表好用，大表多表不好用

USING连接：

语法：SELECT * FROM EMP JOIN DEPT USING(DEPTNO);
↑局限性太大于是逐渐淘汰了

习题：

1.查询员工的员工编号，姓名，工资，及其对应的部门名称，部门地址信息
→

SELECT EMPNO,ENAME,SAL,DNAME,LOC FROM EMP LEFT JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO;

2.查询部门名称为 SALES的员工信息
→

SELECT EMP.*,DEPT.DNAME FROM EMP LEFT JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO WHERE DNAME IN('SALES');

3.查询员工的员工编号，姓名，工资，工作以及员工领导的编号及姓名
→

SELECT A.EMPNO,A.ENAME,A.SAL,A.JOB,B.EMPNO,B.ENAME FROM EMP A LEFT JOIN EMP B ON A.MGR=B.EMPNO;

习题二：

1.查询 Students表中的所有记录的 Sname、Ssex和Class列
→

SELECT SNAME,SSEX,CLASS FROM STUDENTS;

2.查询教师所有的单位即不重复的 Depart列
→

SELECT DISTINCT DEPART FROM TEACHERS;

3.查询 Student表的所有记录
→

SELECT * FROM STUDENTS;

4.查询 Score表中成绩在60到80之间的所有记录
→

SELECT * FROM SCORES WHERE SCORE BETWEEN 60 AND 80;

5.查询 Score表中成绩为85,86或88的记录
→

SELECT * FROM SCORES WHERE SCORE IN(85,86,88);

6.查询 Student表中“95031”班或性别为“女”的同学记录
→

SELECT * FROM STUDENTS WHERE CLASS IN ('96031') OR SEX IN('女');

7.以class降序查询 Student表的所有记录
→

select * from students order by class desc;

8.以Cno升序、 score降序查询 Score表的所有记录
→

select * from scores order by cno asc,score desc;

9.查询95031班的学生人数
→

select count(empno) from students where class in('95031');

10.查询score表中的最高分的学生学号和课程号
→

select sno,cno from scores where sal=(select max(sal) from scores);

11.查询“3-105”号课程的平均分
→

select avg(score) from scores where cno in('3-105');

12.査询score表中至少有5名学生选修的并以3开头的课程的平均分数
→

select cno,avg(score) from scores where cno in(select cno from (select cno from scores where cno like '3%') A group by cno having count(*)>=5 );group by cno  having cno like '3%' and count(*)>=5;

13.查询最低分大于70，最高分小于90的sno列
→

select sno from scores group by sno having min(score)>70 and max(score)<90;

14.查询所有学生的 sname、cno和 score列
→

select s.sname,c.cno,c.score from students s left join scores c on s.sno=c.sno;

15.查询所有学生的sno、 cname和 score列
→

select s.sno,c.cname,c.score from students s left join courses c on s.cno=c.cno;