“全联盟”和“UNION” ????是否返回相同的结果？ [英] "Union all" and "UNION" ???? Return the same result or not ??

问题描述

尊敬的专家，


我的Oracle 8i数据库中有两个表：TEST（COL1，COl2，REC_NO）

和TEST1（COL1，COL2） ，REC_NO）。


两个表都是唯一索引的（COL1，COL2，REC_NO）。


我认为以下SQL命令将会返回相同的结果，但一个我朋友的
不这么认为。他说QUERY 1将返回1未分类

记录（ROWNUM <2）然后排序结果（ORDER BY COL1 ASC，

COL2 ASC，REC_NO ASC）。

在QUERY 2中，UNION将先排序然后返回第一张

区别记录。

但是我不能复制他说的情况。


我错了吗 ？？？请指教并谢谢。


KENNY CHEN


============ QUERY 1 ==== =========

（SELECT COL1，COL2，REC_NO FROM TEST

UNION ALL

SELECT COL1，COL2，REC_NO来自TMP_TEST）

WHERE（COL1 =：val1和COL2 =：val2 AND REC_NO>：val3）

AND ROWNUM< 2

订购COL1 ASC，COL2 ASC，REC_NO ASC

============ QUERY 2 ========= ====

（SELECT COL1，COL2，REC_NO FROM TEST

UNION

SELECT COL1，COL2，REC_NO来自TMP_TEST）

WHERE（COL1 =：val1 AND COL2 =：val2 AND REC_NO>：val3）

AND ROWNUM< 2

按COL1 ASC，COL2 ASC，REC_NO ASC排序

Dear experts,

I have two tables in my Oracle 8i database: TEST (COL1,COl2,REC_NO)
and TEST1 (COL1,COL2,REC_NO).

Both tables are unique-indexed on (COL1,COL2,REC_NO).

I think the following SQL commands will return the same result but one
of my friends don''t think so. He said "QUERY 1" will return 1 unsorted
record (ROWNUM < 2 ) first then sort the result (ORDER BY COL1 ASC,
COL2 ASC, REC_NO ASC).
In "QUERY 2" , "UNION" will sort first then return the first
distincted record.
But I can''t duplicate the situation he said.

AM I WRONG ??? Please advise and thank you.

KENNY CHEN

============ QUERY 1 =============
( SELECT COL1 , COL2 ,REC_NO FROM TEST
UNION ALL
SELECT COL1 , COL2 ,REC_NO FROM TMP_TEST )
WHERE (COL1 = :val1 AND COL2 = :val2 AND REC_NO > :val3 )
AND ROWNUM < 2
ORDER BY COL1 ASC, COL2 ASC, REC_NO ASC
============ QUERY 2 =============
(SELECT COL1 , COL2 ,REC_NO FROM TEST
UNION
SELECT COL1 , COL2 ,REC_NO FROM TMP_TEST )
WHERE (COL1 = :val1 AND COL2 = :val2 AND REC_NO > :val3 )
AND ROWNUM < 2
ORDER BY COL1 ASC, COL2 ASC, REC_NO ASC

推荐答案

任一查询都将返回任意记录。因此，您通过在不同时间运行这些查询可能会得到不同的结果。

如果您告诉我们这些查询所需的结果集是什么，我们

可以帮助你设计一个声明，不仅每次都能给出正确的
结果，而且还能以最有效的方式实现。

主要问题两个查询都是ORDER BY和

ROWNUM< 2的组合。在何处之后分配ROWNUM。条款被评估，

但是*之前的* the order byorder by子句对输出进行排序。由于您的WHERE

允许最多一行通过，因此排序没有多大意义。

如果您要查找最高记录，则应放置

子查询（选择... union [all] select ... order by ...）在

内联视图中：


select * from（

select ... where ...

union all

select ... where ...

订购...）

其中rownum< 2;


HTH，

Flado

Either of the queries will return an arbitrary record. Therefore, you
may get different results by running these queries at different times.
If you tell us what is the required result set of these queries, we
could help you design a statement that not only gives the correct
results every time, but also does so in the most efficient way.
The main problem with both queries is the combination of ORDER BY and
ROWNUM<2. ROWNUMs are assigned after the "where" clause is evaluated,
but *before* the "order by" clause sorts the output. Since your WHERE
allows at most one row to go through, sorting doesn''t make much sense.
If you are trying to find the top record, you should place the
subquery (select ... union [all] select ... order by ... ) in an
inline view:

select * from (
select ... where ...
union all
select ... where ...
order by ...)
where rownum<2;

HTH,
Flado

感谢您的回答。


我对查询结果的期望是从

根据ORDER BY子句的

2表。最初我们使用UNION

