Home

Description

The rule "SQL EXISTS subqueries should not be used" states that the SQL EXISTS subquery should not be used in a query. EXISTS subqueries are used to check if a certain condition is met in a table. They are usually used to check if a certain record exists in a table. However, using EXISTS subqueries can lead to performance issues and should be avoided. Instead, other methods such as joins should be used to check if a certain record exists in a table.

Key Benefits

• Speed: SQL EXISTS subqueries are typically slower than other methods, as they require the database to evaluate the subquery for each row of the outer query.
• Complexity: SQL EXISTS subqueries can be difficult to read and understand, making them difficult to maintain.
• Duplication: SQL EXISTS subqueries can lead to duplication of data, which can lead to errors and inconsistencies.

Non-compliant Code Example

SELECT 
    NAME,
    ADDRESS,
    CREDIT_LIMIT       
FROM
    CUSTOMERS
WHERE EXISTS (SELECT NAME FROM VENDOR WHERE VENDOR.NAME = CUSTOMERS.NAME) --Non compliant code (SQL EXISTS sub-queries is used)

Compliant Code Example

SELECT 
    NAME,
    ADDRESS,
    CREDIT_LIMIT       
FROM
    CUSTOMERS
INNER JOIN VENDOR 
	ON VENDOR.NAME IS NOT NULL AND VENDOR.NAME = CUSTOMERS.NAME;  --Compliant code (SQL EXISTS sub-queries is not used)