SQLCookbook中文版:第3章操作多个表

本章介绍如何使用联接和集合操作，将多个表的数据组合在一起。联接是 SQL 的基础。集合操作也非常重要。本书后续章节的复杂查询，都以本章的联接和集合操作为基础。

要求来自不同表的同一列的数据类型需要相同.

emp, dep 都有名称和编号 , union all 是简单的将多个表中的行组合到一起, 放在一个结果集合中

结果集之间使用union all 或 union 进行叠加
每个结果集的数据列的数量和数据类型应该相同
union all 包含重复的项目
union 会去重

select ename as ename_and_dname,deptno from emp where deptno=10
union all
select '------------','---'
union all
select dname,deptno from dept

结果:

ename_and_dname deptno
CLARK 10
KING 10
MILLER 10
------------ ---
ACCOUNTING 10
RESEARCH 20
SALES 30
OPERATIONS 40

可以使用 distinct 去重.通常,查询中不要使用DISTINCT，除非确有必要这样做，对于UNION 而言也是如此，除非确有必要，一般使用UNION ALL，而不使用UNION。

使用where子句

select emp.ename,dept.loc,emp.deptno as emp_deptno,dept.deptno as dept_deptno 
from emp,dept
where emp.deptno=10

这种方式计算的是笛卡尔积: 所有可能的组合

ename loc emp_deptno dept_deptno
CLARK NEW YORK 10 10
CLARK DALLAS 10 20
CLARK CHICAGO 10 30
CLARK BOSTON 10 40

KING NEW YORK 10 10
KING DALLAS 10 20
KING CHICAGO 10 30
KING BOSTON 10 40

MILLER NEW YORK 10 10
MILLER DALLAS 10 20
MILLER CHICAGO 10 30
MILLER BOSTON 10 40

使用两个表之间的字段关系,使用where进行连接,仅返回 emp.deptno=dept.deptno 的行构成的组合:

select emp.ename,dept.loc,emp.deptno as emp_deptno,dept.deptno as dept_deptno 
from emp,dept
where emp.deptno=10 and emp.deptno=dept.deptno

结果:

ename loc emp_deptno dept_deptno
CLARK NEW YORK 10 10
KING NEW YORK 10 10
MILLER NEW YORK 10 10

使用 join子句的方案,结果是一样的:

select emp.ename,dept.loc,emp.deptno as emp_deptno,dept.deptno as dept_deptno 
from emp join dept on (emp.deptno=dept.deptno) 
where emp.deptno=10

create view v as
select ename,job,sal from emp where job='CLERK';
select * from v

结果:

ename job sal
SMITH CLERK 800.00
ADAMS CLERK 1100.00
JAMES CLERK 950.00
MILLER CLERK 1300.00

现在要查询 emp表中与 v 匹配的 empno,ename,job,sal,deptno:

1.使用 where子句

select emp.empno,emp.ename,emp.job,emp.sal,emp.deptno from emp,v where
    emp.ename= v.ename and emp.job=v.job and emp.sal=v.sal

结果:

empno ename job sal deptno
7369 SMITH CLERK 800.00 20
7876 ADAMS CLERK 1100.00 20
7900 JAMES CLERK 950.00 30
7934 MILLER CLERK 1300.00 10

2.使用join子句进行连接,结果相同:

select emp.empno,emp.ename,emp.job,emp.sal,emp.deptno from emp join v on
    (emp.ename= v.ename and emp.job=v.job and emp.sal=v.sal)

3.使用intersect(交集)

union像并集, 而 intersect 像交集

select empno,ename,job,sal,deptno from emp
where (ename,job,sal) in (
    select ename,job,sal from emp
    intersect 
    select ename,job,sal from v
)

集合操作默认情况下不会返回重复的行.

select deptno from dept 
except deptno from emp

MySQL不支持 except, 用 not in

select deptno from dept 
where  deptno not in (select deptno from  emp)

注意如果not in 的集合中有null值,可能会返回空值,需要处理 null值

实际是去除匹配的行

select dept.* from dept 
left join emp on (emp.deptno=dept.deptno)
where emp.deptno is null

结果:

deptno dname loc
40 OPERATIONS BOSTON

实质是找 left join后的null值

select dept.deptno,dept.dname,dept.loc,emp.deptno as emp_deptno from dept
left join emp on (emp.deptno=dept.deptno)

结果:

