SQL中的笛卡尔积你真的懂了吗？

SQL数据库运维 2023-05-05

5335

点击蓝色字关注“SQL数据库运维”，回复“SQL”获取2TB学习资源！

原文链接：https://www.cnblogs.com/CareySon/archive/2010/04/13/1711095.html

首先感谢原文作者的总结，本文在原文的基础上在SQL Server数据库环境下，进行了部分优化和提供出了全部的测试脚本数据。

什么是笛卡尔积？百科这样说

笛卡尔乘积是指在数学中，两个集合X和Y的笛卡尔积（Cartesian product），又称直积，表示为X×Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。

假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}。

简单来说：就是两个集合相乘的结果。

准备测试数据

CREATE TABLE Student (
ID INT,
StudentName varchar(50),
StudentClassID INT
)
--插入Student表数据
INSERT INTO  Student  VALUES (1,'Careyson',1),(2,'Tony',2),(3,'Jack',3),(4,'Nancy',2),(5,'Peter',1)
--查询表数据
SELECT * FROM Student


--创建Class数据表
CREATE TABLE Class (
ClassID INT,
ClassName varchar(50)
)
--插入Class表数据
INSERT INTO  Class  VALUES (1,'软件一班'),(2,'软件二班'),(3,'软件三班')
--查询表数据
SELECT * FROM Class

多表连接简介

在关系数据库中，一个查询往往会涉及多个表，因为很少有数据库只有一个表，而如果大多查询只涉及到一个表的，那么那个表也往往低于第三范式，存在大量冗余和异常。

因此，连接(Join)就是一种把多个表连接成一个表的重要手段.

比如简单两个表连接学生表(Student)和班级(Class)表，如图：

进行连接后如图：

笛卡尔积

笛卡尔积在SQL中的实现方式即是交叉连接(Cross Join)。所有连接方式都会先生成临时笛卡尔积表，笛卡尔积是关系代数里的一个概念，表示两个表中的每一行数据任意组合,上图中两个表连接即为笛卡尔积(交叉连接)

在实际应用中，笛卡尔积本身大多没有什么实际用处，只有在两个表连接时加上限制条件，才会有实际意义，下面看内连接。

内连接

如果分步骤理解的话，内连接可以看作先对两个表进行了交叉连接后，再通过加上限制条件(SQL中通过关键字on)剔除不符合条件的行的子集，得到的结果就是内连接了。上面的图中，如果我加上限制条件如下：

对于开篇中的两个表，假使查询语句如下：

SELECT *
  FROM [Class] c
       inner join 
       [Student] s
       on c.ClassID=s.StudentClassID

可以将上面查询语句进行分步理解，首先先将Class表和Student表进行交叉连接,生成如下表：

然后通过on后面的限制条件，只选择那些StudentClassID和ClassID相等的列（上图中划了绿色的部分）,最终，得到选择后的表的子集

当然，内连接on后面的限制条件不仅仅是等号，还可以使用比较运算符,包括了>（大于）、>=（大于或等于）、<=（小于或等于）、<（小于）、!>（不大于）、!<（不小于）和<>（不等于）。当然，限制条件所涉及的两个列的数据类型必须匹配.

对于上面的查询语句，如果将on后面限制条件由等于改为大于:

SELECT *
  FROM [Class] c
       inner join 
       [Student] s
       on c.ClassID>s.StudentClassID

则结果从第一步的笛卡尔积中筛选出那些ClassID大于StudentClassID的子集：

虽然上面连接后的表并没有什么实际意义，但这里仅仅作为DEMO使用。

关系演算

上面笛卡尔积的概念是关系代数中的概念，而我在前一篇文章中提到还有关系演算的查询方法，上面的关系代数是分步理解的，上面的语句推导过程是这样的：“对表Student和Class进行内连接，匹配所有ClassID和StudentClassID相等行，选择所有的列”

而关系演算法，更多关注的是我想要什么,比如说上面同样查询，用关系演算法思考的方式是“给我找到所有学生的信息，包括他们的班级信息，班级ID，学生ID，学生姓名”

用关系演算法的SQL查询语句如下：

SELECT *
  FROM [Class] c
       ,
       [Student] s
  where c.ClassID=s.StudentClassID

当然，查询后返回的结果是不会变的：

外连接

假设还是上面两个表，学生和班级.我在学生中添加一个名为Eric的学生，但出于某种原因忘了填写他的班级ID：



INSERT INTO  Student  VALUES (6,'Eric',NULL)

当我想执行这样一条查询：给我取得所有学生的姓名和他们所属的班级:

SELECT s.StudentName,c.ClassName  
  FROM [dbo].[Student] s
       inner join 
       [dbo].[Class] c
       on 
       s.StudentClassID=c.ClassID

结果如下图:

可以看到，这个查询“丢失”了Eric..

这时就需要用到外连接，外连接可以使连接表的一方，或者双方不必遵守on后面的连接限制条件。这里把上面的查询语句中的inner join改为left outer join：

SELECT s.StudentName,c.ClassName     
  FROM [dbo].[Student] s
       left outer join 
       [dbo].[Class] c
       on 
       s.StudentClassID=c.ClassID

