SQL 联合问题

发布于 2024-10-18 02:17:02 字数 413 浏览 2 评论 0原文

有人可以向我解释一下为什么 SQL 语句：

SELECT 'TEST1'
UNION SELECT 'TEST2'
UNION SELECT 'TEST3'

returns:

TEST2
TEST3
TEST1

我试图找出这方面 UNION 关键字背后的逻辑。有没有办法让它返回：

TEST1
TEST2
TEST3

而不使用 ORDER BY 子句？换句话说，我可以控制UNION语句的执行顺序吗？

如果重要的话，我使用 Postgre 9.0 和 PHP 作为我的语言

非常感谢，布雷特

原文

Can someone explain to me why the SQL statement:

SELECT 'TEST1'
UNION SELECT 'TEST2'
UNION SELECT 'TEST3'

returns:

TEST2
TEST3
TEST1

I am trying to figure out the logic behind the UNION keyword in this aspect.
Is there a way I could get it to return:

TEST1
TEST2
TEST3

without using the ORDER BY clause? In other words, can I control the execution order of the UNION statements?

If it matters, I am using Postgre 9.0 and PHP as my language

Many thanks,
Brett

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

幼儿园老大 2024-10-25 02:17:02

根据 UNION 的 PostgreSQL 文档：

UNION 有效地将 query2 的结果附加到 query1 的结果（尽管不能保证这是实际返回行的顺序）。

回复收藏 0 原文

栀子花开つ 2024-10-25 02:17:02

UNION 语义是删除重复项。 PostgreSQL 使用哈希函数来删除重复项，结果按照键的哈希顺序输出。

您可以使用UNION ALL，但除非您使用ORDER BY 子句，否则SQL 仍然不能保证顺序。

EXPLAIN
SELECT 'TEST1'
UNION SELECT 'TEST2'
UNION SELECT 'TEST3'

产生：

HashAggregate  (cost=0.07..0.10 rows=3 width=0)
  ->  Append  (cost=0.00..0.06 rows=3 width=0)
        ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..0.02 rows=1 width=0)
              ->  Result  (cost=0.00..0.01 rows=1 width=0)
        ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..0.02 rows=1 width=0)
              ->  Result  (cost=0.00..0.01 rows=1 width=0)
        ->  Subquery Scan on "*SELECT* 3"  (cost=0.00..0.02 rows=1 width=0)
              ->  Result  (cost=0.00..0.01 rows=1 width=0)

而

EXPLAIN
SELECT 'TEST1'
UNION ALL SELECT 'TEST2'
UNION ALL SELECT 'TEST3'

产生：

Append  (cost=0.00..0.06 rows=3 width=0)
  ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..0.02 rows=1 width=0)
        ->  Result  (cost=0.00..0.01 rows=1 width=0)
  ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..0.02 rows=1 width=0)
        ->  Result  (cost=0.00..0.01 rows=1 width=0)
  ->  Subquery Scan on "*SELECT* 3"  (cost=0.00..0.02 rows=1 width=0)
        ->  Result  (cost=0.00..0.01 rows=1 width=0)

UNION semantics are that duplicates are removed. PostgreSQL is using a Hash function to remove the duplicates, and the results are comin out in the order of the key's hash.

You can use UNION ALL, but SQL still doesn't guarantee an order unless you use the ORDER BY clause.

EXPLAIN
SELECT 'TEST1'
UNION SELECT 'TEST2'
UNION SELECT 'TEST3'

Produces:

HashAggregate  (cost=0.07..0.10 rows=3 width=0)
  ->  Append  (cost=0.00..0.06 rows=3 width=0)
        ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..0.02 rows=1 width=0)
              ->  Result  (cost=0.00..0.01 rows=1 width=0)
        ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..0.02 rows=1 width=0)
              ->  Result  (cost=0.00..0.01 rows=1 width=0)
        ->  Subquery Scan on "*SELECT* 3"  (cost=0.00..0.02 rows=1 width=0)
              ->  Result  (cost=0.00..0.01 rows=1 width=0)

Whereas

EXPLAIN
SELECT 'TEST1'
UNION ALL SELECT 'TEST2'
UNION ALL SELECT 'TEST3'

Produces:

Append  (cost=0.00..0.06 rows=3 width=0)
  ->  Subquery Scan on "*SELECT* 1"  (cost=0.00..0.02 rows=1 width=0)
        ->  Result  (cost=0.00..0.01 rows=1 width=0)
  ->  Subquery Scan on "*SELECT* 2"  (cost=0.00..0.02 rows=1 width=0)
        ->  Result  (cost=0.00..0.01 rows=1 width=0)
  ->  Subquery Scan on "*SELECT* 3"  (cost=0.00..0.02 rows=1 width=0)
        ->  Result  (cost=0.00..0.01 rows=1 width=0)

回复收藏 0 原文