deptno dname loc emp_deptno
20 RESEARCH DALLAS 20
30 SALES CHICAGO 30
30 SALES CHICAGO 30
20 RESEARCH DALLAS 20
30 SALES CHICAGO 30
30 SALES CHICAGO 30
10 ACCOUNTING NEW YORK 10
20 RESEARCH DALLAS 20
10 ACCOUNTING NEW YORK 10
30 SALES CHICAGO 30
20 RESEARCH DALLAS 20
30 SALES CHICAGO 30
20 RESEARCH DALLAS 20
10 ACCOUNTING NEW YORK 10
40 OPERATIONS BOSTON NULL

   select * from emp_bonus

结果如下, 只有三个员工有bonus:

empno received type
7369 2005-05-14 1
7900 2005-05-14 2
7788 2005-05-14 3

执行:

select emp.ename,dept.loc,emp_bonus.received from emp,dept,emp_bonus
where emp.deptno=dept.deptno 
and emp.empno=emp_bonus.empno

输出:

ename loc received
SMITH DALLAS 2005-05-14
SCOTT DALLAS 2005-05-14
JAMES CHICAGO 2005-05-14

where 仅返回多个表共同的行, 如果emp_bonus没有的行, 则不会显示. 如果emp_bonus不存在的返回空值,则需要左连接

select emp.ename,dept.loc,emp_bonus.received from emp 
join dept on (emp.deptno=dept.deptno )
left join emp_bonus on (emp.empno=emp_bonus.empno)

输出:

ename loc received
SMITH DALLAS 2005-05-14
JAMES CHICAGO 2005-05-14
SCOTT DALLAS 2005-05-14
ALLEN CHICAGO NULL
WARD CHICAGO NULL
JONES DALLAS NULL
MARTIN CHICAGO NULL
BLAKE CHICAGO NULL
CLARK NEW YORK NULL
KING NEW YORK NULL
TURNER CHICAGO NULL
ADAMS DALLAS NULL
FORD DALLAS NULL
MILLER NEW YORK NULL

join受空值影响, left join不受空值影响
连接多个表,全部使用join,不要使用where

(略)

**一般来说，要避免产生笛卡儿积，需要使用$n-1$ 规则，这里的$n$为FROM 子句中表的数量, 并且 $n-1$ 是要避免产生笛卡儿积的最小联接数。根据在表中的关键字和联接列不同，可能需要超过 $n-1$个联接，但是对当写查询来说，$n-1$是一个好的开始。

关键字或其他字段的连接是为了产生有效的连接行.

select emp.ename,dept.loc from emp,dept
where emp.deptno=10

输出的是笛卡尔积结果:

ename loc
CLARK NEW YORK
CLARK DALLAS
CLARK CHICAGO
CLARK BOSTON
KING NEW YORK
KING DALLAS
KING CHICAGO
KING BOSTON
MILLER NEW YORK
MILLER DALLAS
MILLER CHICAGO
MILLER BOSTON

正确的sql:

select emp.ename,dept.loc from emp,dept
where emp.deptno=10 and emp.deptno = dept.deptno

输出:

ename loc
CLARK NEW YORK
KING NEW YORK
MILLER NEW YORK

数据准备, 7934 有两笔奖金:

DROP TABLE IF EXISTS emp_bonus;
CREATE TABLE emp_bonus (
  empno decimal(4,0)  NOT NULL,
  received date NOT NULL,
  type char(10) NOT NULL
);

INSERT INTO emp_bonus VALUES ('7934','2005-03-17','1');
INSERT INTO emp_bonus VALUES ('7934','2005-02-15','2');
INSERT INTO emp_bonus VALUES ('7839','2005-02-15','3');
INSERT INTO emp_bonus VALUES ('7782','2005-01-15','1');

查询在部门10中所有员工的工资和奖金, type字段代表奖金额, 1表示为员工工资的 10%, 2表示为员工工资的 20%, 3表示为员工工资的 30%,

select emp.empno,emp.ename,emp.sal,emp.deptno,emp.sal*
    case when emp_bonus.type='1' then 0.1
        when emp_bonus.type='2' then 0.2
        when emp_bonus.type='3' then 0.3
    end as bonus
from emp,emp_bonus
where emp.empno=emp_bonus.empno and emp.deptno=10

结果如下, 注意为了计算bonus, 调用了两次 MILLER:

empno ename sal deptno bonus
7782 CLARK 2450.00 10 245.000
7839 KING 5000.00 10 1500.000
7934 MILLER 1300.00 10 130.000
7934 MILLER 1300.00 10 260.000

计算sal,bonus总数:

select deptno,sum(sal),sum(bonus) from (
    select emp.empno,emp.ename,emp.sal,emp.deptno,emp.sal*
    case when emp_bonus.type='1' then 0.1
        when emp_bonus.type='2' then 0.2
        when emp_bonus.type='3' then 0.3
    end as bonus
from emp,emp_bonus
where emp.empno=emp_bonus.empno and emp.deptno=10
) x

