Home

Description

The SELECT statement used as argument of EXISTS statements should be selective, meaning that it should only return a few rows. This is important because the EXISTS statement only checks whether any rows are returned, not the actual number of rows. If the SELECT statement returns too many rows, the EXISTS statement may return a false positive, which can lead to unexpected results. Therefore, it is important to ensure that the SELECT statement is selective and only returns a few rows.

Key Benefits

• Selective: SELECT statements used as argument of EXISTS statements should be selective to ensure the query optimizer can accurately assess the cost of the query and determine the most efficient query plan. :
• Performance: Selective SELECT statements used as argument of EXISTS statements can improve query performance by reducing the amount of data that needs to be processed. :
• Accuracy: Selective SELECT statements used as argument of EXISTS statements can help ensure accuracy of the query results by reducing the amount of data that needs to be processed. :

Non-compliant Code Example

SELECT 
    NAME,
    ADDRESS,
    CREDIT_LIMIT       
FROM
    CUSTOMERS
WHERE EXISTS (SELECT NAME FROM VENDOR);             --Non compliant code (SELECT statements used as argument of EXISTS statements is not selective)

Compliant Code Example

SELECT 
    NAME,
    ADDRESS,
    CREDIT_LIMIT       
FROM
    CUSTOMERS
WHERE EXISTS (SELECT NAME FROM VENDOR WHERE NAME IS NOT NULL);  --Compliant code (SELECT statements used as argument of EXISTS statements is selective)