Unions for Dummies

lcor
lcor used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
I could explain it but I think this does a better job.

http://www.w3schools.com/sql/sql_union.asp

If you have any specific questions let me know.
Joe WoodhousePrincipal Consultant
Most Valuable Expert 2012
Commented:
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 Specialist
Commented:
See attached.
comments.txt
Commented:
Please refer this link for UNION
http://ist.marshall.edu/ist466/join.html 
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).

Cheers

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial