亲密接触：oracle sql 复杂查询（上）

欢迎大家来到IT世界,在知识的湖畔探索吧!

【七】复杂查询(上)：多表连接技术

7.1 简单查询的解析方法：

全表扫描：指针从第一条记录开始，依次逐行处理，直到最后一条记录结束；

横向选择+纵向投影=结果集

7.2 多表连接

7.2.1多表连接的优缺点

优点：

1）减少冗余的数据，意味着优化了存储空间，降低了IO负担。

2）根据查询需要决定是否需要表连接。

3）灵活地增加字段，各表中字段相对独立（非主外键约束），增减灵活。

缺点：

1）多表连接语句可能冗长复杂，易读性差。

2）可能需要更多的CPU资源，一些复杂的连接算法消耗CPU和Memory。

3）只能在一个数据库中完成多表连接查询。

7.2.2多表连接中表的对应关系

1）一对一关系

将表一分为二，最简单的对应关系

2）一对多关系

两表通过定义主外键约束，符合第三范式标准的对应关系。

3）多对多关系

多对多关系

非标准的对应关系：当两表为多对多关系的时候，通常需要建立一个中间表，中间表至少要有两表的主键，这样，可使中间表分别与每个表为一对多关系。

7.2.3 多表连接的种类和语法

交叉连接（笛卡尔积）

非等值连接

等值连接(内连)

外连接（内连的扩展，左外，右外，全连接）

自连接

自然连接（内连，隐含连接条件,自动匹配连接字段）

复合连接（多个结果集进行并、交、差）

7.2.1 交叉连接（笛卡尔积）

连接条件无效或被省略，两个表的所有行都发生连接，所有行的组合都会返回（n*m）

SQL99写法：

SCOTT@ prod>select * from emp e cross join dept d;

Oracle写法：

SCOTT@ prod>select * from emp e, dept d;

7.2.2非等值连接：（连接条件没有“=”号）

SQL99写法：

SCOTT@ prod>select empno, ename, sal, grade, losal,hisal from emp join salgrade on sal between losal and hisal;

Oracle写法：

SCOTT@ prod>select empno, ename, sal, grade, losal, hisal from emp, salgrade where sal between losal and hisal;

7.2.3 等值连接，典型的内连接

SQL99写法：

SCOTT@ prod>select e.ename, d.loc from emp e inner join dept d on e.deptno=d.deptno;

Oracle写法：

SCOTT@ prod>select e.ename, d.loc from emp e,dept d where e.deptno=d.deptno;

7.2.4 等值连接的using字句（常用）

等值连接的连接字段可以相同，

比如 on e.deptno=d.deptno;

也可以不同

比如 on e.empno=e.mgr

如果连接字段相同，可以使用using字句简化书写

如 on e.deptno=d.detpno.; 换成using(deptno)

例:

SCOTT@ prod>select deptno, e.ename, d.loc from emp e inner join dept d using(deptno); using里也可以多列，使用using关键字注意事项：

1、如果select的结果列表项中包含了using关键字所指明的那个关键字，那么，不要指明该关键字属于哪个表。

2、using中可以指定多个列名。

3、on和using关键字是互斥的，也就是说不能同时出现。

7.2.5 外连接（包括左外连接，右外连接，全外连接）

1）左外连接语法

SQL99语法:

SCOTT@ prod>select * from emp e left outer join dept d on e.deptno=d.deptno;

Oracle语法:

SCOTT@ prod>select * from emp e, dept d where e.deptno=d.deptno(+);

2）左连接要理解两个关键点

1、如何确定左表和右表

SQL99写法：通过from后面表的先后顺序确定，第一个表为左表

SCOTT@ prod>select e.ename, d.loc from emp e left join dept d on e.deptno=d.deptno;

from后第一个表是emp表，为左表，“=”左右位置无所谓

Oracle写法：通过where 后面的“=”的位置确定，“=”号左边的为左表

SCOTT@ prod>select e.ename, d.loc from emp e, dept d where e.deptno=d.deptno(+);

“=”左边是emp表，为左表，from后面表位置无所谓

2、左连是左表为主

①左连是以左表为驱动，每行都参与匹配右表的行，匹配上就连成一行，如果匹配不上，左表行也不缺失该连接行，这时右表内容填空就是了。

②左连后，左表的行是不缺失的，即左连后的结果集的行数>=左表行数，存在>的可能是因为左表的一行可能匹配了右表的多行。

③也可以左表、右表都是同一个表，即“自左连”。

3、到底哪个表当做主表好

无一定之规，根据业务需求来决定。

两表之间一般以主外键确定一对多关系，外键表是明细表，比如emp和dept的关系，以deptno确定父子关系，emp是外键表

你要查每个员工的工作地点，这时以外键表（emp明细表）做左表理所当然

SCOTT@ prod>select e.ename, d.loc from emp e left outer join dept d on e.deptno=d.deptno;

你要查每个部门有多少员工，要求根据每个部门号做统计（40号部门没有员工也统计），这时以主键表(dept)做做表更合理

SCOTT@ prod>select d.deptno, count(e.ename) from emp e, dept d where d.deptno=e.deptno(+) group by d.deptno;

推导一下：

SCOTT@ prod>select d.deptno, e.ename from dept d left outer join emp e on e.deptno=d.deptno;

