我是一名数据库学生,我执行了以下查询来同时学习一些内容(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 机制?