理解SQL中的笛卡尔积

Question

我无法理解笛卡尔积是如何工作的。考虑一个简单的模式：

mysql> select * from account;
+----------------+-------------+---------+
| account_number | branch_name | balance |
+----------------+-------------+---------+
| A101           | Downtown    |     500 |
| A102           | Perryridge  |     400 |
| A201           | Brighton    |     900 |
| A215           | Mianus      |     700 |
| A217           | Brighton    |     750 |
| A222           | Redwood     |     700 |
| A305           | Round Hill  |     350 |
+----------------+-------------+---------+
7 rows in set (0.00 sec)

现在，当我提出这个问题时：

select a.balance from account a, account b where a.balance<b.balance;

我会一系列数值，除了最大值900。然后使用not in运算符确定最大值。在上面的查询中，在基于条件a.balance<b.balance进行连接时，关系中的第一个元组必须是500。理论上，前5个值必须是：

500
500
500
500
400

但我收到了：

+---------+
| balance |
+---------+
|     400 |
|     350 |
|     350 |
|     500 |
|     400 |

它是如何工作的？我正在使用MySQL数据库。

- Java Enthusiast

---

笛卡尔积被命名为混淆你认为它是线性的而不是指数级别的，但实际上它是指数级别的。话虽如此，这是一个很棒的词汇，可以用来吓跑初级程序员。 - Drew

Answer 1

笛卡尔积会将第一张表中的每个记录与第二张表中的每个记录连接起来，因此，由于您的表有7行，并且它是自身连接，如果没有 where 子句，则应该返回49条记录。您的 where 子句仅允许记录的 a 余额小于 b 余额。由于 900 是表中的最大余额，它永远不会小于任何其他余额，因此它永远不会被返回。

关于前五行，SQL 的常规规则也适用于连接。由于 SQL 表没有固有的顺序，完全取决于数据库如何返回它们，除非您在 order by 子句中明确指定顺序。您列出的值是完全有效的值，您希望查询返回这些值。

- Mureinik

---

不同的数据库会返回不同顺序的值吗？我怎么知道数据库使用什么顺序返回结果？ - Java Enthusiast

@JavaEnthusiast 不仅不同的数据库，同一个数据库实例根据各种内部条件可能会以不同的顺序返回行。确保行将以什么顺序返回的唯一方法是使用“order by”子句明确设置它。 - Mureinik

Answer 2

笛卡尔积是从两个给定数据集中生成所有可能的记录组合。

在您的情况下，要生成笛卡尔积，您必须使用 CROSS JOIN ：

SELECT 
  a.branch_name AS first_branch,
  b.branch_name AS second_branch,
  a.balance + b.balance AS total_balance
FROM account a
CROSS JOIN account b 

或者，使用 SQL:89 的 theta-style join:

SELECT 
  a.branch_name AS first_branch,
  b.branch_name AS second_branch,
  a.balance + b.balance AS total_balance
FROM account a, account b 

无论如何，笛卡尔积的目标是将两个集合的所有行关联起来。

当您对CROSS JOIN生成的笛卡尔积应用一些过滤条件时，结果将不再是笛卡尔积，而是它的一个子集，与给定的过滤条件相匹配。

因此，在您的情况下，这个查询：

SELECT 
  a.balance 
FROM account a, account b 
WHERE a.balance < b.balance

不会生成笛卡尔积。

实际上，比起你的查询，这个更好的替代方案是：

SELECT 
  a.balance 
FROM account a
WHERE a.balance < (
  SELECT MAX(balance) FROM account 
)

如果你想获取所有余额低于最大值的行。

无论如何，在这里使用自身 CROSS JOIN 看起来很可疑。因此，最好使用子查询。

- Vlad Mihalcea