结果如下：

Eric又重新出现。

右外连接

右外连接和左外连接的概念是相同的，只是顺序不同，对于上面查询语句，也可以改成：



SELECT s.StudentName,c.ClassName
  FROM [dbo].[Class] c
       right outer join 
       [dbo].[Student] s
       on 
       s.StudentClassID=c.ClassID

效果和上面使用了左外连接的效果是一样的。

全外连接

全外连接是将左边和右边表每行都至少输出一次,用关键字”full outer join”进行连接，可以看作是左外连接和右外连接的结合。

自连接

谈到自连接，让我们首先从一个表和一个问题开始（脚本创建）：

--创建Employee数据表
CREATE TABLE Employee (
ID INT,
EmployeeName varchar(50),
MangerID INT
)
--插入Employee表数据
INSERT INTO  Employee  VALUES (1,'Jack',NULL),(2,'Careyson',1),(3,'Tony',2),(4,'Tom',2),(5,'Peter',1)
--查询表数据
SELECT * FROM Employee

上面员工表(Employee)，因为经理也是员工的一种，所以将两种人放入一个表中，MangerID字段表示的是当前员工的直系经理的员工id。

现在，我的问题是，如何查找CareySon的经理的姓名?

可以看出，虽然数据存储在单张表中，但除了嵌套查询(这个会在后续文章中讲到)，只有自连接可以做到。正确自连接语句如下:

SELECT m.EmployeeName
  FROM [fordemo].[dbo].[Employee] e
  inner join [fordemo].[dbo].[Employee] m
  on  e.ManagerID=m.id and e.EmployeeName='Careyson'

结果如下：

在详细解释自连接的概念之前，请再看一个更能说明自连接应用之处的例子：

--创建MeettingRecord数据表
CREATE TABLE MeettingRecord (
EmployeeName varchar(50),
MeetingName  varchar(50)
)
--插入MeettingRecord表数据
INSERT INTO  MeettingRecord  VALUES ('Careyson','谈论项目进度'),('Jack','谈论项目进度'),('Careyson','讨论职业发展'),('Tom','讨论职业发展')
--查询MeettingRecord表数据
SELECT * FROM MeettingRecord

这个表是一个出席会议记录的表，每一行表示出席会议的记录（这里，由于表简单，我就不用EmployeeID和MeetingID来表示了，用名称对于理解表更容易些）

好了，现在我的问题是：找出既参加“谈论项目进度”会议，又参加”讨论职业发展”会议的员工

乍一看上去很让人迷惑是吧，也许你看到这一句脑中第一印象会是，应该是这样写：

SELECT  EmployeeName
  FROM [dbo].[MeettingRecord] m
  where MeetingName='谈论项目进度' and meetingName='讨论职业发展'

恩，恭喜你，答错了…如果这样写将会什么数据也得不到。正确的写法是使用自连接！

自连接的是一种特殊的连接，是对物理上相同但逻辑上不相同的表进行连接的方式。我看到百度百科上说自连接是一种特殊的内连接,但这是错误的,因为两个相同表之间不光可以内连接，还可以外连接，交叉连接…在进行自连接时，必须为其中至少一个表指定别名以对这两个表进行区分！

回到上面的例子，使用自连接，则正确的写法为：

SELECT  m.EmployeeName
  FROM [dbo].[MeettingRecord] m,
        [dbo].[MeettingRecord] m2
  where m.MeetingName='谈论项目进度' and m2.MeetingName='讨论职业发展'
        and m.EmployeeName=m2.EmployeeName

结果如下：

多表连接

多个表连接实际上可以看成是对N个表进行n-1次双表连接。这样理解会让问题简单很多!

--先删除之前添加的Student表中的第六条数据
DELETE FROM Student WHERE ID =6
--创建Teacher数据表
CREATE TABLE Teacher (
TeacherName varchar(50),
ClassID INT
)
--插入Teacher表数据
INSERT INTO  Teacher  VALUES ('王二',1),('李三',2)
--查询Teacher表数据
SELECT * FROM Teacher

比如上面三个表，前两个表是我们已经在文章开始认识的，假设现在又添加了一个教师表，对这三个表进行笛卡尔积如下:

SELECT *
  FROM [dbo].[Class]
       cross join 
       [dbo].[Teacher]
       cross join 
       [dbo].[Student]

结果可以如图表示：

总结

文中对SQL中各种连接查询方式都做了简单的介绍，并利用一些Demo实际探讨各种连接的用处，掌握好各种连接的原理是写好SQL查询所必不可少的！

点击关注“SQL数据库运维”，后台或浏览至公众号文章底部点击“发消息”回复关键字：进群，带你进入高手如云的技术交流群。后台回复关键字：SQL，获取学习资料。

动动小手点击加关注呦☟☟☟

文章转载自SQL数据库运维，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

SQL中的笛卡尔积你真的懂了吗？

准备测试数据

多表连接简介

笛卡尔积

关系演算

外连接

结果如下：

Eric又重新出现。

右外连接

全外连接

自连接

结果如下：

总结

评论