Skip to Content

Observation: Quantified Predicate with Subselect

You coded a subselect using a quantified predicate. If possible, consider recoding the predicate using either an explicit value or a subselect that returns an atomic value.

The example below uses a quantified predicate to determine which employees have worked more overtime than is allowed for any project:

Information
Example

SELECT 
FIRSTNAME, 
LASTNAME, 
FROM 
EMPLOYEE 
WHERE 
OVERTIME > ANY 
(SELECT 
OVERTIME 
FROM 
PROJECT)


If you knew that employees cannot work more than 100 hours of total overtime on any project, you can recode the statement using that explicit value, as shown below:

Information
Example

SELECT 
FIRSTNAME, 
LASTNAME, 
FROM 
EMPLOYEE 
WHERE 
OVERTIME > 100 

Or, in the case of predicates coded using ANY and SOME, you can reduce the subselect to an atomic value using the MIN column function, as shown below:

Information
Example

SELECT 
FIRSTNAME, 
LASTNAME, 
FROM 
EMPLOYEE 
WHERE 
OVERTIME > 
(SELECT 
MIN(OVERTIME) 
FROM 
PROJECT) 

Warning

Important

If there is an ascending index on OVERTIME, the subselect results in a 1-fetch index scan.

You can apply the same transformation for quantified predicates coded with ALL by using the MAX column function in the subselect. For this transformation, the subselect results in a 1-fetch index scan if there is a descending index on OVERTIME.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC AMI iStrobe Web 24.01