When is UNION used?

Question

When is UNION used?

tomiz

SSCommitted

Points: 1842
More actions
August 25, 2002 at 8:59 am

#79369

Hello!
I've made about 50 stored procs and I haven't used a single UNION statement. I've only used INNER JOINs. Should I be worried? When exactly should I use UNION?
/Tomi

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Unions are really only needed to join to data sets that are similiar. Example may be a history table and a current table with the eact same columns you want the two to look to the viewer or report as one table so there you would union them. Most times you will not need to perform unions.

"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)

ShaunCool SSC Rookie Points: 27 More actions · Answer 2

I have often used UNION to display data in a recordset that isn't actually THERE to populate a select listbox - such as SELECT NULL,'Please Select From List' UNION SELECT a.num, a.name FROM table a; as a shortcut to adding a few lines of code into a complied program.

K. Brian Kelley SSC Guru Points: 114602 More actions · Answer 3

Best case there is to use a UNION ALL if you can. It's a little cheaper on the operation. The difference between UNION and UNION ALL is that with UNION only distinct records are returned. So it's like slapping a DISTINCT on the whole query. If you know up front all records are going to be unique or if you want duplicates, use UNION ALL.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

katravax Old Hand Points: 353 More actions · Answer 4

UNIONs are also valuable when you need to use more than a single index for a given query. Normally, a single select will only use a single index per table, but a UNION allows you to cheat. For example, given this table:

col1, col2, col3, col4, col5

where col1 and col2 each had separate indexes, if you wanted this query:

SELECT * FROM table1 WHERE col1 = 'MyVal' OR col2 = 'MyOtherVal'

You'd be up a creek because the query would have to pick the index on col1 or the index on col2, but in no case would it use both. It would most likely result in a table scan, especially because of the OR.

However, this query written as a UNION would be able to use both indexes:

SELECT * FROM table1 WHERE col1='MyVal' -- will use the index on col1

UNION

SELECT * FROM table1 WHERE col2 = 'MyOtherVal' -- will use the index on col2

would be very fast. The queries could individually use the appropriate index, and the output sets will be joined as a single set.

- Troy King

DeanGroovy Right there with Babe Points: 782 More actions · Answer 5

I've found UNIONs particularly useful in views developed for Crystal Reports when summarizing data that I couldn't figure out an easy or correct way to do the JOINs. You can't do UNIONs from within Crystal Reports. Crystal Decisions has a SQL Designer that you can base your report from and use UNIONs, but you can't always use SQL Designer if Crystal Reports is integrated into a software package (Great Plains, Pivotal, etc.). No doubt the Relational Theory has mathematical models for when to use UNIONs with sets of data but I don't have any of those books handy.

tomiz SSCommitted Points: 1842 More actions · Answer 6

damn, 5 answers in a day!

Thank you all! This really helped!

/Tomi