Logo

菜单

小熊奶糖(BearCandy)
小熊奶糖(BearCandy)
发布于 2024-05-23 / 28 阅读
0
0

MySQL 内连接，外连接，交叉连接

#mysql#数据库#大数据技术

内连接（INNER JOIN）和外连接（OUTER JOIN）是数据库查询中用于合并两个或多个表中数据的关键概念。它们基于相关表之间的关联字段来实现数据的联合。下面将详细解释这两种连接类型。

内连接（INNER JOIN）

内连接是最常见的连接类型，它返回两个或多个表中有匹配的记录。换句话说，当表之间有关联字段，并且这些字段在某些记录中具有匹配的值时，内连接只返回这些匹配的记录。
例如，假设有两个表：员工表（包含员工信息）和 部门表（包含部门信息）。如果使用内连接来查询员工及其所属部门的信息，那么只有在 员工表中存在对应 部门表中部门ID的记录时，这些记录才会被返回。

内连接（Inner Join）是关系数据库中最常见的连接类型之一，用于从两个或更多表中返回所有匹配的行。内连接基于提供的连接条件，仅当连接列的值在两个表中相匹配时，才会返回行。如果某行在其中一个表中没有匹配项，那么该行不会出现在结果集中。

基本概念

内连接的关键在于指定连接条件，通常通过 ON 子句完成，它指定了两个表中哪些列需要进行比较以找到匹配的行。

语法

基本的SQL内连接语法如下：

SELECT column_list
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;
  • column_list 是你想从结果集中选择的列名列表。
  • table1 和 table2 是要进行连接的表名。
  • column_name 是定义连接条件的列名，确保两边表中的相应列值相等。

示例

假设我们有两个表，一个是 Orders表，一个是 Customers表，我们想找出每个订单对应的客户名称。

Orders 表:
+----------+------------+
| OrderID  | CustomerID |
+----------+------------+
| 1        | 3          |
| 2        | 1          |
| 3        | 2          |
+----------+------------+

Customers 表:
+------------+-----------+
| CustomerID | CustomerName|
+------------+-----------+
| 1          | Alice     |
| 2          | Bob       |
| 3          | Charlie   |
+------------+-----------+

查询语句:
SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID = Customers.CustomerID;

结果

查询结果会展示所有在 Orders表中能找到对应 CustomerID的订单及其客户名称。

+----------+-------------+
| OrderID  | CustomerName|
+----------+-------------+
| 1        | Charlie     |
| 2        | Alice       |
| 3        | Bob         |
+----------+-------------+

性能考量

  • 索引优化：在连接列上建立索引可以显著提高内连接的性能，尤其是处理大规模数据时。
  • 连接顺序：数据库管理系统会优化连接顺序，但理解表的大小和连接条件的选择性有助于手动调整以达到最优性能。

使用场景

  • 当你需要从两个或多个表中检索相关数据，且这些表通过某个共享字段相互关联时。
  • 进行数据分析，需要合并不同数据源的信息。

总的来说，内连接是数据联合查询的基础，适用于大多数需要根据共享键匹配数据的情况。

SQL示例

SELECT *
FROM 员工表
INNER JOIN 部门表 ON 员工表.部门ID = 部门表.部门ID;

外连接（OUTER JOIN）

外连接分为三种：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）。

  1. 左外连接（LEFT OUTER JOIN）：返回左表（FROM子句之前的表）的所有记录，即使在右表中没有匹配的记录。如果左表的记录在右表中没有匹配，那么相关联的结果将为NULL。
    例如，如果我们想要查询所有员工，即使某些员工没有分配到部门，也要显示他们，那么可以使用左外连接。

SQL示例

SELECT *
FROM 员工表
LEFT JOIN 部门表 ON 员工表.部门ID = 部门表.部门ID;
  1. 右外连接（RIGHT OUTER JOIN）：与左外连接相反，右外连接返回右表的所有记录，即使在左表中没有匹配的记录。如果右表的记录在左表中没有匹配，那么相关联的结果将为NULL。

SQL示例

SELECT *
FROM 员工表
RIGHT JOIN 部门表 ON 员工表.部门ID = 部门表.部门ID;
  1. 全外连接（FULL OUTER JOIN）：返回左表和右表中的所有记录。当左表没有匹配的记录时，会在左表的位置显示NULL；当右表没有匹配的记录时，会在右表的位置显示NULL。

SQL示例

SELECT *
FROM 员工表
FULL JOIN 部门表 ON 员工表.部门ID = 部门表.部门ID;

总结

  • 内连接只返回两个表中匹配的记录。
  • 左外连接返回左表的所有记录，右表中没有匹配的记录则返回NULL。
  • 右外连接返回右表的所有记录，左表中没有匹配的记录则返回NULL。
  • 全外连接返回左表和右表的所有记录，没有匹配的记录在对方位置显示NULL。
    这些连接类型在数据库设计和查询中非常重要，它们决定了查询结果集中包含哪些数据。正确选择连接类型可以确保获取到准确和完整的数据集。

内连接（INNER JOIN）和外连接（OUTER JOIN）是数据库查询中用于合并两个或多个表中数据的两种常见连接类型。它们基于相关表之间的关联字段来实现数据的联合。下面将详细解释这两种连接类型。

内连接（INNER JOIN）

内连接是最常见的连接类型，它返回两个或多个表中有匹配的记录。换句话说，当表之间有关联字段，并且这些字段在某些记录中具有匹配的值时，内连接只返回这些匹配的记录。

