Unions for Dummies

Posted on 2008-11-11
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

    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
    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 31

    Assisted Solution

    See attached.
    LVL 18

    Assisted Solution

    Please refer this link for UNION
    LVL 6

    Assisted Solution

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    Via a live example, show how to take different types of Oracle backups using RMAN.

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now