Home
WHERE clause conditions should not be contradictory
Description
The rule "WHERE clause conditions should not be contradictory" states that when writing a WHERE clause in a PL/SQL statement, the conditions should not contradict each other. This means that the conditions should not be mutually exclusive, as this will cause the statement to return an empty result set. For example, the following statement would not be valid: WHERE x = 1 AND x = 2. This statement is contradictory because it is impossible for x to be both 1 and 2 at the same time.
Key Benefits
- Prevent Contradictions: WHERE clause conditions should not be contradictory, preventing any confusion or errors in the query.
- Improved Performance: WHERE clause conditions help to reduce the amount of data that needs to be processed, resulting in improved query performance.
- Accurate Results: WHERE clause conditions help to ensure that the query returns accurate results, as only the data that meets the criteria is returned.
Non-compliant Code Example
DECLARE
firstname NVARCHAR2(75);
lastname NVARCHAR2(75);
landmark NVARCHAR2(75);
BEGIN
SELECT FIRSTNAME, LASTNAME, LANDMARK
INTO firstname,lastname,landmark
FROM CUSTOMERS
WHERE (CREDIT_LIMIT = 1800 AND CREDIT_LIMIT = 1100); --Non compliant code (WHERE clause with the contradictory conditions)
END;
Compliant Code Example
DECLARE
firstname NVARCHAR2(75);
lastname NVARCHAR2(75);
landmark NVARCHAR2(75);
BEGIN
SELECT FIRSTNAME, LASTNAME, LANDMARK
INTO firstname,lastname,landmark
FROM CUSTOMERS
WHERE CREDIT_LIMIT >= 1100; --Compliant code (WHERE clause withouts the contradictory conditions)
END;