SQL Server UNION - What is the default ORDER BY Behaviour

sql sql-server union sql-order-by
Ray Booysen picture Ray Booysen · Jan 7, 2009 · Viewed 26.9k times · Source

If I have a few UNION Statements as a contrived example:

SELECT * FROM xxx WHERE z = 1
UNION 
SELECT * FROM xxx WHERE z = 2
UNION
SELECT * FROM xxx WHERE z = 3

What is the default order by behaviour?

The test data I'm seeing essentially does not return the data in the order that is specified above. I.e. the data is ordered, but I wanted to know what are the rules of precedence on this.

Another thing is that in this case xxx is a View. The view joins 3 different tables together to return the results I want.

Answer

DJ. picture DJ. · Jan 7, 2009

There is no default order.

Without an Order By clause the order returned is undefined. That means SQL Server can bring them back in any order it likes.

EDIT: Based on what I have seen, without an Order By, the order that the results come back in depends on the query plan. So if there is an index that it is using, the result may come back in that order but again there is no guarantee.

Related questions

Order by clause with Union in Sql Server

I want List of party names with 1st option as 'All' from database. but i won't insert 'All' to Database, needs only retrieve time. so, I wrote this query. Select 0 PartyId, 'All' Name Union select PartyId, Name from PartyMst This …

Read More
How to use group by with union in t-sql

How can I using group by with union in t-sql? I want to group by the first column of a result of union, I wrote the following sql but it doesn't work. I just don't know how to reference the …

Read More
SELECT INTO USING UNION QUERY

I want to create a new table in SQL Server with the following query. I am unable to understand why this query doesn't work. Query1: Works SELECT * FROM TABLE1 UNION SELECT * FROM TABLE2 Query2: Does not Work. Error: Msg 170, Level 15, …

Read More