Statement is missing valid JOIN criteria

If a SELECT statement contains missing join criteria, when it is executed, it can produce a Cartesian product between the rows in the referenced tables. This can be problematic because the statement will return a large number of rows without returning the proper results.

The code quality check detects missing join criteria between tables in a statement and suggests join conditions based on existing foreign keys, indexes, and column name/type compatibility.

Example

The following statement is missing a valid JOIN criteria:

SELECT * FROM employee e,customer c, sales_order s WHERE e.employee_id = c.salesperson_id

The code quality check fixes the above statement by adding an AND clause:

SELECT * FROM employee e,customer c, sales_order s WHERE e.employee_id = c.salesperson_id AND s.customer_id = c.customer_id

Note: This code quality check is valid for Oracle, DB2, and Sybase-specific join conditions.

Invalid or missing outer join operator

When an invalid outer join operator exists in a SELECT statement, (or the outer join operator is missing altogether), the statement can return incorrect results.

The code quality check detects invalid or missing join operators in the code and suggests fixes with regards to using the proper join operators.

Example

The following statement is missing an outer join operator:

SELECT * FROM employee e, customer c WHERE e.employee_id = c.salesperson_id (+) AND c.state = ‘CA’

The code quality check fixes the above statement by providing the missing outer join operator to the statement:

SELECT * FROM employee e,customer c WHERE e.employee_id = c.salesperson_id(+) AND c.state(+) = ‘CA’

Transitivity issues

The performance of statements can sometimes be improved by adding join criteria, even if a join is fully defined. If this alternate join criteria is missing in a statement, it can restrict the selection of an index in Oracle’s optimizer and cause performance problems.

The code quality check detects possible join conditions by analyzing the existing conditions in a statement and calculating the missing, alternative join criteria.

Example

The following statement contains a transitivity issue with an index problem:

SELECT * FROM item i, product p, price pr WHERE i.product_id = p.product_id AND p.product_id = pr.product_id

The code quality check fixes the above statement with a transitivity issue by adding the missing join condition:

SELECT * FROM item i, product p, price pr WHERE i.product_id = p.product_id AND p.product_id = pr.product_id AND i.product_id = pr.product_id

Nested query in WHERE clause

It is considered bad format to place sub-queries in the WHERE clause of a statement, and such clauses can typically be corrected by moving the sub-query to the FROM clause instead, which preserves the meaning of the statement while providing more efficient code.

The code quality check fixes the placement of sub-queries in a statement, which can affect performance. It detects the possibility of moving sub-queries from the FROM clause of the statement.

Example

The following statement contains a sub-query that contains an incorrect placement of a WHERE statement:

SELECT * FROM employee WHERE employee_id = (SELECT MAX(salary) FROM employee)

The code quality check fixes the above statement by correcting the sub-query issue:

SELECT employee.* FROM employee (SELECT DISTINCT MAX(salary) col1 FROM employee) t1 WHERE employee_id = t1.col1

Wrong place for conditions in a HAVING clause

When utilizing the HAVING clause in a statement

It is recommended to include as few conditions as possible while utilizing the HAVING clause in a statement. DB Optimizer™  detects all conditions in a given HAVING statement and suggests equivalent expressions that can benefit from existing indexes.

Example

The following statement contains a HAVING clause that is in the wrong place:

SELECT col_a, SUM(col_b) FROM table_a GROUP BY col_a HAVING col_a > 100

The code check fixes the above statement by replacing the HAVING clause with equivalent expressions:

SELECT col_a, SUM(col_b) FROM table_a WHERE col_a > 100 GROUP BY col_a

Index suppressed by a function or an arithmetic operator

In a SELECT statement, if an arithmetic operator is used on an indexed column in the WHERE clause, the operator can suppress the index and result in a FULL TABLE SCAN that can hinder performance.

The code quality check detects these conditions and suggests equivalent expressions that benefit from existing indexes.

Example

The following statement includes an indexed column as part of an arithmetic operator:

SELECT * FROM employee WHERE 1 = employee_id - 5

The code quality check fixes the above statement by reconstructing the WHERE clause:

SELECT * FROM employee WHERE 6 = employee_id

Mismatched or incompatible column types

When the data types of join or parameter declaration columns are mismatched, the optimizer is limited in its ability to consider all indexes. This can cause a query to be less efficient as the system might select the wrong index or perform a table scan, which affects performance.

The code quality check flags mismatched or incompatible column types and warns that it is not valid code.

Example

Consider the following statement if Table A contains the column col int and Table B contains the column col 2 varchar(3):

SELECT * FROM a, b WHERE a.col = b.col;

In the above scenario, the code quality check flags the ‘a.col = b.col’ part of the statement and warns that it is not valid code.

Null column comparison

When comparing a column with NULL, the !=NULL condition may return a result that is different from the intended command, because col=NULL will always return a result of false. Instead, the NULL/IS NOT NULL operators should be used in its place.

The code quality check flags occurrences of the !=NULL condition and replaces them with the IS NULL operator.

Example

The following statement includes an incorrect col = NULL expression:

SELECT * FROM employee WHERE manager_id = NULL

The code quality check replaces the incorrect expression with an IS NULL clause:

SELECT * FROM employee WHERE manager_id IS NULL