Oracle学习笔记（三）。连接查询。等值连接，非等值连接，自连接，LEFT OUTER JOIN，RIGHT JOIN，FULL JOIN，自然连接，内连接 INNERJOIN

傻啦猫@_@

已于 2022-02-06 11:01:43 修改

阅读量3k

点赞数 3

分类专栏： oracle 文章标签： oracle 数据库 sql

于 2022-01-22 18:47:32 首次发布

本文链接：https://blog.csdn.net/weixin_45842494/article/details/122641584

版权

oracle 专栏收录该内容

16 篇文章 3 订阅

订阅专栏

文章目录

1. 什么是多表查询
2. 多表链接方式
3. 多表连接语法
4. 等值连接-内连接
5. 非等值连接
- 5.1非等值连接
6. 自连接
- 6.1 什么是自连接
7. 外连接
8. SQL99中的交叉连接
9 SQL99 中的自然连接(NATURAL JOIN)
10 SQL99中的内连接
- 10.1示例
11. 子查询

Oracle多表查询

备注：本文中使用到的sql为HR用户提供的数据和表。HR用户介绍查看以下链接。

https://blog.csdn.net/weixin_45842494/article/details/122528264

1. 什么是多表查询

当查询的数据并不是来源一个表时，需要使用多表链接操作完成查询。根据不同表中的数据之间的关系查询相关联的数据。

在这里插入图片描述

2. 多表链接方式

内连接：链接两个表，通过相等或不等判断链接列，称为内连接。在内连接中典型的连接运算有 = 或 <> 之类的比较运算符。包括等值连接和自然连接。

等值连接

非等值连接

自连接

SQL99：交叉连接（CROSS JOIN）

SQL99：内连接（INNER JOIN）

SQL99：自然连接（NATURAL JOIN）

外连接：在两个表之间的连接，返回内连接的结果，同时还返回不匹配的行的左（或右）表的连接，称为左（或右）连接。返回连接的结果，同时还返回左和右连接，称为全连接。

左外连接

右外连接

全外连接

子查询：当一个查询是另一个查询的条件时，称之为子查询。

3. 多表连接语法

3.1 语法结构

使用一个连接从多个表中查询数据。

在这里插入图片描述

在WHERE字句中写连接条件
当多个表中有相同列名时，将表名或者表的别名作为列名的前缀。

3.2 定义连接

当数据从多表中查询时，要使用连接（JOIN）条件。一个表中的行按照存在于相应列中的值被连接到另一个表中的行。

3.3 原则

在写一个连接表的select语句时，在列名前面用表名或者表的别名可以使语义清楚，并且加快了数据库访问。
为了链接n个表在一起，最少需要n-1个链接条件。例如，为了链接4个表，最少需要3个链接条件。

4. 等值连接-内连接

4.1 什么是等值连接

等值连接也被称为简单连接（simple joins）或内连接（inner joins）。是通过等号来判断连接条件中的数据值是相匹配。

4.2 抉择矩阵

是通过行与列来分析一个查询的方式。

例如：如果你想显示同一个部门所有名字为Taylor的雇员的名字和部门名称，可以写出下面的决策矩阵。

在这里插入图片描述

示例：

查询所有雇员名字以及他们所在的部门名称。

select last_name,department_name from employees e,departments d where e.employee_id = d.department_id;

4.3 使用AND操作符附加搜索条件

除连接之外，还可以要求用WHERE子句在连接中限制一个或多个表中的行。

4.4使用表别名

使用表别名简化查询语句的长度。

4.4.1表别名

可以使用表别名代替表名。就象列别名给列另一个名字一样。表别名有助于保持SQL代码较小，因此使用的存储器也少。

4.4.2使用表别名原则

表别名最多可以有30个字符，但短一些更好。
如果在FROM子句中表别名被用于指定的表，那么在整个SELECT语句中都可以使用表别名。
表别名应该是有意义的。.
表别名只对当前的SELECT语句有效。

4.5多于两个表的连接

为了连接n个表，你最少需要n-1 个连接条件。例如，为了连接3个表，最少需要两个连接。

5. 非等值连接

5.1非等值连接

一个非等值连接是一种不使用相等(=)作为连接条件的查询。如!=、>、<、>=、<=、BETWEEN AND等都是非等值链接的条件判断。

6. 自连接

6.1 什么是自连接

使用一个表连接它自身的操作。

7. 外连接

7.1什么是外连接(LEFT | RIGHT | FULL JOIN )

