理解数据库表之间的五种连接方式

于 2024-09-04 10:33:46 发布
阅读量569 收藏 10
点赞数 9
文章标签： java 数据库 mysql oracle sql 面试
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/m0_65013257/article/details/141886720
版权

1.INNER JOIN(交集)[内连接]

在 SQL 中，INNER JOIN 是一种用于从两个或多个表中检索数据的方法，它会返回两个表中具有匹配值的行。换句话说，只有那些在被连接的表中都存在匹配行的数据才会出现在结果集中。

何时使用 INNER JOIN

当你想要从两个或更多表中获取数据，并且这些表之间有一个共同的列（通常是外键关系），你可以使用 INNER JOIN 来实现。这通常用于关联具有相同数据（如主键或外键）的表。

语法

INNER JOIN 的基本语法如下：

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

这里的 ON 子句定义了连接条件，即哪些列应该相互匹配。

示例

让我们通过一个例子来更好地理解 INNER JOIN。

假设我们有两个表，一个是 Customers，另一个是 Orders。Customers 表包含客户的详细信息，而 Orders 表包含订单的详细信息。Customers 表中的 CustomerID 是 Orders 表中的外键，用来关联这两个表。

Customers 表：

+----+-----------+-------------+---------+
| ID | FirstName | LastName    | Address |
+----+-----------+-------------+---------+
| 1  | John      | Doe         | Street1 |
| 2  | Jane      | Smith       | Street2 |
| 3  | Mike      | Johnson     | Street3 |
+----+-----------+-------------+---------+

Orders 表：

+----+-------------+----------+
| ID | OrderNumber | Customer |
+----+-------------+----------+
| 1  | 1001        | 1        |
| 2  | 1002        | 2        |
| 3  | 1003        | 3        |
| 4  | 1004        | 1        |
+----+-------------+----------+

在这个例子中，Orders 表中的 Customer 列是 Customers 表中的 ID 的外键。

如果我们想要获取每个客户的订单详情，我们可以使用 INNER JOIN 如下：

SELECT Customers.FirstName, Customers.LastName, Orders.OrderNumber
FROM Customers
INNER JOIN Orders
ON Customers.ID = Orders.Customer;

这个查询将返回：

+-----------+-------------+-------------+
| FirstName | LastName    | OrderNumber |
+-----------+-------------+-------------+
| John      | Doe         | 1001        |
| Jane      | Smith       | 1002        |
| Mike      | Johnson     | 1003        |
| John      | Doe         | 1004        |
+-----------+-------------+-------------+

这里的结果只包含了 Customers 和 Orders 表中匹配的行。例如，John Doe 在 Orders 表中有两条记录（OrderNumber 1001 和 1004），所以他在这张结果表中也出现了两次。

使用场景

INNER JOIN 常用于：

  1. 连接两个或多个表以检索相关联的数据：当一个表的主键作为另一个表的外键时，INNER JOIN 可以用来连接这些表并获取相关的记录。
  2. 过滤非匹配的记录：只返回两个表中都有对应数据的行。
  3. 多表连接：在更复杂的情况下，可以使用多个 INNER JOIN 语句来连接三个或更多的表。

总结

INNER JOIN 是一种非常常用的 SQL 连接类型，它可以帮助你结合多个表中的数据，只返回那些在所有参与的表中都有匹配项的行。这对于构建报告、分析数据以及任何需要合并不同表中的信息的应用都非常有用。

2.LEFT JOIN（左全及左右交集）, RIGHT JOIN（右全及左右交集）【外连接】

在 SQL 中，LEFT JOIN 和 RIGHT JOIN 是两种常用的连接类型，它们分别用于从两个表中检索数据，并且保留左侧表或右侧表中的所有行。

2.1 LEFT JOIN

LEFT JOIN（左连接）会返回所有左侧表中的行，并且返回右侧表中匹配的行。如果右侧表中没有匹配的行，则结果集中的右侧表的列将包含 NULL。

语法
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;
示例

假设我们有两个表：Customers 和 Orders。

Customers 表：

+----+-----------+-------------+---------+
| ID | FirstName | LastName    | Address |
+----+-----------+-------------+---------+
| 1  | John      | Doe         | Street1 |
| 2  | Jane      | Smith       | Street2 |
| 3  | Mike      | Johnson     | Street3 |
| 4  | Sarah     | White       | Street4 |
+----+-----------+-------------+---------+

Orders 表：

