SQL 随笔记: 常见的表连接方式

news/2024/10/30 17:41:40/

文章目录

    • 1. 内连接(INNER JOIN)
    • 2. 外连接(OUTER JOIN)
        • 2.1 左外连接(LEFT JOIN)
        • 2.2 右外连接(RIGHT JOIN)
        • 2.3 全外连接(FULL JOIN)
    • 3. 交叉连接(CROSS JOIN)
    • 4. 自连接(SELF JOIN)
    • 5. 自然连接(NATURAL JOIN)
    • 6. 图示化对比
    • 7. 表格示例
      • 7.1 内连接(INNER JOIN)
      • 7.2 左外连接(LEFT JOIN)
      • 7.3 右外连接(RIGHT JOIN)
      • 7.4 全外连接(FULL JOIN)
      • 7.5 交叉连接(CROSS JOIN)
      • 7.6 自连接(SELF JOIN)
      • 7.7 自然连接(NATURAL JOIN)


表连接(Join)是数据库中常见的操作,用于将多个表的数据根据某个条件结合在一起,以获取满足条件的结果集。不同的连接方式会影响查询的结果集,了解每种连接的特性和应用场景有助于高效地获取所需数据。以下是几种常见的表连接方式:


1. 内连接(INNER JOIN)

  • 内连接 是最常用的连接方式,返回两个表中符合条件的匹配记录。如果某一行在任意一个表中没有匹配项,则不会包含在结果中。INNER JOIN 是 SQL 中的默认连接类型,即不指定连接类型时数据库通常会默认执行内连接。

  • 特点:只返回匹配的记录。

    示例语法

    sql">SELECT a.列名, b.列名 
    FROM1 AS a 
    INNER JOIN2 AS b 
    ON a.列名 = b.列名;
    

    或者:

    sql">SELECT a.列名, b.列名 
    FROM1 AS a 
    JOIN2 AS b 
    ON a.列名 = b.列名;  -- JOIN 是 INNER JOIN 的简写
    
    • 适用场景:当只需要返回两个表中都存在的匹配数据时使用。

2. 外连接(OUTER JOIN)

外连接根据数据匹配情况,将某些表中的数据无条件保留,具体分为三种类型:左外连接(LEFT JOIN)、右外连接(RIGHT JOIN)和全外连接(FULL JOIN)。

2.1 左外连接(LEFT JOIN)
  • 返回左表中的所有记录,即使在右表中没有匹配项,右表没有匹配的数据将显示为 NULL。左外连接确保左表的所有行都出现在结果集中,不管右表是否有对应的匹配记录。

    示例语法

    sql">SELECT a.列名, b.列名 
    FROM1 AS a 
    LEFT JOIN2 AS b 
    ON a.列名 = b.列名;
    

    或者:

    sql">SELECT a.列名, b.列名 
    FROM1 AS a 
    LEFT OUTER JOIN2 AS b 
    ON a.列名 = b.列名;  -- OUTER 可以省略
    
    • 适用场景:当需要返回左表中的所有数据,无论其在右表中是否有匹配时使用。
2.2 右外连接(RIGHT JOIN)
  • 与左外连接相反,返回右表中的所有记录,即使左表中没有匹配项。右外连接确保右表的所有行出现在结果集中,即使左表没有匹配的记录。

    示例语法

    sql">SELECT a.列名, b.列名 
    FROM1 AS a 
    RIGHT JOIN2 AS b 
    ON a.列名 = b.列名;
    

    或者:

    sql">SELECT a.列名, b.列名 
    FROM1 AS a 
    RIGHT OUTER JOIN2 AS b 
    ON a.列名 = b.列名;  -- OUTER 可以省略
    
    • 适用场景:当需要返回右表中的所有数据时使用。
2.3 全外连接(FULL JOIN)
  • 返回两个表中的所有记录,如果没有匹配项,未匹配的一方数据列显示为 NULL。全外连接会保留左表和右表中的所有记录。在不支持 FULL JOIN数据库(如 MySQL)中,可以通过 UNION 两个查询来实现,如 LEFT JOINRIGHT JOIN 的联合。特别地,UNION 默认去除重复项,如果希望保留所有记录,可以使用 UNION ALL

    示例语法

    sql">SELECT a.列名, b.列名 
    FROM1 AS a 
    FULL JOIN2 AS b 
    ON a.列名 = b.列名;
    

    或者(在不支持 FULL JOIN 的数据库中):

    sql">SELECT a.列名, b.列名 
    FROM1 AS a 
    LEFT JOIN2 AS b 
    ON a.列名 = b.列名
    UNION
    SELECT a.列名, b.列名 
    FROM1 AS a 
    RIGHT JOIN2 AS b 
    ON a.列名 = b.列名;
    
    • 适用场景:当需要返回两个表中的所有数据时,无论匹配情况。

