[sql] How to execute UNION without sorting? (SQL)

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

This question is related to sql union distinct union-all

The answer is


Consider these tables (Standard SQL code, runs on SQL Server 2008):

WITH A 
     AS 
     (
      SELECT * 
        FROM (
              VALUES (1), 
                     (2), 
                     (3), 
                     (4), 
                     (5), 
                     (6) 
             ) AS T (col)
     ),
     B 
     AS 
     (
      SELECT * 
        FROM (
              VALUES (9), 
                     (8), 
                     (7), 
                     (6), 
                     (5), 
                     (4) 
             ) AS T (col)
     ), ...

The desired effect is this to sort table A by col ascending, sort table B by col descending then unioning the two, removing duplicates, retaining order before the union and leaving table A results on the "top" with table B on the "bottom" e.g. (pesudo code)

(
 SELECT *
   FROM A
  ORDER 
     BY col
)
UNION
(
 SELECT *
   FROM B
  ORDER 
     BY col DESC
);

Of course, this won't work in SQL because there can only be one ORDER BY clause and it can only be applied to the top level table expression (or whatever the output of a SELECT query is known as; I call it the "resultset").

The first thing to address is the intersection between the two tables, in this case the values 4, 5 and 6. How the intersection should be sorted needs to be specified in SQL code, therefore it is desirable that the designer specifies this too! (i.e. the person asking the question, in this case).

The implication in this case would seem to be that the intersection ("duplicates") should be sorted within the results for table A. Therefore, the sorted resultset should look like this:

      VALUES (1), -- A including intersection, ascending
             (2), -- A including intersection, ascending
             (3), -- A including intersection, ascending
             (4), -- A including intersection, ascending
             (5), -- A including intersection, ascending
             (6), -- A including intersection, ascending
             (9), -- B only, descending 
             (8), -- B only, descending  
             (7), -- B only, descending 

Note in SQL "top" and "bottom" has no inferent meaning and a table (other than a resultset) has no inherent ordering. Also (to cut a long story short) consider that UNION removes duplicate rows by implication and must be applied before ORDER BY. The conclusion has to be that each table's sort order must be explicitly defined by exposing a sort order column(s) before being unioned. For this we can use the ROW_NUMBER() windowed function e.g.

     ...
     A_ranked
     AS
     (
      SELECT col, 
             ROW_NUMBER() OVER (ORDER BY col) AS sort_order_1
        FROM A                      -- include the intersection
     ),
     B_ranked
     AS
     (
      SELECT *, 
             ROW_NUMBER() OVER (ORDER BY col DESC) AS sort_order_1
        FROM B
       WHERE NOT EXISTS (           -- exclude the intersection
                         SELECT * 
                           FROM A
                          WHERE A.col = B.col 
                        )
     )
SELECT *, 1 AS sort_order_0 
  FROM A_ranked
UNION
SELECT *, 2 AS sort_order_0 
  FROM B_ranked
ORDER BY sort_order_0, sort_order_1;

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)

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

Try this:

  SELECT DISTINCT * FROM (

      SELECT  column1, column2 FROM Table1
      UNION ALL
      SELECT  column1, column2 FROM Table2
      UNION ALL
      SELECT  column1, column2 FROM Table3

  ) X ORDER BY Column1

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;

1,1: select 1 from dual union all select 1 from dual 1: select 1 from dual union select 1 from dual


"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.


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.


select T.Col1, T.Col2, T.Sort
from 
    (
      select T.Col1,
             T.Col2,
             T.Sort,
             rank() over(partition by T.Col1, T.Col2 order by T.Sort) as rn
      from
          (
            select Col1, Col2, 1 as Sort
            from Table1
            union all
            select Col1, Col2, 2
            from Table2
          ) as T
    ) as T
where T.rn = 1    
order by T.Sort

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to union

Simplest way to form a union of two lists Group by with union mysql select query How to execute UNION without sorting? (SQL) SQL: how to use UNION and order by a specific select? How can I get the intersection, union, and subset of arrays in Ruby? UNION with WHERE clause SQL Server: How to use UNION with two queries that BOTH have a WHERE clause? Intersection and union of ArrayLists in Java How to order by with union in SQL? SELECT INTO USING UNION QUERY

Examples related to distinct

Using DISTINCT along with GROUP BY in SQL Server How to "select distinct" across multiple data frame columns in pandas? Laravel Eloquent - distinct() and count() not working properly together SQL - select distinct only on one column SQL: Group by minimum value in one field while selecting distinct rows Count distinct value pairs in multiple columns in SQL sql query distinct with Row_Number Eliminating duplicate values based on only one column of the table MongoDB distinct aggregation Pandas count(distinct) equivalent

Examples related to union-all

How to execute UNION without sorting? (SQL) What is the difference between UNION and UNION ALL?