但是当桌子变大时会导致性能问题。


============ ===
fl***@imail.de （Vladimir Andreev）在留言中写道：< 7b ** ***********************@posting.google.c om> ...

Thank you for your answer.

What I expect from the query result is return the top one record from
the
2 tables according to the ORDER BY clause. Originally we use "UNION"
but it causees performance problem when the tables get huge.

===============
fl***@imail.de (Vladimir Andreev) wrote in message news:<7b*************************@posting.google.c om>...

其中一个查询将返回任意记录。因此，您可以通过在不同时间运行这些查询来获得不同的结果。
如果您告诉我们这些查询所需的结果集是什么，我们可以帮助您设计一个不是每次只给出正确的结果，但也会以最有效的方式给出结果。
两个查询的主要问题是ORDER BY和
ROWNUM< 2的组合。在何处之后分配ROWNUM。条款被评估，
但*之前的*按顺序子句对输出进行排序。由于你的WHERE
允许最多一行通过，因此排序没有多大意义。
如果你想找到最佳记录，你应该放置
子查询（选择...联合[全部]选择......按...排序
内联视图：

select * from（
select ... where ...
联盟所有
选择......其中......
命令......）
其中rownum< 2;

HTH， Flado

Either of the queries will return an arbitrary record. Therefore, you
may get different results by running these queries at different times.
If you tell us what is the required result set of these queries, we
could help you design a statement that not only gives the correct
results every time, but also does so in the most efficient way.
The main problem with both queries is the combination of ORDER BY and
ROWNUM<2. ROWNUMs are assigned after the "where" clause is evaluated,
but *before* the "order by" clause sorts the output. Since your WHERE
allows at most one row to go through, sorting doesn''t make much sense.
If you are trying to find the top record, you should place the
subquery (select ... union [all] select ... order by ... ) in an
inline view:

select * from (
select ... where ...
union all
select ... where ...
order by ...)
where rownum<2;

HTH,
Flado

感谢您的回复。


我期待的是获得前一条记录表格。

最初我使用

UNION从表中对选择结果进行排序，然后选取

前一条记录。但是，当表格变得很大时，它会导致性能问题。

这就是我尝试使用UNION ALL的原因。避免不必要的排序。


根据您的建议，似乎UNION ALL我的预期不会像

那样工作。如果你或任何人能帮助我，我很期待

设计一个

的声明，以便有效地给我正确的结果。


谢谢，


KENNY CHEN

=============
fl *** @ imail.de （弗拉基米尔·安德列夫）在留言中写道：< 7b **************** *********@posting.google.c om> ...

Thank you for your response.

What I expect is the get the top one record from the tables.
Originally I use
"UNION" to sort the selection result from the tables then pick up the
top one record. However, it causes performance problem when the tables
become huge.
That is why I try to use "UNION ALL" to avoid unnecessary sorting.

According to advice from you , it seems "UNION ALL" doesn''t work as
what I expected. I am looking forward if you or anyone can help me to
design a
statement to give me the correct result efficiently.

Thank you,

KENNY CHEN
=============
fl***@imail.de (Vladimir Andreev) wrote in message news:<7b*************************@posting.google.c om>...

其中一个查询将返回任意记录。因此，您可以通过在不同时间运行这些查询来获得不同的结果。
如果您告诉我们这些查询所需的结果集是什么，我们可以帮助您设计一个不是每次只给出正确的结果，但也会以最有效的方式给出结果。
两个查询的主要问题是ORDER BY和
ROWNUM< 2的组合。在何处之后分配ROWNUM。条款被评估，
但*之前的*按顺序子句对输出进行排序。由于你的WHERE
允许最多一行通过，因此排序没有多大意义。
如果你想找到最佳记录，你应该放置
子查询（选择...联合[全部]选择......按...排序
内联视图：

select * from（
select ... where ...
联盟所有
选择......其中......
命令......）
其中rownum< 2;

HTH， Flado

Either of the queries will return an arbitrary record. Therefore, you
may get different results by running these queries at different times.
If you tell us what is the required result set of these queries, we
could help you design a statement that not only gives the correct
results every time, but also does so in the most efficient way.
The main problem with both queries is the combination of ORDER BY and
ROWNUM<2. ROWNUMs are assigned after the "where" clause is evaluated,
but *before* the "order by" clause sorts the output. Since your WHERE
allows at most one row to go through, sorting doesn''t make much sense.
If you are trying to find the top record, you should place the
subquery (select ... union [all] select ... order by ... ) in an
inline view:

select * from (
select ... where ...
union all
select ... where ...
order by ...)
where rownum<2;

HTH,
Flado

这篇关于“全联盟”和“UNION” ????是否返回相同的结果？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！