UNION ALL w/ MINUS

Question

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?

Answer 1

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);

Answer 2

Martin Smith addressed a possible solution if you don't need to union before you minus (which I'm not sure will work for what you intend).. If you need to UNION first before you MINUS, while still preserving duplicate rows, then I think you'll have to do something along these lines:

SELECT
  *
FROM (
  SELECT 1 as col1 FROM DUAL
  UNION ALL
  SELECT 1 as col1 FROM DUAL) UN
WHERE NOT EXISTS (SELECT 2 as col1 FROM DUAL MI WHERE UN.col1 = MI.col1)

Essentially doing the UNION ALL and then filtering the result (via WHERE clause) to exclude any that exist in your MINUS data set.