Unions for Dummies

I have to use some very extensive, long SQL code devleoped by others.  They make use of UNIONS.

Can someone explain to me the basic concepts behind UNIONs and how practical they are?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I could explain it but I think this does a better job.


If you have any specific questions let me know.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Joe WoodhousePrincipal ConsultantCommented:
UNION means "run one or more SELECTs and combine their resultsets into a single resultset, by default sorting to remove any duplicates that appear in more than one".

(You use UNIONALL to say "but don't bother removing duplicates".)

The resultsets have to have the same number of columns, with the same datatypes and in the same order, but other than that almost anything is legal.

I kinda think of them as saying "select a row from the first query OR from the next". (And indeed in some SQL dialects they are a more efficient way to rewrite queries that use OR clauses.)
awking00Information Technology SpecialistCommented:
See attached.
Please refer this link for UNION
1 Understand Set Theory (maths)
2 Appreciate that the Relational paradigm is based on Set Theory.  Therefore many of the verbs are straight implementations of Set Theory.  They are not isolated weird words made up by RDBMS vendors.
3  Structured Query Language was constructed to allow access to RDBMSs.  Union is a Set Theory term, implemented in SQL.

Now read the links above


Practial ?  Perfectly practical when approached with this understanding, and when the Db is relational.  A complete mess when either is not true.

Practical ?  Well from a performance perspective, no.  The server has to create temporary worktables to process the more-than-one result set; remove duplicates, etc and serve it to you, therefore it is vulnerable to abuse (large result sets can strangle any server).

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.