OracleBlog

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