SQL中的多表关联查询

SQL中的多表关联查询

引用

CSDN

1.

https://blog.csdn.net/m0_69371690/article/details/146586654

在数据库开发中，多表关联查询是一个核心技能。本文将从主键、外键的基本概念出发，深入讲解SQL中的各种JOIN操作，包括INNER JOIN、LEFT JOIN、RIGHT JOIN和FULL OUTER JOIN，通过具体示例帮助读者掌握这些重要知识点。

在实际的业务中，通常我们数据是存储在多张表中，比如学生表中存储的是学生的信息、教师表中存储教师的信息、课程表中记录了课程的信息，如果我们需要查老师所教学生的课程情况，这时我们就需要三张表联合起来找到我们需要的信息。表关联的前提是它们之间有某种关系，即存在相同字段，通过相同的字段建立关联。

先补充一个知识点，就是表的主键与外键，它们是用于定义表与表之间关系的。

主键（Primary Key）是用于唯一标识一行数据的，它可以用于保证数据的唯一性和完整性，特点就是不能重复，而且主键的值不能为NULL。比如一个学校可能有好几个叫张三的，但是每个人的学号肯定是不重复的。所以我们在建表的时候，就可以将学生的ID设为主键，通过某个ID去找才能精确的获取到一条信息。通过主键，可以快速、准确地检索到表中的某行数据。我们在建表的时候，最好就指定主键，例如：

CREATE TABLE [dbo].[Student](
    [StudentNumber] [VARCHAR](10) PRIMARY KEY,
    [Name] [VARCHAR](10) NOT NULL,
    [Gender] [CHAR](2) NOT NULL,
    [Age] [INT] NOT NULL,
    [Class] [VARCHAR](10) NULL
)  

表建好后也可以添加主键，语法是：

ALTER TABLE 表名
ADD CONSTRAINT 主键名称 PRIMARY KEY (列名);  

当然，主键可以不止一列，比如有时表中没有哪一列的值是完全唯一的，但是某两列的组合是唯一的，我们可以将这两列作为一个联合主键。

外键（Foreign Key）是和主键对应的概念，它也是表中的某列，但是它的值是指向另外一个表的主键值，比如说教师编号它在我们的Teacher表中是主键，但同时课程表中也有教师编号TeacherNumber这一列，两个表中的TeacherNumber值是对应的。这时，TeacherNumber列是Teacher表的主键，但是是Course表中的一个外键。通过这种主键和外键建立表与表之间的关联关系。外键的特征是引用完整性，即外键的值必须是另一个表的主键值（只要你在建表的时候建立了这种关系，那么在插入数据的时候，如果值不合规就会报错提示你），这也确保了数据的完整性。

（注意：外键的值可以是NULL）

添加外键的语法如下：

ALTER TABLE 子表名
ADD CONSTRAINT 外键名称 FOREIGN KEY (子表列名)
REFERENCES 父表名 (父表列名);  

一句话总结就是：

主键在主表中定义，是表中数据的唯一标识。外键在从表中定义 ，指向引用主表的主键，从而建立表之间的关联关系。

下面开始讲联表查询，多表关联查询主要就是要理解几类JOIN的用法。

1.INNER JOIN内连接：

内连接是最常见的连接方式。它根据指定的条件，获取两个表中匹配的行，即只有两个表中共有这个关联字段值的数据才显示，其语法是：

SELECT 字段
FROM 表1
    INNER JOIN 表2
        ON 表1.A = 表2.A;  

上图中将User表和Order表INNER JOIN后，只有User ID是123在两张表中都有，所以会被保留。inner join用的最多，是因为我们实际查询的时候希望用这个字段去别的表中串到其他的信息，它得在别的表中也存在，即我们不想获取NULL信息。

2.LEFT JOIN和RIGHT JOIN左连接和右连接：

从上图可以看出，左连接LEFT JOIN以左表信息为准，根据关联条件去找右表中与之匹配的行，如果右表中没有，会以NULL显示，所以最终返回的数据的行数是跟左表保持一致的，其语法是：

SELECT 字段
FROM 表1
    LEFT JOIN 表2
        ON 表1.A = 表2.A;  

RIGHT JOIN同理，只是会以右边的那个表为基准去串联数据：

将这两种连接放在一起讲，是因为它们在查询的时候，都会以某个表的全部信息为准，只是左右顺序的问题。平常我们用的都是LEFT JOIN，将需要保留全部信息的表放在左边，右连接就有点多余，可以不用。

3.FULL OUTER JOIN全连接

最后还有一种查询，是返回两个表中的全部数据，叫全连接。如果没有匹配的记录，那么另一表中的列会以 NULL 填充。不过这个在MySQL数据库中没有，并不是通用的连接查询语法，略作了解即可。

那么怎么选择该用哪一种联结呢？

在实际应用中，不同的 JOIN 类型适用于不同的业务场景。

• 如果我们只想获取两个表中匹配的记录，那么应该使用 INNER JOIN。
• 如果我们想保留其中一张表的所有信息，去其它表中找与之相匹配的记录，那么应该使用 LEFT JOIN，将主表放在左边。
• 如果我们想获取两个表中的所有记录，那么应该使用 FULL OUTER JOIN。

下面我们通过一个例子来运用一下：

现在数据库里有Teacher表和Course两张表，信息分别如下:

如果我们想查出所有女老师所教的课程名，应该怎么写SQL语句？

因为我表中的数据比较少，且每位老师都有对应的课程，每个课程也都指定了授课老师，即两个表中的共同字段TeacherNumber完全一样，所以用inner join和left join都可以。一般用inner join比较多，SQL可以写成：

SELECT T.Name,
       T.Gender,
       C.Name AS Course_Name
FROM dbo.Teacher AS T
    INNER JOIN dbo.Course AS C
        ON T.TeacherNumber = C.TeacherNumber
WHERE Gender = '女';