外连接是指查询出符合连接条件的数据同时还包含孤儿数据。左外链接包含左表的孤儿数据，右外连接包含右表的孤儿数据，全外连接包含两个表中的孤儿数据。

7.2孤儿数据(Orphan Data)

孤儿数据是指被连接的列的值为空的数据。

7.3外连接类型

左外(LEFT OUTER JOIN):包含左表的孤儿数据。
右外(RIGHT OUTER JOIN):包含右表的孤儿数据。
全外(FULL OUTER JOIN):包含两个表中的孤儿数据。

7.4 SQL99中的外连接

SQL99外连接语法格式:

用LEFT OUTER JOIN | RIGHT OUTER JOIN | FULL OUTER JOIN定义连接类型，用ON子句创建连接条件。

7.4. 1左外链接(LEFT OUTER JOIN)

7.4.1.1示例

用左外链接查询雇员名字以及他们所在的部门名称，包含那些没有部门的雇员。

select e.last_name,d.department_name from employees e left outer join departments d on e.department_id = d.department_id;
返回（部分）：
LAST_NAME                 DEPARTMENT_NAME
------------------------- ------------------------------
Gietz                     Accounting
Higgins                   Accounting
Grant                     
107 rows selected

说明：Grant这个雇员没有部门，是一个孤儿数据。

7.4.2右外链接(RIGHT OUTER JOIN)

示例：

用右外链接查询雇员名字以及他们所在的部门名称，包含那些没有雇员的部门。

select e.last_name,d.department_name from employees e right outer join departments d on e.department_id = d.department_id;
返回（部分）：
LAST_NAME                 DEPARTMENT_NAME
------------------------- ------------------------------
Gietz                     Accounting
Higgins                   Accounting
                          Treasuryl
122 rows selected

说明：Treasuryl这个部门还没有雇员，是一个孤儿数据

7.4.3 全外连接（FULL OUTER JOIN）

用全外链接查询雇员名字以及他们所在的部门名称，包含那些没有雇员的部门以及没有部门的雇员。

select e.last_name,d.department_name from employees e full outer join departments d on e.department_id = d.department_id;
返回（部分）：
LAST_NAME                 DEPARTMENT_NAME
------------------------- ------------------------------
Baer                      Public Relations
Higgins                   Accounting
Gietz                     Accounting
Grant                     
                          NOC
                          Manufacturing
123 rows selected

7.5 0racle扩展的外连接

在Oracle数据库中对外连接中的左外与右外连接做了扩展，可以简化外连接的语法。通过在连接条件的后侧使用(+)来表示是否显示孤儿数据，有(+)表示不显示孤儿数据而另一侧则显示孤儿数据。但是该种写法仅能在Oracle数据库中使用。

示例一：

查询雇员名字以及他们所在的部门名称，包含那些没有雇员的部门。

select e.last_name,d.department_name from employees e ,departments d where e.department_id(+) = d.department_id;
返回（部分）：
LAST_NAME                 DEPARTMENT_NAME
------------------------- ------------------------------
Gietz                     Accounting
Higgins                   Accounting
                          Treasury
122 rows selected

说明：相当于正常写了一个内连接查询，不显示孤儿数据的写个(+)，要显示的不用写。Treasury是个孤儿数据。

示例二：

查询雇员名字以及他们所在的部门名称，包含那些没有部门的雇员。

select e.last_name,d.department_name from employees e ,departments d where e.department_id = d.department_id(+);
返回（部分）：
LAST_NAME                 DEPARTMENT_NAME
------------------------- ------------------------------
Gietz                     Accounting
Higgins                   Accounting
Grant                     
107 rows selected

Grant是个孤儿数据。

8. SQL99中的交叉连接

CROSS JOIN子句导致两个表的交叉乘积
该连接和两个表之间的笛卡尔乘积是一样的。

在这里插入图片描述

示例：

查询Employees表与Departments表的笛卡尔乘积

select e.last_name,d.department_id from employees e cross join departments d;
返回（部分）：
LAST_NAME                 DEPARTMENT_ID
------------------------- -------------
Taylor                              270
Taylor                              270
Tobias                              270
2889 rows selected

说明：我们使用多表连接查询，如果不加任何条件，返回的也是笛卡尔乘积。

上面的sql语句相当于:
select e.last_name,d.department_id from employees e,departments d;

9 SQL99 中的自然连接(NATURAL JOIN)

NATURAL JOIN子句基于两个表之间有相同名字的所有列。
它从两个表中选择在所有的匹配列中有相等值的行。
如果有相同名字的列的数据类型不同，返回一个错误。

