SQL UNION ALL to eliminate duplicates

Question

I found this poll and the answer to this poll posted here in the repository. But I really don't understand the code. How will the UNION EVERYTHING turn into UNIION (excellent)? Also, why is this code faster?

Question

Write an SQL query using UNION ALL (not UNION), which uses the WHERE clause to eliminate duplicates. Why would you want to do that? Hide answer You can avoid duplication using UNION ALL, and still work much faster than UNION DISTINCT (which is actually the same as UNION) by running this query:

ANSWER

SELECT * FROM mytable WHERE a=X UNION ALL SELECT * FROM mytable WHERE b=Y AND a!=X

The key is the AND a!=X part. This gives you the benefits of the UNION (a.k.a., UNION DISTINCT) command, while avoiding much of its performance hit.

Answer 1

a, b. , , :

SELECT * FROM mytable WHERE a=X OR b=Y

B-. a? b? , .

, UNION . . UNION.

, , b=Y a=X, . , .

SELECT * FROM mytable WHERE a=X 
UNION DISTINCT
SELECT * FROM mytable WHERE b=Y

UNION DISTINCT , , . , SELECT DISTINCT ....

, "", , , , . .

, , . , . , , , , .

, , .

SELECT * FROM mytable WHERE a=X 
UNION ALL 
SELECT * FROM mytable WHERE b=Y AND a!=X

. , a=X, , a!=X, , .

, , , b=Y, , a=X AND b=Y .

, OR UNION DISTINCT.

Answer 2

, - . select .

, , , UNION ALL UNION.

- . .

- + . 2- db , db , . . DB , , .

UNION ALL + WHERE , . , .

Answer 3

I think it will work

select col1 From (
select row_number() over (partition by col1 order by col1) as b, col1 
from (
select col1  From u1
union all
select col1 From u2 ) a
) x
where x.b =1