AP 34 - Pseudo join condition results in a CROSS join.

Severity

Type

Problem

A join condition exists but it does not link the joining table resulting in a cross join.

Example

SELECT *
  FROM TEST PCT
  JOIN EXAM PCE
    ON 1=1
   AND PCE.AUDIT_CD<>'D'

Some conditions have been specified following the ON clause. However, none of them are actually joining: the first condition uses a constant (1=1). The second join condition does not reference the joining table PCE.

ON 1=1 
AND PCT.CRUD_CD<>'D'

This results in an implicit cross join and performance issues.

Solution

Specify a join condition that links the joining table to another table defined earlier in the scope.

SELECT *
  FROM TEST PCT
  JOIN EXAM PCE
    ON PCT.ID=PCE.ID
   AND PCE.AUDIT_CD<>'D'

Legitimate use of the anti pattern

None. Once a table is explicitly joined (JOIN with ON keyword used) at least one of the condition expressions should bind the table being joined with at least one table specified before.