3. 交叉连接(CROSS JOIN)

  • 交叉连接 返回两个表的笛卡尔积,即没有条件地将每个表的每一条记录组合起来。结果集的行数等于两个表的行数相乘,这意味着结果集的规模可能非常大。交叉连接生成的组合较多,因此在数据量大的表上使用时需要特别小心,避免产生大量冗余数据。

    示例语法

    sql">SELECT a.列名, b.列名 
    FROM1 AS a 
    CROSS JOIN2 AS b;
    
    • 适用场景:当需要生成所有可能的组合时使用,典型应用包括测试数据生成或数据分析中的所有组合情况。注意:谨慎使用交叉连接,以免导致性能问题

4. 自连接(SELF JOIN)

  • 自连接 用于将一个表与自身进行连接,特别适用于处理层级结构(如员工和经理之间的关系),或对同一表中的不同行进行比较。自连接通常用于处理表中层次化的关系或复杂的自身比较,例如查询员工与其经理的关系。

    示例语法

    sql">SELECT a.列名 AS Employee, b.列名 AS Manager 
    FROM 表名 AS a 
    INNER JOIN 表名 AS b 
    ON a.列名 = b.列名;
    
    • 适用场景:当需要对同一表中的不同记录进行比较时使用,如在同一张表中查询员工与其经理的关系或处理其他层次化数据。

5. 自然连接(NATURAL JOIN)

  • 自然连接 根据两个表中相同名称的列自动进行匹配连接,不需要显式指定 ON 条件。这种简化带来便利,但也可能有较高的风险,尤其当两表中存在同名但含义不同的列时,可能会导致意外的连接结果。因此,使用自然连接时应确保同名列在语义上是一致的,以避免误连接。

    示例语法

    sql">SELECT 列名 
    FROM1 
    NATURAL JOIN2;
    
    • 适用场景:当两个表有相同名称的列,并且希望根据这些列自动进行匹配连接时使用。注意:需确保同名列的语义和数据类型一致,避免产生误连接。

6. 图示化对比

为了更好地说明内连接、左外连接、右外连接和全外连接的区别,以下图表展示了这些连接方式的包含关系:

                 全外连接┌─────────────┬─────────────┐│ 左外连接    │ 右外连接     │└─────────────┴─────────────┘内连接

在内连接中,只返回匹配的部分(中间重叠部分);左外连接则返回左表全部数据,并显示右表未匹配项为 NULL;右外连接返回右表全部数据,左表未匹配项为 NULL;全外连接则返回两个表中的所有数据,显示未匹配项为 NULL

7. 表格示例

以下为每种 SQL 表连接方式的示例示范,通过示例可以直观了解连接方式的不同输出效果。

假设我们有两个表 employeesdepartments,其结构如下:

表1:员工(employees)

idnamedepartment_id
1Alice1
2Bob2
3CharlieNULL
4David3

表2:部门(departments)

iddepartment_name
1HR
2Finance
3IT
4Marketing

7.1 内连接(INNER JOIN)

查询每位员工所在的部门名称(只显示有匹配的部门记录的员工)。

sql">SELECT employees.name, departments.department_name 
FROM employees 
INNER JOIN departments 
ON employees.department_id = departments.id;

结果

namedepartment_name
AliceHR
BobFinance
DavidIT

说明Charliedepartment_idNULL,因此没有显示在结果中。

7.2 左外连接(LEFT JOIN)

查询所有员工和他们的部门名称,即使没有匹配的部门记录。

sql">SELECT employees.name, departments.department_name 
FROM employees 
LEFT JOIN departments 
ON employees.department_id = departments.id;

结果

namedepartment_name
AliceHR
BobFinance
CharlieNULL
DavidIT

说明Charlie 没有匹配的部门,因此 department_name 显示为 NULL

7.3 右外连接(RIGHT JOIN)

查询所有部门以及对应的员工,即使某个部门中没有员工。

sql">SELECT employees.name, departments.department_name 
FROM employees 
RIGHT JOIN departments 
ON employees.department_id = departments.id;

结果

namedepartment_name
AliceHR
BobFinance
DavidIT
NULLMarketing

说明Marketing 部门没有员工,因此 name 显示为 NULL

7.4 全外连接(FULL JOIN)

查询所有员工和部门信息,返回未匹配项(对于不支持 FULL JOIN数据库,使用 UNION 替代)。

sql">SELECT employees.name, departments.department_name 
FROM employees 
FULL JOIN departments 
ON employees.department_id = departments.id;

结果

namedepartment_name
AliceHR
BobFinance
CharlieNULL
DavidIT
NULLMarketing

说明CharlieMarketing 均显示出来,尽管没有对应的匹配项。

7.5 交叉连接(CROSS JOIN)

生成员工和部门的所有组合(笛卡尔积)。

