Home

Description

The rule "UNION should be used with caution" in PL/SQL code means that when using the UNION operator, developers should be aware of the potential side effects. This is because UNION combines the results of two or more SELECT statements into a single result set, and can cause unexpected results if not used correctly. Developers should ensure that the columns being combined are compatible, and that the data types of the columns being combined are the same. Additionally, developers should be aware of the potential for duplicate records when using UNION, and should use the UNION ALL operator to prevent this from happening.

Key Benefits

• Increased complexity: Using UNION can add complexity to a query, making it more difficult to read and maintain.
• Performance: UNION can cause performance issues as it requires the database to process multiple queries and combine the results.
• Data integrity: UNION can lead to data integrity issues if the queries are not written correctly, or if the data is not formatted correctly.

Non-compliant Code Example

SELECT Name, Phone FROM Employee
UNION                                       --Non compliant code (Use UNION ALL instead of Union)
SELECT Name, Phone From Customer;

SELECT Name, Phone FROM Customer WHERE StateCode=0015
UNION                                       --Non compliant code (No need to use Union clause, get entries by changing Where clause)
SELECT Name, Phone From Customer WHERE StateCode=0028;

Compliant Code Example

SELECT Name, Phone FROM Employee
UNION ALL                                      --Compliant code (UNION ALL is used)
SELECT Name, Phone From Customer;

SELECT Name, Phone 
From Customer 
WHERE StateCode=0028 OR StateCode=0015;       --Compliant code