UNION ALL

UNION ALL operator in Oracle SQL is used to combine the result sets of two or more SELECT statements into a single result set. Unlike the UNION operator, UNION ALL does not remove duplicate rows. This makes UNION ALL faster and more efficient in scenarios where duplicate rows are acceptable or non-existent.

1. Basic Syntax of UNION ALL

SELECT column1, column2, ...

FROM table1

WHERE condition1

UNION ALL

SELECT column1, column2, ...

FROM table2

WHERE condition2;

• Each SELECT statement must return the same number of columns.
• The corresponding columns must have compatible data types.
• No duplicate removal: UNION ALL combines all rows, including duplicates, from both SELECT queries.

2. Key Characteristics of UNION ALL

a. No Removal of Duplicates

• The most important feature of UNION ALL is that it does not remove duplicate rows.
• If the result sets from the two SELECT queries contain identical rows, both occurrences of that row will appear in the final result set.

Example:

SELECT 'A' AS col

FROM dual

UNION ALL

SELECT 'A' AS col

FROM dual;

This query will return two rows with 'A' because UNION ALL does not eliminate duplicates.

b. Performance

• Faster than UNION: Since UNION ALL does not have to remove duplicates, it typically performs better than UNION, especially when dealing with large datasets.
• Less resource-intensive: It avoids the additional overhead that would come with sorting and filtering duplicates, making it more efficient when working with large amounts of data.

c. Column Compatibility

• Just like UNION, the SELECT statements combined using UNION ALL must have the same number of columns, and the corresponding columns must have compatible data types. Oracle will automatically handle type conversion if necessary (e.g., converting a NUMBER to a VARCHAR if required).

d. Order of Result Set

• UNION ALL does not guarantee any particular order for the combined result set. If you need a sorted result, you must include an ORDER BY clause at the end of the entire query.

Example:

SELECT column1 FROM table1

UNION ALL

SELECT column1 FROM table2

ORDER BY column1;

The ORDER BY clause will apply to the combined result set.

3. When to Use UNION ALL

a. When Duplicates are Acceptable or Expected

• Use UNION ALL when you want to include all records, even if some are duplicated.
• For example, if you are aggregating data from multiple sources where duplicates are expected and should be preserved, UNION ALL is appropriate.

b. Performance Benefits

• If you are sure that there will be no duplicates (or you don't mind if duplicates are included), using UNION ALL is significantly faster than UNION, since it does not need to perform the costly duplicate elimination step.

c. Combining Results from Similar Data

• When combining data from multiple tables or queries that have a similar structure and you don't care about eliminating duplicate rows.

4. Example Usage of UNION ALL

Example 1: Combine Two Result Sets

Suppose you have two tables: employees and contractors, and you want to list all employees and contractors.

SELECT name, salary FROM employees

UNION ALL

SELECT name, salary FROM contractors;

This will return all rows from both the employees and contractors tables, including any duplicates.

Example 2: Combine Data from Multiple Sources

You want to get a list of products available from different warehouses and merge them into one list.

SELECT product_id, product_name FROM warehouse1_products

UNION ALL

SELECT product_id, product_name FROM warehouse2_products

UNION ALL

SELECT product_id, product_name FROM warehouse3_products;

This will give you a combined list of products from all three warehouses, including any duplicate products that may appear in more than one warehouse.

Example 3: Adding a Condition to Each Query

If you want to combine data with specific conditions for each dataset:

SELECT employee_id, department FROM employees WHERE department = 'HR'

UNION ALL

SELECT contractor_id, department FROM contractors WHERE department = 'HR';

This query will return all employees and contractors in the "HR" department, without eliminating any duplicates.

5. Performance Considerations

·        Efficiency: Since UNION ALL does not perform the extra processing step of checking for duplicates, it is generally faster than UNION. This makes UNION ALL the preferred choice when you don't need to eliminate duplicates and are dealing with large datasets.

·        Query Optimization: When using UNION ALL, Oracle can optimize the query execution plan better than with UNION, because it doesn't need to sort the result set to eliminate duplicates.

6. UNION ALL with Subqueries

You can also use UNION ALL with subqueries. Here is an example where UNION ALL combines the results of two subqueries:

SELECT product_id FROM (SELECT product_id FROM products WHERE category = 'Electronics')

UNION ALL

SELECT product_id FROM (SELECT product_id FROM products WHERE category = 'Furniture');

This query combines products from the "Electronics" and "Furniture" categories, including duplicates.

7. Common Mistakes to Avoid

·        Using UNION ALL when duplicates should be eliminated: If your use case requires that duplicate rows be removed from the final result, use UNION instead of UNION ALL.

·        Not using ORDER BY for sorting: If you want to sort the final result set, remember to apply the ORDER BY clause at the end of the entire query. UNION ALL does not sort the data by default.

8. Comparison Between UNION and UNION ALL

Feature	UNION	UNION ALL
Duplicate Rows	Removes duplicates	Retains all rows, including duplicates
Performance	Slower (due to duplicate elimination)	Faster (no duplicate check)
Use Case	When you need distinct results	When you want all rows, including duplicates
Sorting	Implicit sorting to remove duplicates	No sorting unless explicitly specified with ORDER BY

9. Common Use Cases for UNION ALL

·        Combining logs: You can combine log entries from different log tables, especially when logs are generated from multiple sources.

·        Merging data from similar tables: When aggregating data from tables that have the same structure, like sales from multiple regions, customer information from multiple stores, etc.

·        Combining incremental data: If you want to add new records to an existing result set without worrying about duplicates, you can use UNION ALL.

 

Conclusion

UNION ALL is a powerful tool in Oracle SQL for combining result sets from multiple SELECT statements. It is faster than UNION because it doesn't perform the extra step of removing duplicates. Use UNION ALL when you are sure duplicates are either acceptable or don't exist in your datasets. It’s particularly beneficial for handling large datasets efficiently.