Oracle中的表连接

痛定思痛。 2024-03-22 13:12 84阅读 0赞

**目录**

一、定义：

二、连接方式：

2.1 内连接（INNER JOIN）：

2. 2外连接（OUTER JOIN）：

2.2.1左外连接：

2.2.2右外连接：

2.2.3全外连接：

2.3 自连接（SELF JOIN）：

2.4交叉连接（Cross Join）：

三、全外连接的另一种灵魂疑问

3.1集合解法

3.2非空列为空

--------------------

## 一、定义： ##

表连接是通过表与表之间的关联字段,一次查询多张表的数据,如果用n张表,至少需要n-1个关联条件。表连接将两个或多个表中的数据按照一定条件进行关联，从而形成一个新的数据集。

## 二、连接方式： ##

Oracle支持多种表连接方式，包括内连接、外连接、自连接、交叉连接。

下面描述下各种连接方式：

### 2.1 内连接（INNER JOIN）： ###

只返回两个表中符合条件的行，如果没有匹配的行，则不返回任何数据。内连接是显示两张表共有的数据。

内连接语法：

SELECT column1, column2, ...
    FROM table1
    INNER JOIN table2
    ON table1.column = table2.column;

例如，我们有两个表，一个是订单表orders，一个是客户表customers，它们有一个共同的字段customer\_id，我们可以使用内连接查询出订单表中的所有信息以及对应的客户信息：

SELECT A.order_id, A.order_date, B.customer_name
    FROM orders A
    INNER JOIN customers B
    ON A.customer_id = B.customer_id;

### 2. 2外连接（OUTER JOIN）： ###

返回两个表中符合条件的行以及没有匹配的行，其中：

左外连接（LEFT (OUTER) JOIN）返回左表中所有的行，如果右表中没有匹配的行，则返回NULL值（显示两张表共有的数据以及左表独有的数据右表用空补齐）；

右外连接（RIGHT (OUTER) JOIN）返回右表中所有的行，如果左表中没有匹配的行，则返回NULL值（显示两张表共有的数据以及右表独有的数据左表用空补齐）；

全外连接（FULL (OUTER) JOIN）返回两个表中所有的行，如果没有匹配的行，则返回NULL值（显示两张表共有数据以及各自独有数据互相用空补齐）。

下面用两张表分析下各种外连接：已知员工表emp和部门表dept，注意查看各表数据并对比！！

select * from emp ;

![ba9466bac68943d1b5b129a28ed62e6d.png][]

因为要和部门表dept进行表连接，所以只关注两个表的关联字段即部门编号deptno。员工表emp中一共有14条员工信息，有3个员工是10部门，5个员工是20部门，6个员工是30部门。

select * from dept;

![1673c523eac9408d8f0b6726dfc01d33.png][]

部门表一共有四条信息，即有4个部门：10、20、30、40。

进行下面各种外连接的探讨！！！

#### 2.2.1左外连接： ####

--语法如下：
    SELECT column1, column2, ...
    FROM table1
    LEFT OUTER JOIN table2
    ON table1.column = table2.column;
    
    1.where
    select *
    from 左表 z,右表 y
    where z.列=y.列(+)
    
    2.left join
    select *
    from 左表 z
    left join 右表 y
    on z.列=y.列

对员工表emp和部门表dept进行左外连接：

select *
    from emp e
    left join dept d
    on e.deptno=d.deptno;

运行结果如图所示：

![d4964424f5824f9c9d3ee0e0925567ff.png][]

> 分析： 通过上图发现结果中少了40部门的部门信息，而左表员工表信息全部都有，和员工表关联的部门表共有信息也存在（10，20，30部门），唯独部门表中40部门的信息没有。所以当使用左外连接（left join）时，它的运行返回结果包含了两张表共有的信息以及左表独有的信息，而右表独有的数据会为null。

举例运用：

例如，我们有两个表，一个是订单表orders，一个是客户表customers，它们有一个共同的字段customer\_id，要查询所有客户信息以及对应的订单信息：

先分析： 查询所有客户信息以及对应的订单信息，客户是存在不变的，但是客户可能购买订单可能没有订单即为null,而题目要求是查询所有客户的对应订单，所以我们可以使用左外连接完成：

SELECT A.customer_id, A.customer_name, B.order_id, B.order_date
    FROM customers A
    LEFT  JOIN orders B
    ON A.customer_id = B.customer_id;

#### 2.2.2右外连接： ####

--语法如下：
    SELECT column1, column2, ...
    FROM table1
    RIGHT  JOIN table2
    ON table1.column = table2.column;
    
    1.where
    select *
    from 左表 z,右表 y
    where z.列(+)=y.列
    
    2.right join
    select *
    from 左表 z
    right join 右表 y
    on z.列=y.列

对员工表emp和部门表dept进行右外连接：

select *
    from emp e
    right join dept d
    on e.deptno=d.deptno;

