UNION FAQS

1. What is the difference between UNION and UNION ALL in Oracle SQL?

• UNION combines the result sets of two or more SELECT statements and removes duplicate rows. It ensures that only distinct rows are returned.
• UNION ALL also combines the result sets but does not remove duplicates. It will return all rows, including duplicates, making it more efficient in terms of performance when you don't need duplicate elimination.

2. Do the SELECT statements need to have the same number of columns when using UNION?

Yes, both SELECT statements involved in the UNION must return the same number of columns. The columns must also be compatible in terms of data types.

3. Can the columns in the SELECT statements of a UNION have different names?

Yes, the column names in each SELECT statement can differ. However, the column names in the final result set will be determined by the first SELECT statement. To ensure consistency, you can use column aliases (e.g., SELECT column_name AS alias_name).

4. How does UNION handle NULL values?

UNION treats NULL values as duplicates. If two SELECT statements return rows with NULL in the same column, only one NULL will appear in the final result set.

5. Does UNION perform any sorting of the result set?

By default, UNION does not guarantee any specific order for the result set. If you want to sort the combined result, you must use an ORDER BY clause after the entire UNION.

SELECT column1 FROM table1

UNION

SELECT column1 FROM table2

ORDER BY column1;

6. What happens if the columns in the SELECT statements have different data types?

The data types of the corresponding columns in the SELECT statements must be compatible. Oracle will attempt to perform implicit type conversion if necessary. If the data types are incompatible (for example, trying to union a DATE with a VARCHAR), an error will occur.

7. Can I use UNION with more than two SELECT statements?

Yes, you can use UNION to combine more than two SELECT statements. The general syntax would be:

SELECT column1 FROM table1

UNION

SELECT column1 FROM table2

UNION

SELECT column1 FROM table3;

8. Can I use UNION with WHERE, GROUP BY, or HAVING clauses?

Yes, you can use WHERE, GROUP BY, HAVING, and other clauses within individual SELECT statements that are part of a UNION. Each query within the UNION can be as complex as needed.

9. Does UNION work across different tables or only within the same table?

UNION can be used across different tables. As long as the SELECT statements return the same number of columns with compatible data types, you can combine data from different tables.

10. How do I improve performance when using UNION?

• Use UNION ALL when you do not need to eliminate duplicates. It’s faster because it skips the step of duplicate checking.
• Ensure your queries are optimized, especially if you're working with large datasets.
• Indexing relevant columns in the tables involved can also help improve performance.

11. Can I use ORDER BY within each individual SELECT in the UNION?

No, the ORDER BY clause can only be used at the end of the entire UNION query to sort the final result set. It cannot be used within each individual SELECT statement in the UNION.

SELECT column1 FROM table1

UNION

SELECT column1 FROM table2

ORDER BY column1;

12. What is the performance difference between UNION and UNION ALL?

• UNION will perform additional work to remove duplicates, which can be resource-intensive, especially for large datasets.
• UNION ALL does not remove duplicates, so it generally performs faster because it skips the duplicate elimination step.

13. Can UNION combine queries with different conditions?

Yes, you can apply different conditions (using WHERE, GROUP BY, etc.) to each query in the UNION. Each query in the union can have its own filtering and grouping logic, but the final result will combine the rows from each query.

SELECT column1 FROM table1 WHERE column2 > 100

UNION

SELECT column1 FROM table2 WHERE column2 <= 100;

14. Can UNION be used to combine results from different databases or schemas?

Yes, you can use UNION to combine data from tables in different schemas, provided you have the necessary permissions. You would just need to reference the full schema and table name (e.g., schema1.table1 and schema2.table2).

15. Can I use UNION with subqueries?

Yes, you can use subqueries in the SELECT statements of a UNION. Each subquery must return the same number of columns and have compatible data types.

SELECT column1 FROM (SELECT column1 FROM table1 WHERE condition)

UNION

SELECT column1 FROM (SELECT column1 FROM table2 WHERE condition);