+----+-------------+----------+
| ID | OrderNumber | Customer |
+----+-------------+----------+
| 1  | 1001        | 1        |
| 2  | 1002        | 2        |
| 3  | 1003        | 3        |
| 4  | 1004        | 1        |
+----+-------------+----------+

如果我们使用 LEFT JOIN 来连接这两个表：

SELECT Customers.ID, Customers.FirstName, Customers.LastName, Orders.OrderNumber
FROM Customers
LEFT JOIN Orders
ON Customers.ID = Orders.Customer;

结果将是：

+----+-----------+-------------+-------------+
| ID | FirstName | LastName    | OrderNumber |
+----+-----------+-------------+-------------+
| 1  | John      | Doe         | 1001        |
| 1  | John      | Doe         | 1004        |
| 2  | Jane      | Smith       | 1002        |
| 3  | Mike      | Johnson     | 1003        |
| 4  | Sarah     | White       | NULL        |
+----+-----------+-------------+-------------+

在这个结果集中，Customers 表中的所有行都被包含进来。对于 Customers 表中没有匹配的行（例如 Sarah），Orders 表中的列将包含 NULL。

2.2 RIGHT JOIN

RIGHT JOIN（右连接）则相反，它会返回所有右侧表中的行，并且返回左侧表中匹配的行。如果左侧表中没有匹配的行，则结果集中的左侧表的列将包含 NULL。

语法
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;
示例

使用同样的 Customers 和 Orders 表：

SELECT Customers.ID, Customers.FirstName, Customers.LastName, Orders.OrderNumber
FROM Customers
RIGHT JOIN Orders
ON Customers.ID = Orders.Customer;

结果将是：

+----+-----------+-------------+-------------+
| ID | FirstName | LastName    | OrderNumber |
+----+-----------+-------------+-------------+
| 1  | John      | Doe         | 1001        |
| 2  | Jane      | Smith       | 1002        |
| 3  | Mike      | Johnson     | 1003        |
| 1  | John      | Doe     		 | 1004        |
+----+-----------+-------------+-------------+

2.3. 左连接与右连接的对称性

值得注意的是，LEFT JOIN 和 RIGHT JOIN 是对称的，也就是说，如果你交换左右表的位置，结果将是对称的。例如：

SELECT Customers.ID, Customers.FirstName, Customers.LastName, Orders.OrderNumber
FROM Customers
RIGHT JOIN Orders
ON Customers.ID = Orders.Customer;

等价于：

SELECT Orders.Customer, Customers.FirstName, Customers.LastName, Orders.OrderNumber
FROM Orders
LEFT JOIN Customers
ON Orders.Customer = Customers.ID;

使用场景

  • LEFT JOIN：当你希望保留左侧表中的所有行，并且展示右侧表中的匹配行（如果没有匹配，则显示 NULL）。
  • RIGHT JOIN：当你希望保留右侧表中的所有行，并且展示左侧表中的匹配行（如果没有匹配，则显示 NULL）。

总结

LEFT JOIN 和 RIGHT JOIN 是 SQL 中非常有用的连接类型，它们可以帮助你在连接两个表时保留一侧表中的所有行。具体选择哪种连接类型取决于你希望保留哪一侧表中的所有行。如果你不确定应该使用 LEFT JOIN 还是 RIGHT JOIN，可以考虑使用 INNER JOIN 来仅获取两个表中的匹配行，或者使用 FULL JOIN（如果可用）来获取两个表中的所有行。

3.FULL JOIN(OUTER JOIN)【左右全集及交集】

MySQL 本身并没有直接支持 FULL JOIN（全外连接）的语法。然而，你可以通过其他方式来模拟 FULL JOIN 的效果。通常有两种方法来实现这一点：

  1. 使用 UNION 和两个 LEFT JOIN 或 RIGHT JOIN
  2. 使用 UNION ALL 和两个 LEFT JOIN 或 RIGHT JOIN

方法一：使用 UNION 和两个 LEFT JOIN 或 RIGHT JOIN

这种方法使用两次 LEFT JOIN 或 RIGHT JOIN，然后通过 UNION 将结果合并。这种方法可能会导致重复行，因此需要使用 UNION 而不是 UNION ALL 来去除重复行。

示例:

假设有两个表 table1 和 table2，我们需要基于 common_column 进行 FULL JOIN。

表 table1：

+---------+-----------------+
| id      | common_column   |
+---------+-----------------+
| 1       | A               |
| 2       | B               |
| 3       | C               |
+---------+-----------------+

表 table2：

