UNION clause questions 
Author Message
 UNION clause questions

I have been working on trying to understand the UNION operator and I
beieliev I have identified some specifics about using UNION that I have
not been able to find in the BOL and I also have some unanswered
questions I hope someone can help me with.

First, it does mention in BOL that an ORDER BY clause can only appear
after the last SELECT statement, so each SELECT does not get to specify
an ORDER BY.  What I also have found that is not mentioned is that the
ORDER BY clause can only cotain columns from the first SELECT
statement.  Can someone else verify that is the case?

Second, I have found that the way the records are returned by the UNION
is not consistant if an order by clause is not specified.  For example
if I have 3 tables (A, B & C) with the following structure:

[hmy] [int] IDENTITY (1, 1) NOT NULL ,
 [code1] [char] (8) NOT NULL

The values in the tables are:
A
1           a1
2           a2
3           a3

B
1           b1
2           b2
3           b3

C
1           c1
2           c2
3           c3

The statement:
SELECT * FROM A
UNION
SELECT * FROM B
results in:

hmy         code1
----------- --------
1           b1
2           b2
3           b3
2           a2
3           a3
1           a1

The order appears to be all the rows from table B and then the rows from
table B are sorted by the Primary Key.  However the rows in the other
table A, are not in any order.  I also do not understand why the rows
from the 2nd select statement are returned first.

When you add another select to the statement which pulls from the last
table, C, you get these results:

hmy         code1
----------- --------
1           a1
1           b1
1           c1
2           a2
2           b2
2           c2
3           a3
3           b3
3           c3

In this case it would appear that the rows were returned in order of the
Primary Key and that the rows from the different tables are mixed
together instead of seperated as in the previous UNION SELECT.

Can anyone offer any explination on this?

Thanks
Ed



Tue, 16 Mar 2004 11:03:44 GMT
 UNION clause questions

The Books Online states "Furthermore, when the SELECT statement includes a
UNION operator, the column names or column aliases must be those specified
in the first select list".  As an alternative, use the ordinal position
syntax in the ORDER BY clause.

Unless you specify ORDER BY, the sequence of the resultant rows is not
guaranteed.  SQL Server will return rows in the sequence it deems most
efficient unless constrained by ORDER BY.  Items like clustered indexes may
influence the ordering will not guarantee it.

Different query plans may yield different sequencing of rows.  This is most
likely why the addition of the third SELECT in your example changed the
sequence of the result.  Compare the execution plans if you want to find out
the specifics.

Hope this helps.

-----------------------
SQL FAQ links (courtesy  Neil Pike):

 http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
 (faqxxx.zip in lib 7)
 or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
 or www.sqlserverfaq.com
 or www.mssqlserver.com/faq
-----------------------


Quote:
> I have been working on trying to understand the UNION operator and I
> beieliev I have identified some specifics about using UNION that I have
> not been able to find in the BOL and I also have some unanswered
> questions I hope someone can help me with.

> First, it does mention in BOL that an ORDER BY clause can only appear
> after the last SELECT statement, so each SELECT does not get to specify
> an ORDER BY.  What I also have found that is not mentioned is that the
> ORDER BY clause can only cotain columns from the first SELECT
> statement.  Can someone else verify that is the case?

> Second, I have found that the way the records are returned by the UNION
> is not consistant if an order by clause is not specified.  For example
> if I have 3 tables (A, B & C) with the following structure:

> [hmy] [int] IDENTITY (1, 1) NOT NULL ,
>  [code1] [char] (8) NOT NULL

> The values in the tables are:
> A
> 1           a1
> 2           a2
> 3           a3

> B
> 1           b1
> 2           b2
> 3           b3

> C
> 1           c1
> 2           c2
> 3           c3

> The statement:
> SELECT * FROM A
> UNION
> SELECT * FROM B
> results in:

> hmy         code1
> ----------- --------
> 1           b1
> 2           b2
> 3           b3
> 2           a2
> 3           a3
> 1           a1

> The order appears to be all the rows from table B and then the rows from
> table B are sorted by the Primary Key.  However the rows in the other
> table A, are not in any order.  I also do not understand why the rows
> from the 2nd select statement are returned first.

> When you add another select to the statement which pulls from the last
> table, C, you get these results:

> hmy         code1
> ----------- --------
> 1           a1
> 1           b1
> 1           c1
> 2           a2
> 2           b2
> 2           c2
> 3           a3
> 3           b3
> 3           c3

> In this case it would appear that the rows were returned in order of the
> Primary Key and that the rows from the different tables are mixed
> together instead of seperated as in the previous UNION SELECT.

> Can anyone offer any explination on this?

> Thanks
> Ed



Tue, 16 Mar 2004 12:58:28 GMT
 UNION clause questions
In addition to Dan's comment, even though you can only specify column names
( or ordinals) from the first query, all records from all of the Select
statements are sorted in that order... If you wish to have the recs sorted
by which query , use a literal, ie

select 3 as ord,......
Union
select 1,......
Union
select 2,......
order by ord

--
Wayne Snyder SQL Server MVP
IKON Education Services, C{*filter*}te, NC
(Please Respond to newsgroups)
I proudly support the PASS SQL Server user community and its upcoming user
event,
PASS 2001 North America.
For details, visit < http://www.***.com/ > www.sqlpass.org.)



Tue, 16 Mar 2004 19:31:30 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. UNION Clause Question

2. UNION clause syntax question

3. SQL question: using UNION in FROM clause with DB2 V5

4. Union Clause

5. UNION and ORDER BY clause

6. problem with UNION and not in clause

7. Parameterised queries and UNION clause

8. Stored Procedure - TOP/UNION clauses

9. Having Union clause in a view

10. INTO Clause with UNION?

11. Union Clause help needed

12. Using Union subquery in select clause


 
Powered by phpBB® Forum Software