SQL 中的笛卡尔积
SQL中的笛卡尔积是数学集合论中的一个术语。但是,我们也可以在 SQL 数据库手册中找到这个术语。它意味着什么,我们应该如何使用它?让我们来学习一下。
两个集合 X 和 Y 的笛卡尔积,表示为 X × Y,是所有有序对的集合,其中 x 在 X 中,y 在 Y 中。
就 SQL 而言,笛卡尔积是由两个表组成的新表。如果这些表分别有 3 行和 4 行,则笛卡尔乘积表将有 3×4 行。因此,第一个表中的每一行都连接第二个表的每一行。你得到两个集合的乘法结果,使原始集合的元素的所有可能的有序对。
笛卡尔积涉及大量计算运算,这些运算通常是冗余的。因此,对于大型表,我们建议使用限定符运算符。
如何在SQL中实现笛卡尔积?
在 SQL 中实现笛卡尔积可以通过返回两个表的叉积的 CROSS JOIN 运算符来实现。
让我们看一下下图中的示例。两个相应的表说明了颜色和大小值。由于没有 JOIN 条件,因此颜色表中的所有行 (2) 都连接到大小表中的所有行 (4),从而生成 8 行作为结果。
CROSS JOIN 方法适用于许多情况。例如,我们需要有一个办公室一个月的完整工资数据。即使月份 X 没有工资,您也可以将办公室与所有月份的表格交叉合并。
注意:在实践中,表的笛卡尔积并不常见。我们可能希望将所有员工与所有部门联系起来,但只有当每个人都按照一个计划工作,并且他们的工作影响所有部门时,这才是合理的。将所有员工/部门与所有__cpLocations联系起来完全是无稽之谈。
不过,有时数据库包含只有一行的表,用于存储一些常量(例如,公司名称)。在这里,我们可以使用笛卡尔乘积运算将此类表连接到任何查询。
在实践中使用笛卡尔乘积 SQL
笛卡尔乘积 SQL 在以下情况下很有用:
- 省略 JOIN 条件;
- JOIN 条件无效;
- 第一个表中的所有行都与第二个表中的所有行连接起来。
如果需要仅选择那些相互匹配的记录,则表的笛卡尔乘积变得更加常见。我们可以通过使用 ON、USING 或 WHERE 指定选择条件来做到这一点。
有时,笛卡尔积是由于查询文本中的错误而发生的。联接表的主要方法是内部联接或自然联接操作。
笛卡尔乘积中的关节
关节在笛卡尔乘积实现中起着重要作用。它们是它的子集。
例如,n 个表的笛卡尔积是一个包含所有可能的 r 行的表。在这里,r 是第一个表中的一些行、第二个表中的行等,直到第 n个表中的行的串联。让我们看看我们是否可以使用 SELECT 语句获得笛卡尔积。
要获取多个表的笛卡尔积,请在 FROM 子句中指定相乘表的列表,并在 SELECT 子句中指定其所有列的列表。在我们的例子中,我们需要得到菜肴类型(5 行)和膳食(3 行)表的笛卡尔乘积:
SELECT type_of_dishes.*, meal.* FROM type_of_dishes, meal;
结果是一个包含 5 x 3 = 15 行的表:
菜肴种类 | 餐 |
小吃 | 早餐 |
小吃 | 午餐 |
小吃 | 晚餐 |
汤 | 早餐 |
汤 | 午餐 |
汤 | 晚餐 |
主菜 | 早餐 |
主菜 | 午餐 |
主菜 | 晚餐 |
甜点 | 早餐 |
甜点 | 午餐 |
甜点 | 晚餐 |
喝 | 早餐 |
喝 | 午餐 |
喝 | 晚餐 |
现在,我们将表菜单(20 行)、膳食(3 行)、菜肴类型(5 行)和菜肴(30 行)乘以以下查询:
SELECT Menu.*, Meal.*, Type_of_Dishes.*, Dishes.* FROM Menu, Meal, Type_of_Dishes, Dishes;
我们得到一个包含 20 x 3 x 5 x 30 = 9000 行的表。
实践中表的内部 JOIN
内部 JOIN 只能将表与公共列合并。因此,当我们执行此操作时,它仅连接具有公共值的字符串。
通常,内部 JOIN 用于具有一对多关系的表。在这种情况下,主表的主键和从属表的外键充当关系列。因此,在内部 JOIN 期间,从属表中没有相关行的主表行将根本不包含在查询结果中。
在 SQL 中,有 2 种方法可以实现表的内部 JOIN。这两种方法是等效的,通常会导致相同的查询执行算法。让我们详细了解它们。
从笛卡尔积中选出
假设我们想要显示所有学生的姓名及其分数。相应的请求如下所示:
SELECT students.name_st, marks.mark FROM students, marks WHERE students.cod_st = marks.cod_st
JOIN操作
将学生与其分数联系起来的相同查询可以写得略有不同:
SELECT students .name_st, marks.mark FROM students join marks ON students.cod_st = marks.cod_st
这两个查询的结果相似,如下所示:
name_st mark Smith Smith... Adams Adams Adams ...
每个学生的姓氏在结果表中重复的次数与学生获得分数的次数相同。例如,如果学生的表格包含一个姓氏为 Anderson 的行,而 Anderson 尚未获得任何分数,则该姓氏将根本不显示在生成的表格中。
这就是内部联接操作的工作方式。
我们需要注意上述示例的一些特征。
首先,查询文本使用用点表示法书写的复合列名:table_name.column_name
使用可分辨名称可避免列名记录中的歧义,因为不同的表可以包含同名的列。如果任何列的名称在 FROM 子句的表中是唯一的,则可以使用简单名称。但是,可分辨名称更好,因为此类查询的编译速度更快。
其次,在上面的两个查询中,我们明确指定了 JOIN 条件 – 关系列的相等性 (students.cod_st = marks.cod_st)。
从理论上讲,您可以缩短查询文本,因为 students 和 marks 表中只有一个公共列 (cod_st)。但是,联接两个表并不总是仅通过主键和外键完成。同一类型的任意两列可用于联接表。
使用关系的非键列链接行时要非常小心。例如,通过条件 students.cod_st = marks.cod_sub 定义关系的查询在语法上是正确的,但完全没有意义。
结论
一般来说,笛卡尔 SQL 产品会生成大量行,结果很少有用。因此,在使用 SQL 表时,最好避免使用笛卡尔乘积。应始终在 WHERE 子句中包含有效的 JOIN 条件,但特定需要合并所有表中的所有行的情况除外。
但是,笛卡尔乘积 SQL 可以应用于必须生成大量行来模拟所需数据量的测试。
标签:JOIN,乘积,笛卡尔,SQL,st,表中 From: https://www.cnblogs.com/cdaniu/p/18059154