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;
Visual Expert 2024
 VEPLSQLRULE52