9.1使用自然连接需要注意

1.如果做自然连接的两个表的有多个字段都满足有相同名称类型，那么他们会被作为自然连接的条件。

2.如果自然连接的两个表仅是字段名称相同,但数据类型不同，那么将会返回一个错误。

3.由于oracle中可以进行这种非常简单的natural join,我们在设计表时对具有相向含义的字段需要考虑到使用相同的名字和数据类型。

9.2示例：

查询部门ID，部门名称以及他们所在的城市。

select d.department_id,d.department_name,l.city from departments d natural join locations l;
返回（部分）：
DEPARTMENT_ID DEPARTMENT_NAME                CITY
------------- ------------------------------ ------------------------------
          250 Retail Sales                   Seattle
          260 Recruiting                     Seattle
          270 Payroll                        Seattle
27 rows selected

说明：以上sql相当于：
select d.department_id,d.department_name,l.city from departments d , locations l where d.location_id = l.location_id;
区别就是少了where条件语句。因为自然连接的两个表的有多个字段都满足有相同名称类型，那么他们会被作为自然连接的条件。

9.3用USING 子句创建连接

当有多个列匹配时，用USING子句匹配唯一的列。
如果某列在USING中使用，那么在引用该列时不要使用表名或者别名。
NATURAL JOIN和USING子句是相互排斥的。

9.3.1示例

查询location_ id为1800的部门名称以及他们所在的城市名称，指定location_ id 为连接列。

select d.department_name,l.city from departments d join locations l using (location_id) where location_id = 1800;
返回（部分）：
DEPARTMENT_NAME                CITY
------------------------------ ------------------------------
Marketing                      Toronto

说明：使用了using子句，就不能出现natural，这里只写了join。并且where子句中的location_id没有写限定哪个表的location_id 。

10 SQL99中的内连接

内连接(INNER JOIN):内连接通过INNER JOIN来建立两个表的连接。在内连接中使用INNER JOIN作为表的连接，用ON子句给定连接条件。INNER JOIN语句在性能上其他语句没有性能优势。

10.1示例

查询雇员id为202的雇员名字，部门名称，以及工作的城市。

答案一：(等值连接)

select e.last_name,d.department_name,l.city from employees e,departments d,locations l where e.department_id = d.department_id and d.location_id = l.location_id and e.employee_id = 202;
返回：
LAST_NAME                 DEPARTMENT_NAME                CITY
------------------------- ------------------------------ ------------------------------
Fay                       Marketing                      Toronto

答案二：（内连接）

select e.last_name,d.department_name,l.city from employees e inner join departments d on e.department_id = d.department_id inner join locations l on d.location_id = l.location_id and e.employee_id = 202;
返回：
LAST_NAME                 DEPARTMENT_NAME                CITY
------------------------- ------------------------------ ------------------------------
Fay                       Marketing                      Toronto

答案三：在内连接中使用USING子句定义等值连接

select e.last_name,d.department_name,l.city from employees e inner join departments d using(department_id) inner join locations l using(location_id) where e.employee_id = 202;
返回：
LAST_NAME                 DEPARTMENT_NAME                CITY
------------------------- ------------------------------ ------------------------------
Fay                       Marketing                      Toronto

11. 子查询

11.1 子查询介绍

11.1.1 什么是子查询

子查询是一个select语句，它是嵌套在另一个select语句中的子句。
在这里插入图片描述
可以用组合两个查询的方法解决这个问题，放置一个查询到另一个查询中。内查询或子查询返回一个值给外查询或主查询。使用一个子查询相当于执行两个连续查询并且用第一个查询的结果作为第二个查询的搜索值。

11.1.2 子查询语法
在这里插入图片描述

子查询（内查询）在主查询之前执行一次。
子查询的结果被用于主查询（外查询）。

可以将子查询放在许多SQL子句中，包括：

WHERE子句
HAVING子句
FROM子句

11.2 使用子查询

11.2.1 使用子查询的原则

子查询放在圆括号中
将子查询放在比较条件的右边
在单行子查询中用单行运算符，在多行子查询中用多行运算符

示例：

谁的薪水比Abel高

答案一：（内连接查询）

select em.last_name,em.salary from employees abel,employees em where abel.last_name = 'Abel' and  em.salary > abel.salary;
返回（部分）：
LAST_NAME                     SALARY
------------------------- ----------
King                        24000.00
Kochhar                     17000.00
Higgins                     12008.00
10 rows selected

