UNION

 UNION operator is used to combine the result sets of two or more SELECT statements into a single result set. The UNION operator ensures that only distinct rows are returned. Here's a detailed breakdown of how UNION works, its usage, and important notes:

1. Basic Syntax of UNION

SELECT column1, column2, ...

FROM table1

WHERE condition1

UNION

SELECT column1, column2, ...

FROM table2

WHERE condition2;

• Each SELECT statement must have the same number of columns in the result set.
• The corresponding columns must have compatible data types.
• The UNION operator removes duplicate rows, meaning only unique rows will appear in the final result set.

2. Key Points to Remember About UNION

a. Number of Columns

• Both SELECT statements involved in the UNION must return the same number of columns.
• The data types of the corresponding columns must be compatible. If necessary, Oracle will implicitly perform type conversion (e.g., converting NUMBER to VARCHAR, if required).

b. Column Names

• The column names in the result set are determined by the first SELECT statement in the union. So, the column names from the first query will be used in the final output.

c. Sorting the Results

• If you want to sort the final result set, you should use ORDER BY at the end of the query. The ORDER BY clause is applied to the entire result set after the union of all queries.

SELECT column1, column2

FROM table1

UNION

SELECT column1, column2

FROM table2

ORDER BY column1;

d. Removing Duplicates

• The UNION operator removes duplicate rows by default. If you want to retain duplicates, you should use UNION ALL.

3. Using UNION ALL

• UNION ALL is similar to UNION, but unlike UNION, it does not remove duplicate rows. This can be more efficient when you are certain that there will be no duplicates or when duplicates are acceptable in the final result.

Example:

SELECT column1, column2

FROM table1

UNION ALL

SELECT column1, column2

FROM table2;

Performance Consideration:

• UNION ALL is generally faster than UNION because it does not have to perform the additional step of removing duplicates. If you're confident there are no duplicates or if duplicates are acceptable, using UNION ALL will be more efficient.

4. Handling NULLs

• Both UNION and UNION ALL treat NULL values as equal. For example, if one SELECT returns a NULL value for a particular column and another SELECT returns NULL for the same column, only one NULL will appear in the final result set when using UNION, because NULL values are considered duplicates.

Example:

SELECT column1 FROM table1

UNION

SELECT column1 FROM table2;

If both table1 and table2 contain rows with NULL in column1, only one NULL will appear in the result set.

5. Order of Execution

• The queries within a UNION are executed sequentially, meaning Oracle will first execute the first SELECT statement, then the second, and combine the results.

6. Performance Considerations

• UNION: Since it removes duplicates, it has to perform additional processing to check for and eliminate those duplicates. This can impact performance, especially with large datasets.
• UNION ALL: Since it does not perform this step, it can be faster, especially when dealing with large datasets.

7. Example Use Case: Combining Results from Multiple Tables

Consider two tables, employees and contractors, both of which have a name and salary column. You can use UNION to combine these results:

SELECT name, salary

FROM employees

WHERE status = 'active'

UNION

SELECT name, salary

FROM contractors

WHERE status = 'active';

This will give you a combined list of active employees and contractors with no duplicates.

8. Restrictions with UNION

• Column data types: The data types of corresponding columns must be compatible (e.g., you can't union a VARCHAR column with a DATE column).
• Order of Results: The result set will not necessarily maintain the order of rows from each individual query unless you explicitly use ORDER BY.

9. Advanced Example: Combining Data with Different Column Names

If the SELECT statements return different column names, you can alias the columns to ensure the result set has consistent column names.

SELECT name AS person_name, salary AS person_salary

FROM employees

WHERE status = 'active'

UNION

SELECT name AS person_name, salary AS person_salary

FROM contractors

WHERE status = 'active';

This ensures that both queries return the same column names (person_name and person_salary) in the result set.

10. Use Cases for UNION

• Combining data from different tables with the same structure.
• Merging data from different time periods or different regions.
• Eliminating duplicate records when you are combining data from multiple sources.

Conclusion

The UNION operator in Oracle is a powerful tool for combining result sets from multiple queries. It’s particularly useful for eliminating duplicate records and combining data from different tables with the same or similar structure. However, when performance is a concern and duplicates are not an issue, you can use UNION ALL for better efficiency.