Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Unions for Dummies

Posted on 2008-11-11
Medium Priority
Last Modified: 2012-05-05
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?
Question by:lcor
LVL 16

Accepted Solution

brad2575 earned 400 total points
ID: 22931481
I could explain it but I think this does a better job.


If you have any specific questions let me know.
LVL 24

Assisted Solution

by:Joe Woodhouse
Joe Woodhouse earned 400 total points
ID: 22931498
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.)
LVL 32

Assisted Solution

awking00 earned 400 total points
ID: 22933508
See attached.
LVL 18

Assisted Solution

sventhan earned 400 total points
ID: 22940460
Please refer this link for UNION

Assisted Solution

IncisiveOne earned 400 total points
ID: 22949099
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).


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question