我是一名数据库学生,我执行了以下查询来同时学习一些内容(LEFT/RIGHT JOIN、UNION、WHERE + RegEx)。令我困扰的是执行顺序。我有两个表,如下所示:
create table practicaleft(
id smallint primary key,
nombre varchar,
cumple date
);
create table practicaright(
id smallint primary key,
apellido varchar,
cumpleanios date
);
然后,我插入一些随机数据:
INSERT INTO practicaleft VALUES
(1, 'John', CURRENT_DATE - 1),
(5, 'Alice', CURRENT_DATE - 5),
(3, 'Bob', CURRENT_DATE - 3),
(7, 'Eva', CURRENT_DATE - 7);
INSERT INTO practicaright VALUES
(5, 'Doe', CURRENT_DATE - 5),
(6, 'Smith', CURRENT_DATE - 6),
(3, 'Johnson', CURRENT_DATE - 3),
(4, 'Brown', CURRENT_DATE - 4);
之后,我执行此查询:
select id, nombre
from practicaleft
where nombre similar to 'A%'
union
select pr.id, pr.apellido
from practicaright pr
where pr.id = 4 or pr.apellido ilike '_o%'
union all
select id, apellido
from practicaright
where cumpleanios > current_date - 5;
结果?干得好:
4 "Brown"
5 "Alice"
5 "Doe"
3 "Johnson"
3 "Johnson"
4 "Brown"
TL;DR:该查询分为三部分,结果使用运算符 UNION ALL 合并。
现在问题来了。人们可能认为这是逐条指令执行的,因此顺序应该是:
5 "Alice"
5 "Doe"
3 "Johnson"
4 "Brown"
3 "Johnson"
4 "Brown"
但这并没有发生。解决这个问题的唯一方法是添加一些随机字符串作为字段,如下所示:
select id, nombre, 'part1' as query_part
from practicaleft
where nombre similar to 'A%'
union
select pr.id, pr.apellido, 'part2' as query_part
from practicaright pr
where pr.id = 4 or pr.apellido ilike '_o%'
union all
select id, apellido, 'part3' as query_part
from practicaright
where cumpleanios > current_date - 25;
怎么了?我是否跳过了一些真正重要的 SQL 机制?
Jasen 的答案是正确的 - PostgreSQL 可以自由地以任何顺序返回行,除非您添加
ORDER BY
如下子句:您需要括号来确保排序应用于整个结果,而不仅仅是
UNION
.但让我解释一下为什么 PostgreSQL 不按您期望的顺序返回行。原因是第一个
UNION
不是UNION ALL
。如果你在任何地方都使用过UNION ALL
,PostgreSQL 将执行如下查询:也就是说,PostgreSQL 将执行三个查询并简单地附加结果,最终会得到您期望的排序。
但你使用了
union
第一次,并union
消除了重复。其执行方式如下:PostgreSQL 使用哈希聚合来删除前两个分支中的重复项。结果行按照它们在哈希表中的顺序返回,这是相当随机的(好的哈希函数的行为是这样的)。
SQL 不保证结果排序,除非
order by
查询中有子句。如果您不说“order by”,您的结果将以查询规划器和数据库引擎决定的最有效(或足够有效)的任何顺序出现。
并行表扫描是多个查询同时扫描同一个表的情况。但您的示例表可能太短了。
当我有一个
union all
想要按顺序排列的字符串时,我会向查询添加一个具有常量值的排序列。1 as sort
2 as sort
3 as sort
order by sort
抱歉,我不是 Postgres 专家,但问题是关于操作顺序,而不是结果集中行的顺序。3个数据集之间存在并集和并集。假设我们有数据集 1,2,3 并集 3,4,5 并集所有 5,6,7。如果首先应用并集,结果应为 1,2,3,4,5,5,6,7。如果首先执行 union all,则结果应为 1,2,3,4,5,6,7,因为最后一步应用了 union 的隐式分组依据。如果我错了,请纠正我。