例如，假设有两个表：员工表（包含员工信息）和 部门表（包含部门信息）。如果使用内连接来查询员工及其所属部门的信息，那么只有在 员工表中存在对应 部门表中部门ID的记录时，这些记录才会被返回。

SQL示例


SELECT *

FROM 员工表

INNER JOIN 部门表 ON 员工表.部门ID = 部门表.部门ID;

外连接（OUTER JOIN）

外连接分为三种：左外连接（LEFT OUTER JOIN）、右外连接（RIGHT OUTER JOIN）和全外连接（FULL OUTER JOIN）。

  1. 左外连接（LEFT OUTER JOIN）：返回左表（FROM子句之前的表）的所有记录，即使在右表中没有匹配的记录。如果左表的记录在右表中没有匹配，那么相关联的结果将为NULL。

    例如，如果我们想要查询所有员工，即使某些员工没有分配到部门，也要显示他们，那么可以使用左外连接。

SQL示例


SELECT *

FROM 员工表

LEFT JOIN 部门表 ON 员工表.部门ID = 部门表.部门ID;
  1. 右外连接（RIGHT OUTER JOIN）：与左外连接相反，右外连接返回右表的所有记录，即使在左表中没有匹配的记录。如果右表的记录在左表中没有匹配，那么相关联的结果将为NULL。

SQL示例

在 SQL 的 LEFT JOIN 语句中，"左表"和"右表"的称呼是基于JOIN关键字左边和右边的表来确定的。

在你的查询例子中：

SELECT * FROM 员工表 LEFT JOIN 部门表 ON 员工表.部门ID = 部门表.部门ID;
  • 左表 是：员工表（员工表），这是因为在 LEFT JOIN 关键字左边的表。
  • 右表 是：部门表（部门表），因为这个表位于 LEFT JOIN 关键字的右边。

LEFT JOIN 操作会返回左表（员工表）的所有记录，即使在右表（部门表）中没有匹配的记录。如果存在匹配，则结果显示来自右表的匹配数据；如果没有匹配，则右表对应的部分显示为 NULL。


SELECT *

FROM 员工表

RIGHT JOIN 部门表 ON 员工表.部门ID = 部门表.部门ID;
  1. 全外连接（FULL OUTER JOIN）：返回左表和右表中的所有记录。当左表没有匹配的记录时，会在左表的位置显示NULL；当右表没有匹配的记录时，会在右表的位置显示NULL。

SQL示例


SELECT *

FROM 员工表

FULL JOIN 部门表 ON 员工表.部门ID = 部门表.部门ID;

总结

  • 内连接只返回两个表中匹配的记录。
  • 左外连接返回左表的所有记录，右表中没有匹配的记录则返回NULL。
  • 右外连接返回右表的所有记录，左表中没有匹配的记录则返回NULL。
  • 全外连接返回左表和右表的所有记录，没有匹配的记录在对方位置显示NULL。

这些连接类型在数据库设计和查询中非常重要，它们决定了查询结果集中包含哪些数据。正确选择连接类型可以确保获取到准确和完整的数据集。

MySQL中的交叉连接（Cross Join），也称为笛卡尔积（Cartesian Product），是一种将两个或多个表中的每一行进行配对以生成所有可能组合的查询操作。在执行交叉连接时，MySQL不考虑表之间的任何关联条件，因此结果集中每个表的第一行会与另一个表中的每一行组合，第一表的第二行再与另一个表中的每一行组合，以此类推。

基本语法

交叉连接的基本语法如下：

SELECT column_list
FROM table1
CROSS JOIN table2;

其中，column_list 是你想要从结果集中选择的列名列表，table1 和 table2 是你要进行交叉连接的表名。

示例

假设我们有两个表，一个是 students表，包含学生信息，另一个是 courses表，包含课程信息。

  • students表有3行数据（3个学生）。
  • courses表有2行数据（2门课程）。

如果我们执行一个交叉连接查询，结果集将会是两表行数的乘积，即6行，显示每个学生与每门课程的组合。

SELECT students.name, courses.title
FROM students
CROSS JOIN courses;

结果可能类似于：

+---------+-----------+
| name    | title     |
+---------+-----------+
| Alice   | Math      |
| Alice   | Science   |
| Bob     | Math      |
| Bob     | Science   |
| Charlie | Math      |
| Charlie | Science   |
+---------+-----------+

性能考虑

由于交叉连接生成的结果集大小是参与连接的表行数的乘积，当处理大表时，结果集可能会非常庞大，导致查询效率极低，消耗大量内存和CPU资源。因此，在实际应用中，除非明确需要获取所有可能的组合，否则应谨慎使用交叉连接，并考虑是否可以通过添加适当的过滤条件（使用 WHERE子句）来限制结果集的大小，或者是否应该使用其他类型的连接（如内连接、外连接等）来满足查询需求。

使用场景

尽管交叉连接可能导致性能问题，但在某些特定场景下它是有用的，比如：

  • 当你需要列出所有可能的组合，以便进一步根据某些条件筛选时。
  • 在数据分析中，为了探索不同维度间的各种组合可能性。
  • 在创建复杂的报告或统计信息时，需要基础的全排列数据集。

总之，交叉连接是一个强大的工具，但应当根据具体情况谨慎使用。

第5章 多表操作.pptx
https://www.alipan.com/s/ncMMHnK36jr
点击链接保存，或者复制本段内容，打开「阿里云盘」APP ，无需下载极速在线查看，视频原画倍速播放。

MySQL子查询
MySQL实验报告9-多表查询

评论