结果中 sal的sum是错的,因为之前为了计算bonus, 调用了两次 MILLER

deptno sum(sal) sum(bonus)
10 10050.00 2135.000

使用disctinct 处理sal并不合适, 可能有不同时期的sal是相同的.

单独计算sal的和:

select sum(emp.sal) from emp
where emp.empno in (select empno from emp_bonus) and emp.deptno=10

3.9 处理了重复的问题, 外连接是为了处理空值的问题.

数据准备如下, 部门10中只有一名员工有奖金,其他员工是没有奖金的:

DROP TABLE IF EXISTS emp_bonus;
CREATE TABLE emp_bonus (
  empno decimal(4,0)  NOT NULL,
  received date NOT NULL,
  type char(10) NOT NULL
);

INSERT INTO emp_bonus VALUES ('7934','2005-03-17','1');
INSERT INTO emp_bonus VALUES ('7934','2005-02-15','2');

计算bonus


    select emp.empno,emp.ename,emp.sal,emp.deptno,emp.sal*
    case when emp_bonus.type='1' then 0.1
        when emp_bonus.type='2' then 0.2
        when emp_bonus.type='3' then 0.3
    end as bonus
from emp,emp_bonus
where emp.empno=emp_bonus.empno and emp.deptno=10

结果:

empno ename sal deptno bonus
7934 MILLER 1300.00 10 130.000
7934 MILLER 1300.00 10 260.000

处理方式同上, 单独计算sal 的和

select sum(emp.sal) from emp
where emp.empno in (select empno from emp_bonus) and emp.deptno=10

结果:

sum(emp.sal)
1300.00

这是有奖金的雇员对应的销售总额.

左连接:返回左表所有的行,即使右表没有匹配的行
右连接:返回右表所有的行,即使左表没有匹配的行

查询没有员工的部门,要列出所有部门

select dept.deptno,dept.dname,emp.deptno from dept left join emp on (emp.deptno=dept.deptno)

结果:

deptno dname deptno
20 RESEARCH 20
30 SALES 30
30 SALES 30
20 RESEARCH 20
30 SALES 30
30 SALES 30
10 ACCOUNTING 10
20 RESEARCH 20
10 ACCOUNTING 10
30 SALES 30
20 RESEARCH 20
30 SALES 30
20 RESEARCH 20
10 ACCOUNTING 10
40 OPERATIONS NULL

进一步:

select d_no ,d_name
from (
    select dept.deptno as d_no,dept.dname as d_name,emp.deptno as d_no_emp from dept left join emp on (emp.deptno=dept.deptno)
) X
where d_no_emp is null

结果:

d_no d_name
40 OPERATIONS

NULL值永远不会等于或不等于任何值，也包括NULL值自己

正文完

SQLCookbook中文版:第1章检索记录,第2章查询结果排序

SQLCookbook中文版:第3章操作多个表

3.1 多个表的记录的叠加: 行的叠加

3.2 组合相关的行: 使用where子句或者内连接

3.3 在两个表中查找共同的行: 两个表同一行的多个字段相同

3.4 从一个表中查找另一个表没有的值: 去除另一个表中存在的值,使用except 子句.

3.5 在一个表中查找与其他表不匹配的记录:反连接

3.6 向已经存在的查询中增加联接而不影响之前的联接

3.7 检测两个表中是否有相同的数据

3.8 识别和消除笛卡尔积

3.9 聚集与联接

3.10 聚集与外连接

3.11从多个表中返回丢失的数据

3.12 在运算和比较时使用 null值: 使用 coalesce 函数将null转为其他值

已阻挡的垃圾评论

SQLCookbook中文版:第3章 操作多个表

3.1 多个表的记录的叠加: 行的叠加

3.2 组合相关的行: 使用where子句或者内连接

3.3 在两个表中查找共同的行: 两个表同一行的多个字段相同

3.4 从一个表中查找另一个表没有的值: 去除另一个表中存在的值,使用except 子句.

3.5 在一个表中查找与其他表不匹配的记录:反连接

3.6 向已经存在的查询中增加联接而不影响之前的联接

3.7 检测两个表中是否有相同的数据

3.8 识别和消除笛卡尔积

3.9 聚集与联接

3.10 聚集与外连接

3.11从多个表中返回丢失的数据

3.12 在运算和比较时使用 null值: 使用 coalesce 函数将null转为其他值

已阻挡的垃圾评论

SQLCookbook中文版:第3章操作多个表