+---------+-----------------+
| id      | common_column   |
+---------+-----------------+
| 4       | B               |
| 5       | D               |
+---------+-----------------+

使用 LEFT JOIN 和 RIGHT JOIN 并通过 UNION 合并：

(SELECT t1.id AS t1_id, t1.common_column, t2.id AS t2_id
 FROM table1 t1
 LEFT JOIN table2 t2 ON t1.common_column = t2.common_column)

UNION

(SELECT t1.id AS t1_id, t1.common_column, t2.id AS t2_id
 FROM table2 t2
 LEFT JOIN table1 t1 ON t1.common_column = t2.common_column)

结果：


+--------+-----------------+--------+
| t1_id  | common_column   | t2_id  |
+--------+-----------------+--------+
| 1      | A               | NULL   |
| 2      | B               | 4      |
| 3      | C               | NULL   |
| 4      | B               | 2      |
| 5      | D               | NULL   |
+--------+-----------------+--------+

4.CROSS JOIN（笛卡尔集）

在 SQL 中，CROSS JOIN（交叉连接）是一种特殊的连接类型，它会产生两个表中所有行的笛卡尔积（Cartesian product），即第一个表中的每一行与第二个表中的每一行进行配对。

什么是交叉连接？

当执行 CROSS JOIN 时，如果表 A 有 m 行，表 B 有 n 行，那么结果集将包含 m * n 行。每个结果行都是由 A 表的一行和 B 表的一行组合而成的。

语法

CROSS JOIN 的语法如下：

SELECT *
FROM table1
CROSS JOIN table2;

或者，由于 CROSS JOIN 的关键字 CROSS 可以省略，因此也可以写作：

SELECT *
FROM table1
JOIN table2;

示例

假设我们有两个表：orders 和 items，分别包含以下数据：

表 orders：

+----+----------+--------+
| id | order_no | status |
+----+----------+--------+
| 1  | 1001     | open   |
| 2  | 1002     | closed |
+----+----------+--------+

表 items：

+----+-----------------+--------+
| id | item_name       | price  |
+----+-----------------+--------+
| 1  | Laptop          | 1000   |
| 2  | Smartphone      | 800    |
| 3  | Headphones      | 200    |
+----+-----------------+--------+

如果我们执行 CROSS JOIN：

SELECT o.id AS order_id, o.order_no, i.id AS item_id, i.item_name
FROM orders o
CROSS JOIN items i;

结果将是：


+----------+----------+---------+------------+
| order_id | order_no | item_id | item_name  |
+----------+----------+---------+------------+
| 1        | 1001     | 1       | Laptop     |
| 1        | 1001     | 2       | Smartphone |
| 1        | 1001     | 3       | Headphones |
| 2        | 1002     | 1       | Laptop     |
| 2        | 1002     | 2       | Smartphone |
| 2        | 1002     | 3       | Headphones |
+----------+----------+---------+------------+

这里，表 orders 有 2 行，表 items 有 3 行，所以结果集共有 2 * 3 = 6 行。

使用场景

CROSS JOIN 主要有以下几个用途：

  1. 生成组合数据：当你需要生成所有可能的组合数据时，可以使用 CROSS JOIN。
  2. 测试目的：在测试环境中，为了生成测试数据，可以使用 CROSS JOIN 来生成大量的测试组合。
  3. 数据填充：在某些情况下，为了填充数据，可以使用 CROSS JOIN 生成所有可能的组合。

注意事项

  1. 性能问题：对于大型表，CROSS JOIN 可能会导致巨大的结果集，从而影响性能。因此，在生产环境中应谨慎使用。
  2. 明确意图：在实际应用中，很少需要生成笛卡尔积。如果确实需要生成组合数据，请确保这是有意为之，并且理解其后果。

示例代码

以下是一个具体的示例，展示如何使用 CROSS JOIN 生成所有可能的订单和商品组合：

-- 创建表 orders
CREATE TABLE orders (
  id INT,
  order_no INT,
  status VARCHAR(10)
);

-- 插入数据
INSERT INTO orders (id, order_no, status)
VALUES (1, 1001, 'open'), (2, 1002, 'closed');

-- 创建表 items
CREATE TABLE items (
  id INT,
  item_name VARCHAR(50),
  price INT
);

-- 插入数据
INSERT INTO items (id, item_name, price)
VALUES (1, 'Laptop', 1000), (2, 'Smartphone', 800), (3, 'Headphones', 200);