运行结果如图所示：

![818ebb6c36984943890460f280692c27.png][]

> 分析： 通过上图发现结果中一共反馈了15条信息，其中左表员工表信息全部都有，右表部门表的信息也存在（10，20，30，40部门），而左表员工表中没有40部门的员工信息时，会用空null进行补齐。所以当使用右外连接（right join）时，它的运行返回结果包含了两张表共有的信息以及右表独有的信息，而左表没有的数据会用空null补齐。

举例运用：

假设有两个表，一个是学生表（students），包含学生的ID、姓名和年龄；另一个是成绩表（scores），包含学生的ID和成绩。现在需要查询所有有成绩的学生的姓名和成绩，如果学生没有成绩，则成绩为NULL。(理解分析：要清楚成绩表中所有成绩都会有对应的学生，此时可以使用右外连接完成)

SELECT s.name, sc.score
    FROM students s
    RIGHT JOIN scores sc
    ON s.id = sc.id;

该语句中，使用了RIGHT JOIN将学生表和成绩表进行连接，连接条件是学生的ID。连接后得到的结果集中，会包含所有有成绩的学生和他们的成绩，如果学生没有成绩，则成绩为NULL。

如果想要查询某个学生的成绩，可以在WHERE子句中添加条件，如下所示：

SELECT s.name, sc.score
    FROM students s
    RIGHT JOIN scores sc
    ON s.id = sc.id
    WHERE s.id = 1;

该语句会得到学生ID为1的学生的姓名和成绩，如果该学生没有成绩，则成绩为NULL。

#### 2.2.3全外连接： ####

--语法如下：
    SELECT column1, column2, ...
    FROM table1
    FULL OUTER JOIN table2
    ON table1.column = table2.column;
    
    select *
    from 左表 z 
    full join 右表 y
    on z.列=y.列

首先往员工表emp中插入一条数据方便对比，如下：

--向员工表插入一条数据，员工属于50部门的信息
    insert into emp(empno,
                    ename,
                    job,
                    mgr,
                    hiredate,
                    sal,
                    comm,
                    deptno)
            values(8888,'shuaige','CLERK',7777,date'2023-5-6',9999,200,50);
    
    --查询emp
    select * from emp;

![e8a5ea976e3748f1bbabbccb31dcf8d3.png][]

执行后发现一共15条数据，50部门已经插入进去。对员工表emp和部门表dept进行全外连接：

select *
    from emp e
    full join dept d
    on e.deptno=d.deptno;

运行结果如图所示：

![a17e403e96944818bdeceefd706b77be.png][]

> 分析： 通过上图发现结果中一共反馈了16条信息，其中左表员工表信息全部都有（15条名员工信息），右表部门表的信息也存在（10，20，30，40部门），而左表员工表中没有40部门的员工信息时，会用空null进行补齐，而右表部门表中没有50部门的信息时，会用空null进行补齐。所以当使用全外连接（full join）时，它的运行返回结果是包含了两张表共有的信息以及各自独有的信息互相用空null补齐。

假设有两个表，一个是学生表（students），包含学生的ID、姓名和年龄；另一个是成绩表（scores），包含学生的ID和成绩。现在需要查询所有学生的姓名和成绩，如果学生没有成绩，则成绩为NULL。

可以使用如下的全外连接语句：

SELECT s.name, sc.score
    FROM students s
    FULL JOIN scores sc
    ON s.id = sc.id;

该语句中，使用了FULL JOIN将学生表和成绩表进行连接，连接条件是学生的ID。连接后得到的结果集中，会包含所有学生和他们的成绩，如果学生没有成绩，则成绩为NULL。

如果想要查询某个学生的成绩，可以在WHERE子句中添加条件，如下所示：

SELECT s.name, sc.score
    FROM students s
    FULL JOIN scores sc
    ON s.id = sc.id
    WHERE s.id = 1;

该语句会得到学生ID为1的学生的姓名和成绩，如果该学生没有成绩，则成绩为NULL。

### 2.3 自连接（SELF JOIN）： ###

将一个表作为两个独立的表进行连接，可以用于比较同一表中的不同数据。

自连接语法：

SELECT column1, column2, ...
    FROM table1 t1
    INNER JOIN table1 t2
    ON t1.column = t2.column;

例如，我们有一个员工表employees，其中有一个字段manager\_id表示员工的上级领导，我们可以使用自连接查询出所有员工的信息以及对应的领导信息：

SELECT e.employee_id, e.employee_name, m.employee_name AS manager_name
    FROM employees e
    INNER JOIN employees m
    ON e.manager_id = m.employee_id;

### 2.4交叉连接（Cross Join）： ###

将两个表中的所有记录进行组合，不考虑它们之间的关联关系。交叉连接可以使用CROSS JOIN语句来实现。

语法如下：

SELECT column1, column2, ...
    FROM table1
    CROSS JOIN table2;

