patternMinor

UNION ALL w/ MINUS

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

minusunionall

Problem

Why does:

select 1 FROM DUAL 
UNION ALL
select 1 FROM DUAL
MINUS
SELECT 2 FROM DUAL

return only a single 1 rather than 2 rows of 1 in Oracle?

Solution

In Oracle all set operators currently have equal precedence and are evaluated from top to bottom.

SELECT 1
FROM   DUAL
UNION ALL
SELECT 1
FROM   DUAL;

Returns two rows with both columns containing 1.

But minus operates similarly to union (as opposed to union all) and removes duplicates so distinct-ifying the result.

One way of getting the result you want would be to add parentheses so that (1) is Union all-ed onto the result of (1) MINUS (2).

SELECT 1
FROM   DUAL
UNION ALL
(SELECT 1
 FROM   DUAL
 MINUS
 SELECT 2
 FROM   DUAL);

Code Snippets

SELECT 1
FROM   DUAL
UNION ALL
SELECT 1
FROM   DUAL;

SELECT 1
FROM   DUAL
UNION ALL
(SELECT 1
 FROM   DUAL
 MINUS
 SELECT 2
 FROM   DUAL);

Context

StackExchange Database Administrators Q#144353, answer score: 7

Revisions (0)

No revisions yet.