-- 执行 CROSS JOIN
SELECT o.id AS order_id, o.order_no, i.id AS item_id, i.item_name
FROM orders o
CROSS JOIN items i;

这个查询将生成表 orders 和表 items 的所有可能组合。

总结

CROSS JOIN 会产生两个表的笛卡尔积，即第一个表中的每一行与第二个表中的每一行进行配对。在实际应用中，CROSS JOIN 应该谨慎使用，特别是在处理大型数据集时。如果你确实需要生成所有可能的组合数据，CROSS JOIN 是一个有效的工具。

Czi橙
关注
  • 9
    点赞
  • 踩
  • 10
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
〖Python 数据库开发实战 - MySQL篇⑳〗- 数据表的内连接
易编橙 · 终身成长社群，相遇已是上上签！
07-30 4万+
该章节的内容为多表连接查询的内连接，因为 MySQL 是关系型数据库，数据是拆分重组在多个数据表里面的。所以我们势必要从多个数据表中提取数据，通过 SQL 语句的内连接与外连接就能够实现多表查询了。这部分内容是需要我们重点学习的，学习的过程中会穿插多种的案例来强化对表连接的语法的运用。（简单一点来说，其实就是从多张表里查询数据集，这一大段是我为了给摘要凑字数用的！）......
数据库表之间的关系
weixin_44540396的博客
06-28 2885
数据库表之间的关系 开发工具与关键技术：Visual Studio 2015 表与表之间的关系处理 作者：廖 茂 撰写时间：2019年06月26日 做项目最重要的就是数据库，数据库没做好，那么后面的功能实现就会出现问题，数据库创建时，表与表之间的关系至关重要，如果处理不好，那么将无法对表进行数据的插入与修改，表与表之间有着一对多、多对多的关系，现在先来看一对多的关系，表格如...
数据库表跟表之间的关系
m0_57756558的博客
11-21 1890
数据库设计就是规范和结构化数据库中的数据对象以及数据对象之间关系的过程
数据库的各种连接方式
dexter159的博客
06-08 1万+
1. ODBC数据库接口 ODBC即开放式数据库互连（Open Database Connectivity），是微软公司推出的一种实现应用程序和关系数据库之间通讯的接口标准。符合标准的数据库就可以通过SQL语言编写的命令对数据库进行操作，但只针对关系数据库。目前所有的关系数据库都符合该标准（如SQL Server，Oracle，Access，Excel等）。ODBC本质上是一组数据库访问API（应用程序编程接口），由一组函数调用组成，核心是SQL语句，其结构如图1.8所示： 图 1.8 ODBC数.
常用的数据库多表连接方法
心若向阳 无谓悲伤
08-23 9162
1、内连接   (1).from  a,b      语法：select a.x,b.y from a,b where a.id=b.id;     备注：返回所有满足where条件的行 (2).inner join        语法：select a.x from a inner join b on a.id=b.id      备注：inner 可以省略,一般都省略【和 from...
数据库的外键和主表从表以及连接查询
u010804417的博客
10-05 1万+
数据库中的外键和主表、从表主键和外键主键（主关键字，primary key）外键（外关键字，foreign key，公共关键字的其中一个）存在的必要性主表和从表单表 主键和外键 主键（主关键字，primary key） 关系型数据库中的一条记录中有若干个属性，若其中某一个属性组(注意是组)能唯一标识一条记录，该属性组就可以成为一个主键 。 （主键）属性可以唯一标识表中的某一条记录。表的主键可以由一个或多个关键字共同组成，并且主关键字的列不能包含空值。但是一个表只能有一个主关键字。 外键（外关键字，forei
数据库的连接形式
赵国闯 廊坊师范学院 信息技术提高班
11-26 3988
1、内联接（典型的联接运算，使用像 =  或 内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行。例如，检索 students和courses表中学生标识号相同的所有行。         2、外联接。外联接可以是左向外联接、右向外联接或完整外部联接。      在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：      1）LEFT  JOIN或LEFT OU
MySQL关于表连接的几种方式的举例说明
weixin_49561506的博客
11-06 3699
帮助读者理解MySQL中几种连接方式的区别，以便更灵活的使用。
数据库关系代数详解
Charon的博客
02-26 9万+
数据库关系代数 1. 关系代数的运算 1.1 传统的关系运算 传统的关系运算起源于数学的集合论，有下面几种： 笛卡尔积运算 差运算 交运算 并运算 1.2 专门的关系运算 选择 投影 连接 除运算 1.2.1 关系运算中的基础概念 在学习关系代数的时候，我们脑海中要时刻拥有一张表格，还有一些表格的参数，这张表格如下： 比如说我们每个人都见过成绩单，牢记以下的比喻 R就为整张成绩单 R[A1, A2, A3,…Ai] = R[语文，英语，数学……学科] t为某个同学 t [Ai] 就
基础篇：数据库 SQL 入门教程
热门推荐
LuciferLiu_DBA
09-12 35万+
超全SQL教程，收藏就完事了！
Oracle数据库的实例/表空间/用户/表之间关系简单讲解
09-08
本文将深入讲解Oracle数据库中实例、表空间、用户和表之间的关系。 首先，我们需要理解Oracle数据库的基本结构。一个完整的Oracle数据库由两大部分组成：Oracle数据库本身和数据库实例。数据库是存储在磁盘上的物理...
navicate数据库连接
12-30
- Navicat提供了一套完整的数据库设计和管理工具，包括数据表视图、图表视图、关系视图，让用户可以清晰地查看和理解数据库结构。 - 可以进行数据编辑，支持添加、修改、删除记录，以及执行SQL查询，以直观的方式...
JAVA多线程实现数据库之间的数据互导、连接池、及多表插入数据库功能
03-20
理解这两种方式的优缺点是基础，例如，实现Runnable接口更利于资源复用。 - **线程同步**：为了避免多线程环境中的数据竞争问题，Java提供了synchronized关键字、Lock接口（如ReentrantLock）以及各种同步工具类...
C++连接mysql数据库的两种方法小结
08-19
标题中提到的知识点是“C++连接mysql数据库的两种方法”，即讨论了两种不同的技术路径来实现C++程序与MySQL数据库之间的连接。首先，一种是使用ActiveX数据对象（ADO）连接，另一种是使用MySQL提供的API函数进行连接...
浅析synchronized锁升级的原理与实现 1
水w的博客
09-01 1191
浅析synchronized锁升级的原理与实现
在Java Web开发中，如何保障应用的安全性？请列举常见的安全策略和措施。请解释Spring框架中的事务管理，包括声明式事务和编程式事务。
最新发布
weixin_53180424的博客
09-03 675
在实际开发中，声明式事务管理因其非侵入性和易维护性而更受欢迎。它允许开发者将注意力集中在业务逻辑上，而不是事务管理的细节上。然而，在某些特殊场景下，编程式事务管理可能提供更灵活的控制力。因此，在选择事务管理方式时，应根据实际需求和场景来决定。
计算机毕业设计选题推荐-任务管理系统-项目任务分配系统-Java/Python项目实战
IT研究室的博客
09-03 565
随着信息化技术的迅猛发展，各类企业和组织越来越依赖于高效的任务管理系统以保证其日常运营的顺利进行。根据IDC（国际数据公司）发布的报告显示，全球企业在数字化转型上的投入已达数千亿美元，其中相当一部分资金用于提升企业内部的管理效率。任务管理系统作为企业信息化的重要组成部分，能有效帮助企业分配、监控和反馈任务，从而提高工作效率。然而，尽管市场上已有多种任务管理系统，它们在功能、用户体验和适用性上存在显著差异。
操作系统原子操作
zzt_is_me的博客
08-28 8680
所谓的原子操作就是不可被拆分的操作，对于多线程对全局变量进行操作时，就再也不用再线程锁了，和pthread_mutex_t保护作用是一样的，也是线程安全的，有些编译器在使用时需要加-march=i686编译参数。2、type只能是整数相关的类型，浮点型和自定义类型无法使用。功能：把value赋值给*ptr,并返回*ptr的旧值。功能：以上操作返回的是*ptr与value计算后的值。使用原子操作实现一个线程安全的无锁队列。1、该功能并不通用，有些编译器不支持。功能：以上操作返回的是*ptr的旧值。
MapStruct-Java实体转换利器
m0_63622279的博客
09-02 400
MapStruct 是一个用于生成映射器接口的代码生成库，主要用于简化 Java 对象之间的转换过程。在处理不同模型之间数据转换时，MapStruct 可以自动生成实现类减少手工转换逻辑的工作量。使用场景：各种模型之间的转换。BODTOVOPOJO引入mapstruct的pom依赖定义mapper接口（注意，这里不是MyBatis的Mapper哦），使用@Mapper注解标记，并在接口中声明映射方法。编写映射方法，（从xxxO转换为yyyO,模型之间的转换）在业务层中使用Mapper。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值