Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Union


Union

Author
Message
Andy Warren
Andy Warren
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: Moderators
Points: 8178 Visits: 2711
Comments posted to this topic are about the item Union

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Samuel Vella
Samuel Vella
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 2141
Except and Intersect - Fantastic, something I didn't know had been added for 2005

I've used (to great effect i might add) the extended UNION (INTERSECT and MINUS) features which have been available in Oracle since 8i and I really missed them when I made the shift to SQL Server 7/2000.

Good to find out they were added for SQL Server 2005 they are a boon in some cases where a join might not be practicle
SuperDBA-207096
SuperDBA-207096
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1597 Visits: 711
Nice article!
I've seen some weirdness mixing unions and union all with unexpected results in the past.

select ...
union
select ....
union
select ....
union all
select ....

something to beware of I guess in the future.
Samuel Vella
Samuel Vella
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 2141
If you wanted to use several unions like that then I think it would be wise to wrap sets together.

if you had 3 tables, each with identicle columns (just different row data)
Table1, table2 and table3
and you wanted data from table2 which was not in table1 or table3 then I think the following would be best:


select *
from table2
except
select *
from (select *
from table1
union
select *
from table3) as ExTables


Andy Warren
Andy Warren
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: Moderators
Points: 8178 Visits: 2711
Interesting how the one extra sentence at the end added more value than I thought!

Mixing UNION and UNION ALL - definitely interesting. I think I'd be inclined to separate them with parens to give a good visual signal about what I was trying to achieve and why.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
bc_
bc_
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1410 Visits: 7312
it would be great if you wrote a little article like this one on the topics of EXCEPT and INTERSECT. i was aware of those, but have yet to find a use for them.

thanks,
bc

bc
Andy Warren
Andy Warren
SSCrazy Eights
SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)SSCrazy Eights (8.2K reputation)

Group: Moderators
Points: 8178 Visits: 2711
I will add them to my todo list!

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Samuel Vella
Samuel Vella
SSC-Addicted
SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)SSC-Addicted (489 reputation)

Group: General Forum Members
Points: 489 Visits: 2141
The except and intercept clauses are great if you want to compare 2 data sets but for whatever reason cannot use the primary keys. Or you need to compare 2 tables with many columns to try and find the difference between them when that difference could be in any column.
You could build a query with joins and then a where clause which compares all the columns. Or you can use the EXCEPT keyword and simply select from both tables.

For a real world example, imagine you have an ETL procedure. A large part of this is contained within some stored procedures which populate a pre-extraction table which being a flattened version of a normalised database has many many columns.
You've made a change to the scripts as some of the values weren't being transformed correctly.
You now need to check that a) the script has made the changes you wanted it to and b) the script isn't inadvertantly making any other changes.
You can copy the pre-extraction table, make the changes and then run the scripts. You now have two copies of the data, one pre change, the other post change.

To write the sql query, you can use something like the following to get the column names (saves typing)
select column_name + ','
from information_schema.columns
where table_name = ' '
order by ordinal_position



remove any columns you don't want to compare and then build the query:
select col1, col2, col3...col99
from postchange_table
EXCEPT
select col1, col2, col3...col99
from prechange_table



the return should only give the rows you expect to have changed. Any more and you've changed more than you expected, any less or zero and nothing has happened
Julie Breutzmann
Julie Breutzmann
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1121 Visits: 1913
I was surprised the first time I encountered this feature of UNION. It's good to share this for others who may also be unaware.

I didn't know about Interest and Except were added in 2005 either. Thanks for a brief and helpful article.
Anipaul
Anipaul
SSCertifiable
SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)SSCertifiable (6.6K reputation)

Group: General Forum Members
Points: 6567 Visits: 1407
Nice article...



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search