oracl:多表查询＞＞表连接[内连接,外连接,交叉连接,自连接,自然连接,等值连接和不等值连接]

 SQL（Structured Query Language，结构化查询语言）是一种用于管理和操作关系数据库的标准编程语言。

sql分类: 

数据查询语言（DQL - Data Query Language）

查询的关键词 select

多表查询>>表连接

表连接: 把2个或多张表左右合并为一张表

表连接的方式: 内连接, 外连接, 自连接, 自然连接, 交叉连接

其中外连接分为: 左外连接, 右外连接, 全外连接

连接的关键词: join  on 

连接关键词书写位置: from 之后, where之前

内连接 join  on 

表中的数据,满足关联条件可以关联上的数据才能被保留下来,没有主表从表之分

格式:  表1 join 表2 on 关联条件

示例: 查询员工工资小于3000的部门信息

注意: 对于在2张表都出现的字段,使用前在字段前加上表名以指定字段, 比如e.deptno表示的emp表的deptno字段,d.deptno表示的是dept表中的deptno字段 

确定表

select 后面需要展现的查询结果是:  部门信息>>即dept表的所有字段的信息>>dept表

where 条件过滤中需要用到的字段sal >>  emp表

确定连接方式: 满足条件的数据才出现>>内连接

连接条件的确定

通过查看2张表可以知道列DEPTNO是表EMP的外键(FK_DEPTNO), 也是表DEPT的主键(PK_DEPT), 所以可以使用e.deptno=d.deptno作为2张表的连接条件

确定过滤条件where

 工资大于3000, 即where sal < 3000

表和表之间的关系

没有distinct的查询结果中同样的部门信息会出现多次

查询员工工资大于3000的部门信息, 可知最终查询结果只需要每个部门出现一次即可,所以select 后面加destinct

外连接 

外连接: 左外连接, 右外连接, 全外连接

除了全外连接,左/右外连接需要区分主表和从表, 主表的数据会全部保留, 从表的数据只有符合关联连接的数据才会出现在查询结果中, 主表中, 没有和从表关联上的数据也会出现,但在对应的从表中是空值

连接格式

左外连接: 表1  left   [outer]  join 表2 on 连接条件;      left  jion之前是主表

右外连接: 表1  right   [outer]  join 表2 on 连接条件;   right  join 后面的是主表

全外连接:  表1  full  [outer]  join 表2 on 连接条件    

示例: 查询没有员工的部门信息

分析:

确定表

需要查询的是部门信息>>dept表,

条件是部门里没有员工, 即where emp.empno is null >>emp表

关联条件: emp.deptno=dept.deptno

连接方式: emp表的主键是EMPNO员工编号,"没有员工">> >该部门信息不会在emp表中出现, 即emp表中没有相应的deptno数据 >> 不满足关联条件 emp.deptno=dept.deptno 的部门信息需要出现在结果集中, 使用外/左/右连接

确定主表: 不满足关联条件但是需要出现在结果集中的信息是部门信息>>>主表: dept

条件: where emp.empno is null

>>左连接

>>右连接

>>外连接

示例: 全表连接和左/右表连接的不同

>>全连接

>>左连接 (从表dept中未满足关联条件的数据没有出现)

注意: 表连接是左右拼接为一张表, 当关联条件的字段也在查询结果中出现时,该关联字段出现2次

自然连接

 格式: 表1  natrural   jion  表2

一种特殊的内连接（INNER JOIN），它不需要明确指定连接条件。自然连接会自动使用两个表中具有相同名称的列作为连接条件。这些列的值必须相等，才能将对应的行连接起来。

注意: 不能写on 关联条件, 但是表中必须有可以用于关联的条件列(字段名称和数据类型均相同)

示例

>>自然连接

Oracle不允许在NATURAL JOIN中直接使用带有表别名的列进行过滤，因为NATURAL JOIN隐式地使用两个表中具有相同名称的列作为连接条件，而这些列在连接后会被重复，导致歧义。

解决: 使用内连接或套一个子查询

交叉连接 cross join

将两个表中的每一行与另一个表中的每一行配对，生成笛卡尔积（Cartesian Product）。这意味着结果集中的行数将是两个表行数的乘积。

语法: 表1 cross join 表2 

示例

select * from emp, dept;

查询结果: 先是dept表的第一行数据和emp表的每一行数据都进行连接, 再到dept表的第二行数据和emp表的每一行数据进行连接, 以此类推.所以结果集行数= emp表的行数*dept表的行数

自连接

通过取别名的方式让表自己连接自己, 可以把同一列的数据经过自连接后转到同一行(列转行)

示例: 原表

示例: 自连接>>获取每个城市及其所属省份的关系，从而实现层级结构的数据展示。

通过取别名的方式把areas表变成p表和c表, 再用关联条件把这2张表进行连接

SQL92标准的连接方式

没有join on 关键词,

使用where写连接条件, 连接效果和内连接相同; 连接条件为恒等式(比如1=1)时,形成交叉连接

示例 : 内连接效果

示例: 交叉连接效果 

外连接和内连接

当2张表可以使用内连接时, 意味着使用全/左/右连接都可以,并且不需要关注主表从表, 因为最终满足关联条件的数据都会返回

示例 : 查询员工工资小于3000的部门信息

左连接如下

右连接如下 

全外连接如下

 

连接条件的确定

通常基于两个表中具有关联性的列来确定

注意: 关联条件的字段不一定相同, 但是含义必须相同

等值连接和不等值连接

等值连接: 基于两个表中具有相等值的列来连接表 =

不等值连接/范围连接

1.使用不等运算符（如 <, >, <=, >=, <> 或 !=）来连接两个表的条件。

2. 使用between 值1 and 值 2  ; 值1<值2

3. 使用in / not in 

4.like

where和on的区别

示例