• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1673
  • Last Modified:

when to Use "UNION" in queries

when to Use "UNION" in queries?can any tell me some example using emp or dept table
0
chaitu chaitu
Asked:
chaitu chaitu
4 Solutions
 
sapnamCommented:
UNION is a set operator i.e. operator which works on set of data. So if you have 2 queries and want to display the unique results returned by both of them, you use UNION operator.  The usage is

Query 1
UNION
Query 2

and so on

The requirement of ORacle is that all the queries used should have same number of columns and corresponding columns of all the queries should be of same type.

Normally UNION is used when the 2 queries are such that it is not possible to put them in one query, so you have 2 queries and use UNION.

You can also use UNION ALL.  UNION ALL will not filter unique values and return all values returned without eliminating duplicates
0
 
paquicubaCommented:
I wouldn't use Set Operator "UNION" for emp - dept join. It's more useful when you have two tables with very similar structure and data, for instance orders_list1 and orders_list2:
SELECT part
    FROM orders_list1
UNION
SELECT part
    FROM orders_list2;

UNION selects unique rows between the two tables and list them all in the same column "part"

UNION is not always the best alternative, since it sorts the results set in order to oputput distinct rows, UNION ALL is faster if just a complete list of all parts is needed.

Hope this helps!


 

0
 
sapnamCommented:
Example using EMP

First Query is returning employees of Dept 10

SELECT emp_code,emp_name
  FROM emp
 WHERE emp_dept  = '10';

Second Query is returning employees of Dept 20

SELECT emp_code,emp_name
  FROM emp
 WHERE emp_dept  = '20';

If you want both to be displayed

SELECT emp_code,emp_name
  FROM emp
 WHERE emp_dept  = '10'
UNION
SELECT emp_code,emp_name
  FROM emp
 WHERE emp_dept  = '20';

0
Independent Software Vendors: 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!

 
geotigerCommented:
In this case:

SELECT emp_code,emp_name
  FROM emp
 WHERE emp_dept  = '10'
UNION
SELECT emp_code,emp_name
  FROM emp
 WHERE emp_dept  = '20';

it can be replaced with this:

SELECT emp_code,emp_name
  FROM emp
 WHERE emp_dept  in ('10','20');

The better example would be that if another department is maitaining emp info in a separate table (my_depart_emp), you want to merge together with UNION:

SELECT emp_code,emp_name
  FROM emp
UNION
SELECT emp_code,emp_name
  FROM my_depart_emp;

GT

0
 
sapnamCommented:
As I had mentioned in my fisrt post

Normally UNION is used when the 2 queries are such that it is not possible to put them in one query, so you have 2 queries and use UNION.

Like, a real life example is when you have to report transactions for a G/L Code and the transactions are being reported from A/R Module,A/P Module, G/L Module etc. Each module has its own set of tables.

So you write one query to get details from A/R Module, another for G/L module and a third one for A/P module. It would be impossible to put all this in one query so you use the 3 queries with UNION (or UNION ALL) to give you the desired results
0
 
anmeetCommented:
Please check the following link.

http://www.really-fine.com/SQL_union.html
0
 
malachiteCommented:
Just to add to the conversation

Although UNION is a set operator, it is most often used incorrectly.  The correct use of UNION is to merge TWO sets of rows together, where one or more of the rows is in BOTH sets.  If you find yourself using UNION to join TWO completely different set of rows (with regards to data values) then you should be using UNION ALL instead of UNION.  UNION ALL will increase performance dramatically in these cases since you're basically telling ORACLE to accept all rows from both select statements, without checking for duplicate rows.

Example,  I have the following rows

EMPLOYEE
Name          Dept
Doug           1
John            1
Jane            2
Joe              2
Mike            3
Michelle       2
Doug           2

-- Example where UNION ALL should be used, since both SQL statements return a totally different set of rows
SELECT NAME, DEPT
FROM EMPLOYEE
WHERE DEPT = 1
UNION ALL
SELECT NAME,DEPT
FROM EMPLOYEE
WHERE DEPT = 2

-- Example where UNION should be used, since both SQL statements return the same rows (in this case, both will return MICHELLE
SELECT NAME, DEPT
FROM EMPLOYEE
WHERE NAME = 'MICHELLE'
UNION
SELECT NAME, DEPT
FROM EMPLOYEE
WHERE DEPT = 2

So, just in summation, be careful and analyze your where clause to determine if you need to use UNION or UNION ALL.  If you can use UNION ALL, then do so, since it will greatly speed performance.
0
 
malachiteCommented:
Note: the above examples are trivial, most of the time when I use union, I'm dealing with data sets that come from completely different sets of tables, have many joins, and are usually totally different in origion, but must be unioned together for purposes of reporting etc.  Case in point would be a typical UNION ALL of invoice line data with payment transaction data to produce a receipt showing what a customer bought, and how they paid.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now