SQL ROW 子查询中比较的顺序定义? [英] order definition for comparisons in SQL ROW subqueries?

问题描述

我想知道，当使用 > 或 >= 等比较运算符执行行子查询时，比较的顺序是使用词典(即字典)定义的吗?) 顺序还是按元素定义?

I was wondering when a row subquery is performed with a comparison operator such as > or >=, is the order of comparison defined using lexicographic (i.e. dictionary) order or is it defined element-wise?

即对于ROW(A,B)，应该

(79, 48) > (75, 52) 

在 WHERE 子句中的行查询中是 TRUE(字典)还是 FALSE(逐元素)?

be TRUE (dictionary) or FALSE (element-wise) in a row query in the WHERE clause?

我在 PostgreSQL 中对此进行了测试，似乎它使用的是字典顺序，即 (79, 48) >(75, 52) 是 TRUE 因为 79 >75 和第二个组件因此无关紧要.找了一圈，好像MySQL也是这样:MySQL行子查询比较问题，而且 MySQL 文档在这一点上似乎令人困惑.搜索 postgresql row subquery 并没有显示太多关于比较顺序的信息.

I tested this in PostgreSQL, and it seems that it's using the dictionary order, i.e. (79, 48) > (75, 52) is TRUE because 79 > 75 and the second component doesn't matter therefore. Searching around, it seems that this is also the case with MySQL: MySQL row subquery comparison issue, and the MySQL documentation seems to be confusing on this point. Searching for postgresql row subquery doesn't show much about the comparison order.

虽然从计算机科学的角度来看字典顺序是有意义的，但对于数据库用户来说它可能看起来有点奇怪，因为现在行的顺序取决于您在 SQL 中首先列出的列.例如，使用字典顺序，我们应该有:

While the dictionary order makes sense from a computer science perspective, it may look a bit weird to a database user because the order of rows now depends on which column you list first in the SQL. For example, using dictionary order, we should have:

 (52, 75) > (48, 79)  

对于ROW (B,A).比较相同的行，顺序正好相反，因为列 B 列在最前面.

for ROW (B,A). The same rows are compared, the order is exactly the opposite because column B is listed first.

我的问题是:

这种行为(在行查询中使用字典顺序)是来自 SQL 标准/跨供应商还是特定于实现?有没有这方面的参考资料?

Is this behavior (use of dictionary order in row queries) from SQL standards/cross-vendor or is it implementation specific? Any references for this?

推荐答案

这在 行构造器比较 Postgres 手册:

对于 <、<=、> 和 >= 的情况，行比较元素从左到右，在不相等或空元素对时停止被发现.如果这对元素中的任何一个为空，则结果行比较未知(空)；否则比较这个一对元素决定了结果.例如，ROW(1,2,NULL) 产生真，不为空，因为第三对元素不考虑.

For the <, <=, > and >= cases, the row elements are compared left-to-right, stopping as soon as an unequal or null pair of elements is found. If either of this pair of elements is null, the result of the row comparison is unknown (null); otherwise comparison of this pair of elements determines the result. For example, ROW(1,2,NULL) < ROW(1,3,0) yields true, not null, because the third pair of elements are not considered.

还有:

注意:在 PostgreSQL 8.2 之前，<、<=、> 和 >=代码>案例不是按照 SQL 规范处理.像 ROW(a,b) < 这样的比较ROW(c,d)被实现为 a <c AND b 而正确的行为是相当于 a .

Note: Prior to PostgreSQL 8.2, the <, <=, > and >= cases were not handled per SQL specification. A comparison like ROW(a,b) < ROW(c,d) was implemented as a < c AND b < d whereas the correct behavior is equivalent to a < c OR (a = c AND b < d).

这阐明了现代 Postgres 的行为符合 SQL 标准.

Which clarifies that the behavior of modern Postgres is according to the SQL standard.

它与 SELECT 查询的 ORDER BY 子句中的逻辑基本相同:从左到右比较项目，直到找到第一个不等式 - 除了 NULL 值, 按默认升序对 last 进行排序.

It's basically the same logic as in the ORDER BY clause of a SELECT query: items are compared left to right until the first inequality is found - except for NULL values, which sort last in default ascending order.

相关:

• 'WHERE (col1, col2) <(val1, val2)'

这篇关于SQL ROW 子查询中比较的顺序定义?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！