Oracle UNION with Examples

In this tutorial, we will be learning about the UNION and UNION ALL set operator in Oracle.

The set operators work on the same principle as set operators in mathematics. Before we begin understanding the set operators in Oracle, lets revisit the concept of set operators in Maths through this example:

Here, A and B are two sets containing numbers. In Oracle, we can think of A and B as tables with elements in rows:

If, 
A = { 1 2 3 4 }
B = { 3 4 5 6 }

Then,

A Union B -- Contains all unique elements from both set 
= { 1 2 3 4 5 6 } 
A Intersection B -- Contains elements common in both sets
= { 3 4 }
A Minus B -- Elements which are unique to set A
= { 1 2 }
B Minus A -- Elements which are unique to set B
= { 5 6 }
A Union All B -- Contains all elements from both sets
= { 1 2 3 4 3 4 5 6 } 
= { 1 2 3 3 4 4 5 6 }

Now that we understand the concept of set operators in mathematics, let’s see how they work in Oracle. The Oracle offers following SET operators:

1. UNION
2. UNION ALL
3. INTERSECT
4. MINUS

Syntax

The syntax of UNION set operator in Oracle is as follows:

SELECT
    column_list1
FROM
    Table1
[UNION/ UNION ALL]
SELECT
    column_list1
FROM
    Table2;

Here,

column_list1 and column_list2 – These are the names of columns from each table. The number of columns in column_list1 must be equal to number of columns in column_list2. They should also belong to the same data type group, i.e, Number or Characters.

Table1 and Table2 – Names of Table that we want to combine using set operator.

[ UNION / UNION ALL ] – The Oracle keywords for Set Operators. UNION gives us a combined set with unique elements from both tables while UNION ALL contains all elements, including the duplicate ones.

Let’s see some queries to understand this better.

Oracle UNION and UNION ALL Examples

Let’s create two tables named department_a and department_b containing list of interns working in them. Some of the interns are common in both departments.

CREATE TABLE department_a (
    intern_id INT PRIMARY KEY,
    name VARCHAR2 (50) NOT NULL
);

CREATE TABLE department_b (
    intern_id INT PRIMARY KEY,
    name VARCHAR2 (50) NOT NULL
);

-- insert data in department_a table

INSERT INTO department_a (intern_id, name)
VALUES (1, 'Eva');

INSERT INTO department_a (intern_id, name)
VALUES (2, 'Sia');

INSERT INTO department_a (intern_id, name)
VALUES (3, 'Tim');

INSERT INTO department_a (intern_id, name)
VALUES (4, 'Ray');

-- insert data in department_b table

INSERT INTO department_b (intern_id, name)
VALUES (3, 'Tim');

INSERT INTO department_b (intern_id, name)
VALUES (4, 'Ray');

INSERT INTO department_b (intern_id, name)
VALUES (5, 'Ashley');

INSERT INTO department_b (intern_id, name)
VALUES (6, 'Alexa');

Result:

1. Oracle UNION Example

Suppose, we want to see a list of all interns working in both departments, for this we will use UNION set operator. Let’s see the query.

SELECT
    intern_id,
    name
FROM
    department_a
UNION 
SELECT
    intern_id,
    name
FROM
    department_b;

Result:

The result set contains all unique rows with no duplicate values. We get a list of all interns working in both departments using this query.

2. Oracle UNION with ORDER BY example

We can also use ORDER BY with set operator to sort data in ascending or descending order based on some column:

SELECT
    intern_id,
    name
FROM
    department_a
UNION 
SELECT
    intern_id,
    name
FROM
    department_b
ORDER BY
name DESC;

Result:

3. Oracle UNION ALL example

UNION ALL set operator is used for giving a list of all values in both tables which includes duplicate values. Let’s have a look at the query :

SELECT
    intern_id,
    name
FROM
    department_a
UNION ALL
SELECT
    intern_id,
    name
FROM
    department_b;

Result:

As, we can see Interns with inter_id = 3 and 4 are repeated twice. Using UNION ALL set operator in query gives us a list of all values in both tables.

Oracle UNION vs JOIN

A UNION is used when we want to append the result set vertically, while Oracle join is used for appending tables horizontally.

Let’s see the difference between two, by using Oracle Join and Union Set operator on Department_a and Department_b tables.

Oracle UNION:

SELECT
    intern_id,
    name
FROM
    department_a
UNION 
SELECT
    intern_id,
    name
FROM
    department_b;

Result:

Oracle Join:

SELECT
    a.intern_id id_a,
    a.name name_a,
    b.intern_id id_b,
    b.name name_b
FROM
    department_a a
INNER JOIN department_b b ON a.intern_id = b.intern_id;

Result: ( Appended Horizontally)

As we can see, only matching values from both tables were appended horizontally when using Inner Join.

Note: You should keep this things in mind whien using set operator:

1. Number of columns in both subqueries should be same.
2. The data type of each corresponding column in both the queries should be same.

In this tutorial, we learned how to use UNION and UNION ALL set operator in query.