当前位置：首页 > news >正文

【你也能从零基础学会网站开发】 SQL Server结构化查询语言数据操作应用--DML篇浅谈SQL JOIN多表查询之FULL JOIN 全连接查询

news 2024/11/6 23:04:07

🚀 个人主页 极客小俊
✍🏻 作者简介：程序猿、设计师、技术分享
🐋 希望大家多多支持, 我们一起学习和进步！
🏅 欢迎评论 ❤️点赞💬评论 📂收藏 📂加关注

FULL JOIN 全连接查询概述

FULL JOIN查询又叫全连接查询

它的查询流程是当执行A表 FULL JOIN B表时，结果集将包括A表和B表中的所有记录。

对于A表中的每条记录，如果在B表中存在一条或多条可以进行条件匹配数据，这些记录将被包括在结果集中~ 同理，对于B表中的每条记录，如果在A表中存在进行条件匹配数据，那么它们也将被包括在结果集中。

如果某条记录在A表中没有可以进行条件匹配数据，但它在B表中有，那么结果集中，这条记录的A表部分将显示为NULL，反之亦然!

语法规则如下

SELECT [字段..*] FROM 表A FULL OUTER JOIN 表B ON 表A.关联字段 = 表B.关联字段;

我们在SQL Server 2000中也可以省略OUTER 关键字

如下

SELECT [字段..*] FROM 表A FULL JOIN 表B ON 表A.关联字段 = 表B.关联字段;

应用场景

我们还是以之前的案例 公司表和员工表

SQL如下

CREATE TABLE Company(cid int PRIMARY KEY IDENTITY,cname VARCHAR(255) NOT NULL,cmsg VARCHAR(255),Address VARCHAR(255),NumberPeople INT
)CREATE TABLE Employee(eid int PRIMARY KEY IDENTITY,ename VARCHAR(255) NOT NULL,eage TINYINT,esaley INT,epart VARCHAR(255) NOT NULL,ephone VARCHAR(255) NOT NULL,cid INT,
)INSERT INTO Company(cname,cmsg,Address,NumberPeople) VALUES('重庆新俊方','科技企业','重庆市渝中区',5000);
INSERT INTO Company(cname,cmsg,Address,NumberPeople) VALUES('成都水刚','制造企业','成都庆阳',8000);
INSERT INTO Company(cname,cmsg,Address,NumberPeople) VALUES('重庆欧元','金融企业','重庆市杨家平',450);
INSERT INTO Company(cname,cmsg,Address,NumberPeople) VALUES('重庆万科集团','房地产企业','重庆市江北',876);
INSERT INTO Company(cname,cmsg,Address,NumberPeople) VALUES('香港时代证券','金融企业','香港九龙',100);
INSERT INTO Company(cname,cmsg,Address,NumberPeople) VALUES('贵州圣烟','烟草企业','贵州山脉',682);INSERT INTO Employee(ename,eage,esaley,epart,ephone,cid) VALUES('张军',26,3600,'开发部门','333-222-777',5);
INSERT INTO Employee(ename,eage,esaley,epart,ephone,cid) VALUES('王辉',30,5600,'技术部门','111-000-888',4);
INSERT INTO Employee(ename,eage,esaley,epart,ephone,cid) VALUES('张三进',21,2301,'市场部门','666-777-111',2);
INSERT INTO Employee(ename,eage,esaley,epart,ephone,cid) VALUES('杨小明',26,4520,'开发部门','888-999-000',3);
INSERT INTO Employee(ename,eage,esaley,epart,ephone,cid) VALUES('刘罗戈',19,7800,'新媒体部门','111-111-111',4);
INSERT INTO Employee(ename,eage,esaley,epart,ephone,cid) VALUES('毛利兰',22,3000,'市场部门','999-999-321',1);
INSERT INTO Employee(ename,eage,esaley,epart,ephone,cid) VALUES('詹华',22,8000,'运营部门','999-888-321',8);
INSERT INTO Employee(ename,eage,esaley,epart,ephone,cid) VALUES('何林',37,2500,'制造部门','100-888-321',9);

我们可以先用FULL JOIN 把这两个表连接起来看看效果!

如下

SELECT * FROM Employee AS c FULL JOIN Company AS e 
ON c.cid = e.cid

如图

我们从结果集上其实就可以看出一个特点,FULL JOIN其实就是LEFT JOIN和RIGHT JOIN的组合形式!

