Wednesday, August 10, 2005

UNION ALL

You want to write a query that contains the rows from 2 or more tables. What you want to use is one of Oracle's set operators: UNION, INTERSECT, or MINUS. (Note: in the ANSI SQL standard, MINUS is referred to as EXCEPT). My example will deal with UNION.

You may have tables containing your employees, contractors and clients, each with their own unique and appropriate columns. For illustrative purposes, however, let's consider only their names and phone numbers.

CREATE TABLE Employee (e_name VARCHAR2(32), e_phonenumber VARCHAR2(16));
CREATE TABLE Contractor (c_name VARCHAR2(32), c_phonenumber VARCHAR2(16), c_startcontract DATE, c_endcontract DATE);
CREATE TABLE Client (c_name VARCHAR2(32), c_phonenumber VARCHAR2(16));

Let's get some sample data:

INSERT INTO Employee VALUES ('Joe Smith', '555-555-1234');
INSERT INTO Contractor VALUES ('Adam Johnson', '555-555-8888', '01-Jan-04', '01-Mar-04');
INSERT INTO Contractor VALUES ('Bob Jackson', '555-555-1111', '01-Jan-04', NULL);
INSERT INTO Contractor VALUES ('Adam Johnson', '555-555-8888', '01-Jan-05', '01-Mar-05');
INSERT INTO Client VALUES ('Bill Taylor', '555-555-6767');
INSERT INTO Client VALUES ('Adam Johnson', '555-555-8888');

What you would like to do is create a view to contain all the phone numbers. You can use the UNION operator, which is very easy. Write your queries however you wish, just make sure that they all have the same number of columns and similar data types.

SELECT e_name, e_phonenumber FROM Employee
UNION
SELECT c_name, c_phonenumber FROM Contractor
UNION
SELECT c_name, c_phonenumber FROM Client;


E_NAME E_PHONENUMBER
-------------------------------- ----------------
Adam Johnson 555-555-8888
Bill Taylor 555-555-6767
Bob Jackson 555-555-1111
Joe Smith 555-555-1234



Excellent!

But observe two things:
1. The order of the results have been re-arranged
2. There are no duplicates.

Actually, 1 and 2 are tied closely together. Oracle re-arranges the results in order to put identical rows next to each other and remove duplicates. On large tables, you may get a bit of a performance hit.

If you don't care about removing duplicates, or especially if you want the duplicates, use UNION ALL instead:

SELECT e_name, e_phonenumber FROM Employee
UNION ALL
SELECT c_name, c_phonenumber FROM Contractor
UNION ALL
SELECT c_name, c_phonenumber FROM Client;

E_NAME                           E_PHONENUMBER
-------------------------------- ----------------
Joe Smith 555-555-1234
Adam Johnson 555-555-8888
Bob Jackson 555-555-1111
Adam Johnson 555-555-8888
Bill Taylor 555-555-6767
Adam Johnson 555-555-8888


If you want them sorted, but don't want duplicates removed, you can include an ORDER BY clause. Use UNION with ORDER BY if you want duplicates removed and you want a guaranteed order.

SELECT e_name, e_phonenumber FROM Employee
UNION ALL
SELECT c_name, c_phonenumber FROM Contractor
UNION ALL
SELECT c_name, c_phonenumber FROM Client
ORDER BY 1;


E_NAME                           E_PHONENUMBER
-------------------------------- ----------------
Adam Johnson 555-555-8888
Adam Johnson 555-555-8888
Adam Johnson 555-555-8888
Bill Taylor 555-555-6767
Bob Jackson 555-555-1111
Joe Smith 555-555-1234


If you just want to remove duplicates within tables, but not in the merged set, try the DISTINCT clause.

SELECT DISTINCT e_name, e_phonenumber FROM Employee
UNION ALL
SELECT DISTINCT c_name, c_phonenumber FROM Contractor
UNION ALL
SELECT DISTINCT c_name, c_phonenumber FROM Client;


E_NAME E_PHONENUMBER
-------------------------------- ----------------
Joe Smith 555-555-1234
Adam Johnson 555-555-8888
Bob Jackson 555-555-1111
Adam Johnson 555-555-8888
Bill Taylor 555-555-6767



I guess it all depends what you want.

Here is Dan Morgan's reference on Built-In Operators:
http://www.psoug.org/reference/ora_operators.html

But you also want to check out the Oracle SQL Reference, Chapter 8: SQL Queries and Subqueries.

For your reading pleasure, check out this article by Jonathan Gennick on set operators, including UNION:
http://five.pairlist.net/pipermail/oracle-article/2003/000003.html

Administrative note: I've added a new section to include the links to all the Oracle blogs I regularly visit, to save you from going here:
http://thinkoracle.blogspot.com/2005/07/oracle-blogs.html

Comments:
I'm a little surprised by your implication that if you use a UNION then Oracle will sort the results for you without you using an order by.

It's definately what it appears to do, but Oracle makes no promises of the fact. Certainly I can find no evidence here or here.
I believe it wil sort the results in a way that will make it possible for the UNION to take place, which may or may not be sorted as far as a human user is concerned.

To quote Tom Kyte (from here)

"you want data sorted? You better use ORDER BY"
 
Thanks - that was a serious enough error that I edited the article.

Thanks also for the links. Based on appearances, it's easy to assume that UNION will predictably order your results, but that's actually an unsafe, false assumption to rely on.
 
The website is very useful for freshers like me
continue ur support to people like me
hats off!!
 
THIS IS GOOD FOR UNDERSTAND OF THE UNION BUT I AM FRESHER AS I AM LEARNIG THE ORACLE WILL YOU HELP ME TO UNDERSTAND ORCALE QUARIES.

THANKS,
AMIT PATEL
 
Thanks. I was looking for using order by with union all. your blog helped me a lot..
 
This comment has been removed by a blog administrator.
 
its almost 5 years and still helping with info to users. Thats cool.
 
Thanks, it helped big time!!!
 
Very very helpful... written categorically with good thought... saved my day
 
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?