sql">SELECT employees.name, departments.department_name 
FROM employees 
CROSS JOIN departments;

结果

namedepartment_name
AliceHR
AliceFinance
AliceIT
AliceMarketing
BobHR
BobFinance
BobIT
BobMarketing
CharlieHR
CharlieFinance
CharlieIT
CharlieMarketing
DavidHR
DavidFinance
DavidIT
DavidMarketing

说明:所有员工和所有部门的组合都生成了 16 条记录(4 个员工 × 4 个部门)。

7.6 自连接(SELF JOIN)

查询 employees 表中员工及其直接上级(假设每个员工有一个 manager_id 列)。

假设 employees 表新增了一个 manager_id 列,数据如下:

表1:员工(employees)

idnamedepartment_idmanager_id
1Alice1NULL
2Bob21
3CharlieNULL2
4David31

查询每位员工和他们的经理:

sql">SELECT e1.name AS employee, e2.name AS manager
FROM employees e1
LEFT JOIN employees e2
ON e1.manager_id = e2.id;

结果

employeemanager
AliceNULL
BobAlice
CharlieBob
DavidAlice

说明:Alice 是顶层,没有经理,所以 manager 显示为 NULL

7.7 自然连接(NATURAL JOIN)

自然连接会自动匹配同名列(假设 employeesdepartments 表中 department_id 列同名)。

sql">SELECT name, department_name 
FROM employees 
NATURAL JOIN departments;

结果

namedepartment_name
AliceHR
BobFinance
DavidIT

说明Charlie 没有 department_id 值,因而没有出现在结果中。


http://www.ppmy.cn/news/1543151.html

相关文章

Java多线程编程基础

目录 编写第一个多线程程序 1. 方式一 : 继承Thread类, 重写run方法 2. 方式二: 实现Runnable接口, 重写run方法 3. 方式三: 使用Lambda表达式 [匿名内部类] [Lambda表达式] 在上个文章中, 我们了解了进程和线程的相关概念. 那么, 在Java中, 我们如何进行多线程编程呢? …

政务网站如何选择SSL证书?JoySSL提供一年期免费证书!

政务网站作为政府与公众沟通的重要桥梁,其安全性至关重要。选择合适的SSL证书,是确保政务网站数据安全、提升公众信任的关键。JoySSL,作为国内领先的数字证书提供商,我们为政务网站提供了一年期免费SSL证书,助力政务网…

SQLite 数据库设计最佳实践

SQLite特点 SQLite是一款功能强大的 轻量级嵌入式数据库 ,具有以下显著特点: 体积小 :最低配置仅需几百KB内存,适用于资源受限环境。 高性能 :访问速度快,运行效率高于许多开源数据库。 高度可移植 :兼容多种硬件和软件平台。 零配置 :无需复杂设置,开箱即用。 自给自…

在浏览器里就可以运行的本地AI模型 - 一键去除图片背景AI

前言 浏览器的功能越来越强大, 从Chrome 113 开始, 谷歌把WebGPU引入到了浏览器中, 通过WebGPU的API, 可以直接访问本机电脑的GPU资源. 既然GPU资源可以在浏览器里运行, 给AI模型推理等带来了便利, 使得一些AI模型可以直接在浏览器里运行. 本文主要介绍介绍以下WebGPU的基本概…

利用前向勾子获取神经网络中间层的输出并将其进行保存(示例详解)

代码示例: # 激活字典,用于保存每次的中间特征 activation {}# 将 forward_hook 函数定义在 upsample_v2 外部 def forward_hook(name):def hook(module, input, output):activation[name] output.detach()return hookdef upsample_v2(in_channels, o…

抓取和分析JSON数据:使用Python构建数据处理管道

引言 在大数据时代,电商网站如亚马逊、京东等已成为数据采集的重要来源。获取并分析这些平台的产品信息可为市场分析、价格比较等提供数据支持。然而,由于网站数据通常以JSON格式动态加载,且限制较多(如IP限制、反爬机制&#xf…

echarts实现 水库高程模拟图表

需求背景解决思路解决效果index.vue 需求背景 需要做一个水库高程模拟的图表&#xff0c;x轴是水平距离&#xff0c;y轴是高程&#xff0c;需要模拟改水库的形状 echarts 图表集链接 解决思路 配合ui切图&#xff0c;模拟水库形状 解决效果 index.vue <!--/*** author:…

【Python爬虫实战】网络爬虫完整指南:网络协议OSI模型

网络爬虫完整指南&#xff1a;从协议基础到实践应用 什么是网络协议&#xff1f; **网络协议&#xff08;Network Protocol&#xff09;**是指计算机网络中设备和设备之间进行通信的规则和约定。它定义了数据传输的格式、顺序、传输方法和错误处理机制&#xff0c;使不同设备和…