Sql Union All *with* “distinct”

Question

UNION joins two results and remove duplicates, while UNION ALL does not remove duplicates. UNION also sort the final output.

What I want is the UNION ALL without duplicates and without the sort. Is that possible?

The reason for this is that I want the result of the first query to be on top of the final result, and the second query at the bottom. (And each sorted as if they where run individually)

Answer 1

SELECT *, 1 AS sort_order
  FROM table1
 EXCEPT 
SELECT *, 1 AS sort_order
  FROM table2
UNION
SELECT *, 1 AS sort_order
  FROM table1
 INTERSECT 
SELECT *, 1 AS sort_order
  FROM table2
UNION
SELECT *, 2 AS sort_order
  FROM table2
 EXCEPT 
SELECT *, 2 AS sort_order
  FROM table1
ORDER BY sort_order;

But the real answer is: other than the ORDER BY clause, the sort order will by arbitrary and not guaranteed.

Answer 2

I assume your tables are table1 and table2 respectively, and your solution is;

(select * from table1 MINUS select * from table2)
UNION ALL
(select * from table2 MINUS select * from table1)

Answer 3

The sort is used to eliminate the duplicates, and is implicit for DISTINCT and UNION queries (but not UNION ALL) - you could still specify the columns you'd prefer to order by if you need them sorted by specific columns.

For example, if you wanted to sort by the result sets, you could introduce an additional column, and sort by that first:

SELECT foo, bar, 1 as ResultSet
FROM Foo
WHERE bar = 1
UNION
SELECT foo, bar, 2 as ResultSet
FROM Foo
WHERE bar = 3
UNION
SELECT foo, bar, 3 as ResultSet
FROM Foo
WHERE bar = 2
ORDER BY ResultSet

Answer 4

I notice this question gets quite a lot of views so I'll first address a question you didn't ask!

Regarding the title. To achieve a "Sql Union All with “distinct”" then simply replace UNION ALL with UNION. This has the effect of removing duplicates.

For your specific question, given the clarification "The first query should have "priority", so duplicates should be removed from bottom" you can use

SELECT col1,
       col2,
       MIN(grp) AS source_group
FROM   (SELECT 1 AS grp,
               col1,
               col2
        FROM   t1
        UNION ALL
        SELECT 2 AS grp,
               col1,
               col2
        FROM   t2) AS t
GROUP  BY col1,
          col2
ORDER  BY MIN(grp),
          col1  

Answer 5

You can do something like this.

Select distinct name from  (SELECT r.name FROM outsider_role_mapping orm1 
    union all
SELECT r.name FROM user_role_mapping orm2
) tmp;

Answer 6

"UNION also sort the final output" - only as an implementation artifact. It is by no means guaranteed to perform the sort, and if you need a particular sort order, you should specify it with an ORDER BY clause. Otherwise, the output order is whatever is most convenient for the server to provide.

As such, your request for a function that performs a UNION ALL but that removes duplicates is easy - it's called UNION.

---

From your clarification, you also appear to believe that a UNION ALL will return all of the results from the first query before the results of the subsequent queries. This is also not guaranteed. Again, the only way to achieve a particular order is to specify it using an ORDER BY clause.