以上三点，属于个人理解，这套法则同样适用于右连。

2）右外连接

SQL99语法:

SCOTT@ prod>select * from emp e right join dept d on e.deptno=d.deptno;

Oracle语法:

SCOTT@ prod> select * from emp e, dept d where e.deptno(+)=d.deptno;

3）全外连接

SQL99语法:

SCOTT@ prod> select * from emp e full join dept d on e.deptno=d.deptno;

Oracle语法:（无，等同于union连接）

SCOTT@ prod>

select * from emp e, dept d where e.deptno=d.deptno(+)

Union

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

7.2.6 自连接

SQL99语法：

SCOTT@ prod>select e1.empno,e2.mgr from emp e1 cross join emp e2;

Oracle语法:

SCOTT@ prod> select e1.empno,e2.mgr from emp e1,emp e2;

注意：必须使用别名区别不同的表

7.2.6 自然连接（属于内连中等值连接）

使用关键字natural join,就是自然连接。

SCOTT@ prod>select e.ename, d.loc from emp e natural join dept d;

如果有多列复合匹配条件，则自动多列匹配。

7.3 复合查询（使用集合运算符）

Union，对两个select结果集进行并集操作，重复行只取一次，同时进行默认规则的排序；

Union All，对两个select结果集进行并集操作，包括所有重复行，不进行排序；

Intersect，对两个select结果集进行交集操作，重复行只取一次，同时进行默认规则的排序；

Minus，对两个select结果集进行差操作，不取重复行，同时进行默认规则的排序。

复合查询操作有并，交，差３种运算符。

示例：

SQL> create table dept1 as select * from dept where rownum <=1;

SQL> insert into dept1 values (80, ‘MARKTING’, ‘BEIJING’);

SQL> select * from dept;

DEPTNO DNAME LOC

———- ————– ————-

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> select * from dept1;

DEPTNO DNAME LOC

———- ————– ————-

10 ACCOUNTING NEW YORK

80 MARKTING BEIJING

1）union

SQL>

select * from dept

union

select * from dept1;

DEPTNO DNAME LOC

———- ————– ————-

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

80 MARKTING BEIJING

2）union all

SQL>

select * from dept

union all

select * from dept1;

DEPTNO DNAME LOC

———- ————– ————-

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

10 ACCOUNTING NEW YORK

80 MARKTING BEIJING

特别注意：可以看出只有union all的结果集是不排序的。

3）intersect

SQL>

select * from dept

intersect

select * from dept1;

DEPTNO DNAME LOC

———- ————– ————-

10 ACCOUNTING NEW YORK

4）minus（注意谁minus谁）

SQL>

select * from dept

minus

select * from dept1;

DEPTNO DNAME LOC

———- ————– ————-

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL>

select * from dept1

minus

select * from dept;

DEPTNO DNAME LOC

———- ————– ————-

80 MARKTING BEIJING

7.4 复合查询几点注意事项

1）列名不必相同，但要类型匹配且顺序要对应，大类型对上就行了，比如char对varchar2，date对timestamp都可以，字段数要等同，不等需要补全。

create table a (id_a int,name_a char(10));

create table b (id_b int,name_b char(10),sal number(10,2));

insert into a values (1, ‘sohu’);

insert into a values (2, ‘sina’);

insert into b values (1, ‘sohu’, 1000);

insert into b values (2, ‘yahoo’, 2000);

commit;

SQL> select * from a;

ID_A NAME_A

———- ———-

1 sohu

2 sina

SQL> select * from b;

ID_B NAME_B SAL

———- ———- ———-

1 sohu 1000

2 yahoo 2000

SQL>

select id_a,name_a from a

union

select id_b,name_b from b;

2）四种集合运算符优先级按先后出现的顺序执行，如有特殊要求可以使用（）。

3）关于复合查询中order by 使用别名排序的问题：

①缺省情况下，复合查询后的结果集是按所有字段的组合隐式排序的（除union all 外）

如果不希望缺省的排序，也可以使用order by显式排序

SQL> select id_a, name_a name from a

union

select id_b, name_b name from b

order by name;

SQL>

select id_a, name_a from a

union

select id_b, name_b from b

order by 2;

②显式order by是参照第一个select语句的列元素。所以，order by后的列名只能是第一个select使用的列名、别名、列号。如果是补全的null值需要order by，则需要使用别名。

SQL>

select id_a, name_a name,to_number(null) from a

union

select id_b, name_b name,sal from b

order by sal;

报错：ORA-00904: “SAL”: 标识符无效

以下三种写法都是正确的:

SQL>

select id_a, name_a name,to_number(null) from a

union

select id_b, name_b name,sal from b

order by 3;

SQL>

select id_b, name_b name,sal from b

union

select id_a, name_a name,to_number(null) from a

order by sal;

SQL>

select id_a, name_a name,to_number(null) aa from a

union

select id_b, name_b name,sal aa from b

order by aa;

③排序是对复合查询结果集的排序，不能分别对个别表排序，order by 只能一次且出现在最后一行；

SQL>

select id_a, name_a from a order by id_a

union

select id_b, name_b from b order by id_b;

报错：ORA-00933: SQL 命令未正确结束

the end ！！！

@jackman 共筑美好！