了解SQL中的笛卡尔积 [英] Understanding cartesian product in SQL

问题描述

我无法理解笛卡尔积的工作原理.考虑简单的模式:

I am not able to understand how Cartesian product works. Consider the simple schema:

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个值必须为:

I get a series of values except the maximum value 900. Then using the not in operator I determine the maximum value. Before that in the above query, when the join takes place based on the condition a.balance<b.balance, the first tuple in the relation must be 500. Theoretically, the first 5 values must be:

500
500
500
500
400

但是我得到了:

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

它如何运作?我正在使用MySQL数据库.

How is it working? I am using MySQL database.

推荐答案

笛卡尔式联接将第一个表中的每个记录与第二个表中的每个记录联接在一起，因此，由于您的表有7行，并且与自身联接，因此如果没有where子句，则返回49条记录.您的where子句仅允许记​​录a余额小于b余额的记录.正如您所说，由于900是表中的最大余额，因此它永远不会小于任何其他余额，因此也永远不会被退回.

A Cartesian join joins every record in the first table with every record in the second table, so since your table has 7 rows and it's joined with itself, it should return 49 records had you not had a where clause. Your where clause only allows records where a's balance is smaller than b's balance. Since 900 is, as you said, the maximal balance in the table, it will never be smaller than any other balance, and therefore it will never be returned.

对于前五行，SQL的常规规则也适用于联接.由于SQL表没有内在的顺序，除非您在order by子句中明确声明了顺序，否则完全由数据库来决定如何返回它们.您列出的值是您希望查询返回的完全有效的值.

With regard to the first five rows, the normal rules of SQL apply to joins too. Since SQL tables have no intrinsic order, it's completely up to the database to decide how to return them, unless you explicitly state an order in the order by clause. The values you listed are perfectly valid values you'd expect the query to return.

这篇关于了解SQL中的笛卡尔积的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！