Oracle UNION

Education is not limited to just classrooms. It can be gained anytime, anywhere... - Ravi Ranjan (M.Tech-NIT)

In Oracle, UNION operator is used to combine the result sets of two or more Oracle SELECT statements. It combines the both SELECT statement and removes duplicate rows between them./p>

Each SELECT statement within the UNION operator must have the same number of fields in the result sets with similar data types.

Syntax

  1. SELECT expression1, expression2, ... expression_n  
  2. FROM table1  
  3. WHERE conditions  
  4. UNION  
  5. SELECT expression1, expression2, ... expression_n  
  6. FROM table2  
  7. WHERE conditions;   

Parameters

1) expression1, expression2, ... expression_n: It specifies the columns that you want to retrieve.

2) table1, table2: it specifies the tables from where you retrieve the records.

3) conditions: it specifies the conditions that must be fulfilled for the records to be selected.

Note: The number of expressions must be same in both of the SELECT statements.

Oracle UNION Example: (Fetch single field)

  1. SELECT supplier_id  
  2. FROM suppliers  
  3. UNION  
  4. SELECT supplier_id  
  5. FROM order_details  

output

Oracle Union

In this example, supplier_id is defined in both of the table "suppliers" and "order_details". After the UNION, it would appear once in the result set because Oracle UNION operator removes duplicate sets.

Note: If you don't want to remove duplicates, use Oracle UNION ALL operator.

Oracle UNION Example: (Using ORDER BY)

The Oracle UNION operator can be used with ORDER BY clause to orders the results of the query.

  1. SELECT supplier_id, supplier_name  
  2. FROM suppliers  
  3. WHERE supplier_id <= 20  
  4. UNION  
  5. SELECT s_id, s_name  
  6. FROM shopkeepers  
  7. WHERE s_name = 'dhirubhai'  
  8. ORDER BY 1;  

Output

Oracle Union 2

In the above example, result is sorted by supplier_name/s_name in ascending order, as denoted by ORDER BY 1.