其中，table1和table2是要进行交叉连接的两个表，column1、column2等是要查询的列名。

假设有两个表，一个是学生表（students），包含学生的ID、姓名和年龄；另一个是课程表（courses），包含课程的ID和名称。现在需要查询每个学生选修的所有课程。

可以使用如下的交叉连接语句：

SELECT s.name, c.course_name
    FROM students s
    CROSS JOIN courses c;

该语句会将学生表和课程表中的所有记录进行组合，得到一个所有可能组合的结果集。如果学生表中有3条记录，课程表中有4条记录，则结果集会包含12条记录，每个学生都会与每个课程进行组合。

如果想要查询某个学生选修的所有课程，可以在WHERE子句中添加条件，如下所示：

SELECT s.name, c.course_name
    FROM students s
    CROSS JOIN courses c
    WHERE s.id = 1;

该语句会得到学生ID为1的学生选修的所有课程。

> ***注意：***
> 
> 交叉连接会产生笛卡尔积现象。交叉连接是一种没有任何条件的连接方式，它会将一个表中的每一行与另一个表中的每一行进行组合，从而产生所有可能的组合情况，也就是笛卡尔积。因此，在使用交叉连接时，需要谨慎考虑其可能产生的结果，并通过限制条件或其他方式来避免产生不必要的笛卡尔积。

## 三、全外连接的另一种灵魂疑问 ##

已知全外连接（FULL (OUTER) JOIN）是返回两个表中所有的行，如果没有匹配的行，则返回NULL值，显示两张表共有数据以及各自独有数据互相用空补齐。那怎么做才能只显示各自独有的数据呢？

### 3.1集合解法 ###

数据集合中有一个集合是差集，具体使用可以参看文章[http://t.csdn.cn/5RbpJ][http_t.csdn.cn_5RbpJ]

集合A包含\{1, 2, 3\}，集合B包含\{2, 3, 4\}，则A MINUS B的结果为\{1\}，B MINUS A的结果为\{4\}。利用差集就可以显示各自独有的数据，语句如下：

select *
    from emp e
    full join dept d
    on e.deptno=d.deptno   
    minus
    select *
    from emp e
    join dept d
    on e.deptno=d.deptno;

解析：先用全外连接法将两张表关联找出他们的全部数据结果，再用内连接法将两张表关联找出它们共有的数据结果，最后进行差集计算得到各自独有的数据，运行结果如图所示：

![c2c9ab61e24b4551a8ee0cf2e2040999.png][]

### 3.2非空列为空 ###

语句如下：

select *
    from emp e
    full join dept d
    on e.deptno=d.deptno
    where e.deptno is null or d.deptno is null;

解析：先对两张表进行全外关联显示全部数据，然后加where约束条件，让左表的一个非空列为空或者右表的一个非空列为空，比如：

select *
    from emp e
    full join dept d
    on e.deptno=d.deptno
    where e.deptno  is null ;

执行结果如下：

![6399e3446c4b4ff186e298e7a0e18e22.png][]

select *
    from emp e
    full join dept d
    on e.deptno=d.deptno
    where d.deptno  is null;

执行结果如下：

![2cb35580887f4edb8f89e540a7c868f5.png][]

选择一个非空列为空时，就可以查找出另一张表中独有的数据。

> ***总结：***
> 
> 为了查找出两张表各自独有的数据时，可以使用集合差集的方法解决，但是要注意MINUS运算符只能用于两个查询结果都具有相同的列数和数据类型的情况下进行操作。
> 
> 另一种方法是 利用where条件让【**左表的一个非空列 is null or 一个右表的非空列 is null**】，通常情况下会选择表的主键列。

[ba9466bac68943d1b5b129a28ed62e6d.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/323291771d89409f937253756d4a35af.png
[1673c523eac9408d8f0b6726dfc01d33.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/af16d89f2e0e40c488433185f95c110a.png
[d4964424f5824f9c9d3ee0e0925567ff.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/195f93f7f2d5408a9fbd0bd101dede83.png
[818ebb6c36984943890460f280692c27.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/5d137e9c949b4d5a9efe8694b6c13d87.png
[e8a5ea976e3748f1bbabbccb31dcf8d3.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/56d074383d9c4ebb8fd31a9e5aa52bc2.png
[a17e403e96944818bdeceefd706b77be.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/e3eaf84e63d14397837fac374ab2880e.png
[http_t.csdn.cn_5RbpJ]: http://t.csdn.cn/5RbpJ
[c2c9ab61e24b4551a8ee0cf2e2040999.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/5e678262760e4f76aa5de1eb86c0052a.png
[6399e3446c4b4ff186e298e7a0e18e22.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/57d0fcddcfe4482f992afb7fe92caf73.png
[2cb35580887f4edb8f89e540a7c868f5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/22/54bc6de7358749d8a287a4cb23851d60.png