Home

Description

The rule "Individual WHERE clause conditions should not be unconditionally true or false" states that each condition in a WHERE clause should be evaluated and not be predetermined as either true or false. This means that each condition should be checked against the data and not be assumed to be true or false without any evaluation. This helps to ensure that the results of the query are accurate and that the query is not returning incorrect results due to an assumption that a condition is true or false.

Key Benefits

• Eliminates unnecessary query execution: By ensuring that individual WHERE clause conditions are not unconditionally true or false, unnecessary query execution can be eliminated.
• Improves query performance: By avoiding unnecessary query execution, query performance can be improved.
• Reduces code complexity: By ensuring that individual WHERE clause conditions are not unconditionally true or false, code complexity can be reduced.

Non-compliant Code Example

Create TABLE EMPLOYEE
(
  EMP_ID number(10) NOT NULL,
  FIRSTNAME NVARCHAR2(75) NOT NULL,
  LASTNAME NVARCHAR2(75),
  DEPT_ID int,  
  ADDRESS NOT NULL,
  CONSTRAINT pk_employee PRIMARY KEY (EMP_ID)
);

BEGIN
	SELECT
		*
	FROM
		EMPLOYEE
	WHERE
		FIRSTNAME IS NOT NULL; --Non compliant code (Where clause is unconditionally true as column definition contains not null column)
END;

Compliant Code Example

Create TABLE EMPLOYEE 
(
  EMP_ID number(10) NOT NULL,
  FIRSTNAME NVARCHAR2(75) NOT NULL,
  LASTNAME NVARCHAR2(75),
  DEPT_ID int,  
  ADDRESS NOT NULL,
  CONSTRAINT pk_employee PRIMARY KEY (EMP_ID)
);

BEGIN
	SELECT
		*
	FROM
		EMPLOYEE;   --Compliant code (Where clause is removed)
END;