答案二：（子查询）

select em.last_name,em.salary from employees em where em.salary > (select m.salary from employees m where m.last_name = 'Abel');
返回：
同上

11.3 子查询的类型

在这里插入图片描述

单行子查询：子查询语句返回一行的查询
多行子查询：子查询语句返回多行的查询

11.3.1 单行子查询

仅返回一行
使用单行比较符

主查询对子查询结果的单行比较运算符：

在这里插入图片描述

示例：

显示job_id与雇员141相同，并且薪水高于雇员143的那些雇员。

    select e.last_name,e.job_id,e.salary from employees e where e.job_id = (select em.job_id from employees em where em.employee_id = '141') and e.salary > (select emp.salary from employees emp where emp.employee_id = 143);
    返回（部分）：
    LAST_NAME                 JOB_ID         SALARY
    ------------------------- ---------- ----------
    Nayer                     ST_CLERK      3200.00
    Mikkilineni               ST_CLERK      2700.00
    Bissot                    ST_CLERK      3300.00
    11 rows selected

11.3.2 在子查询中使用组函数

在子查询中可以使用组函数。

示例：

显示所有薪水等于最低薪水的雇员的last_name，job_id，salary

select e.last_name,e.job_id,e.salary from employees e where e.salary = (select min(salary) from employees);
返回：
LAST_NAME                 JOB_ID         SALARY
------------------------- ---------- ----------
Olson                     ST_CLERK      2100.00

11.3.3 带子查询的HAVING子句

可以在WHERE子句中使用子查询，也可以在HAVING子句中使用子查询

示例：

显示所有最低薪水小于部门50的最低薪水的部门号和最低薪水。

select em.department_id,min(em.salary) from employees em group by em.department_id having min(em.salary) > (select min(e.salary) from employees e where e.department_id = 50);
返回（部分）：
DEPARTMENT_ID MIN(EM.SALARY)
------------- --------------
          100           6900
           30           2500
           10           4400
11 rows selected

11.4 什么是子查询错误？

在这里插入图片描述

使用子查询的一个常见错误就是单行子查询返回了多行。

11.5 多行子查询（ANY，ALL，IN）

返回多于一行
使用多行比较符

主查询对子查询的多行比较运算符

在这里插入图片描述
在条件中也可以使用NOT取反。

11.5.1 在多行子查询中使用IN

示例：

查找各部门收入为部门最低的那些雇员，显示他们的名字，薪水以及部门ID。

select e.last_name,e.department_id,e.salary from employees e where e.salary in (select min(em.salary) from employees em group by em.department_id);
返回（部分）：
LAST_NAME                 DEPARTMENT_ID     SALARY
------------------------- ------------- ----------
Perkins                              50    2500.00
Whalen                               10    4400.00
Fay                                  20    6000.00
Mavris                               40    6500.00
Baer                                 70   10000.00
Gietz                               110    8300.00
26 rows selected

15.5.2 在多行子查询中使用ANY运算符
在这里插入图片描述

< ANY意思是小于最大值。> ANY意思是大于最小值。

示例：

显示工作岗位不是IT_PROG的雇员，并且这些雇员的薪水少于IT_PROG工作岗位的雇员的ID，名字，工作岗位和薪水。

select e.employee_id, e.last_name,e.job_id from employees e where e.job_id <> 'IT_PROG' and e.salary < any (select em.salary from employees em where em.job_id = 'IT_PROG');
返回（部分）：
EMPLOYEE_ID LAST_NAME                 JOB_ID
----------- ------------------------- ----------
        154 Cambrault                 SA_REP
        160 Doran                     SA_REP
        111 Sciarra                   FI_ACCOUNT
76 rows selected

15.5.3 在多行子查询中使用ALL运算符
在这里插入图片描述

< ALL意思是小于最小值，> ALL意思是大于最大值。

示例：

显示那些薪水低于工作岗位IT_PROG的最低薪水，并且工作岗位不是IT_PROG的所有雇员。

select em.employee_id,em.last_name,em.job_id,em.salary from employees em where em.job_id <> 'IT_PROG' and em.salary < all (select e.salary from employees e where e.job_id = 'IT_PROG');
返回（部分）：
EMPLOYEE_ID LAST_NAME                 JOB_ID         SALARY
----------- ------------------------- ---------- ----------
        128 Markle                    ST_CLERK      2200.00
        136 Philtanker                ST_CLERK      2200.00
        132 Olson                     ST_CLERK      2100.00
44 rows selected