就像上面这个查询~
我们可能有一个包含所有员工记录的表和一个公司表, 如果我们想要生成一份报告，显示出所有员工的信息 , 包括没有员工的公司信息, 那么我们就可以使用FULL JOIN来连接这两个表。

这样，我们就可以在结果集中看到所有员工信息，以及它们对应的公司信息，或者NULL值, 相反在公司表中也是一样，显示出所有公司，以及对应的员工信息, 我们从结果集中就很容易看到，哪些公司没有员工，哪些员工没有等级注册公司!~

我们再举个例子

比如我们有一家在线书店，有两个重要的数据表：books（书籍表）和 sales（销售记录表）

books表包含所有书籍的信息，如: 书籍ID、书名、作者等等 …
sales表包含所有销售记录的信息，如: 销售ID、书籍ID、销售数量等等 …

那么我们先把数据表和数据创建到数据库!~

SQL如下

CREATE TABLE books (book_id INT PRIMARY KEY,title VARCHAR(255) NOT NULL,author VARCHAR(255) NOT NULL
)CREATE TABLE sales (sale_id INT PRIMARY KEY,book_id INT,num INT NOT NULL
)INSERT INTO books (book_id, title, author) VALUES  (1, '机器学习', '张三');
INSERT INTO books (book_id, title, author) VALUES  (2, 'AI人工智能', '李四');
INSERT INTO books (book_id, title, author) VALUES  (3, '三天学会编程开发', '王五');INSERT INTO sales (sale_id, book_id, num) VALUES (101, 1, 130);
INSERT INTO sales (sale_id, book_id, num) VALUES (102, 2, 100);
INSERT INTO sales (sale_id, book_id, num) VALUES (103, 4, 66);

如图

注意：这里的 book_id = 4这个值在 books 表中不存在，我们用于测试 FULL JOIN

场景需求

那么现在我们想要生成一份报告，显示出所有书籍名称以及它们的销售记录!

由于有些书籍可能还没有被销售，而有些销售记录中可能对应着已经不存在的书籍，这完全也是有可能例如，书籍可能被下架或删除，那么销售记录中也可能保留了此书的销售记录，对吧!

这个时候，我们就使用FULL JOIN来确保结果集中包含所有书籍名称和销售记录，即使它们之间没有直接的匹配关系!

SQL如下

SELECT * FROM books AS b FULL JOIN sales AS s ON b.book_id = s.book_id;

那这里我们能不能提几个问题？

查询哪些书籍可能还没有被销售？

SELECT b.title  FROM books AS b FULL JOIN sales AS s ON b.book_id = s.book_id WHERE s.num IS NULL

被下架的书籍ID是多少?

SELECT s.book_id FROM books AS b FULL JOIN sales AS s ON b.book_id = s.book_id WHERE b.book_id IS NULL

怎么样，有了FULL JOIN是不是很方便了!

不过到底要在什么样子的场景下使用FULL JOIN跟其他查询方式一样，都是要看具体的需求和逻辑决定的!

如下所示

我们可以清晰的看到，所有书籍名称，以及被删除的书籍对应的书籍ID,以及它的销售记录, 还有没有开始销售的书籍名称!

SELECT b.title,s.book_id,s.num FROM books AS b FULL JOIN sales AS s ON b.book_id = s.book_id

FULL JOIN 与 INNER JOIN 的区别

其实FULL JOIN的效果正好与INNER JOIN内连接相反, 这主要体现在它们如何处理两个表中不匹配的数据记录!

INNER JOIN只会返回两个表中具有条件匹配关系的记录，通俗的说只有当两个表中的记录在连接条件上相匹配时，这些数据记录才会被包含在结果集中! 明白这个意思吧!

如果某个表中的记录在另一个表中没有匹配的行，则这些记录根本不会出现在结果集中。
所以我们通常都是在说INNER JOIN的结果集是两个表的交集!~

相反的FULL JOIN会返回两个表中所有的数据记录，无论它们是否匹配。

对于在两个表中都存在的匹配记录，FULL JOIN会在结果集中包含这些行, 对于只存在于一个表中的记录, 即便是没有条件匹配的记录，FULL JOIN也会在结果集中显示这些数据记录，但会将缺失的部分填充为NULL 那么FULL JOIN的结果集是两个表的并集，并包含了所有不满足查询条件的数据记录，这些不匹配的记录在另一个表的相应字段中会以NULL值填充~~