SQL Anti Patterns

This section describes the types of Anti-Patterns detected in FlowHigh and possible solutions for the same.

AP 01 - Avoid ANSI-89 join syntax

AP 02 - Beware of count distinct

AP 03 - Avoid count(*) in the outer join

AP 04 - Avoid nesting scalar subqueries in the SELECT statement

AP 05 - Beware of SELECT *

AP 06 - Beware of SELECT DISTINCT

AP 07 - Avoid using functions in the WHERE clause

AP 08 - Beware of NULL with arithmetic and string operations

AP 09 - Avoid filtering attributes from the non-preserved side of an outer join

AP 10 - Beware of filtering for NULL

AP 11 - Avoid implicit column references

AP 12 - Beware of implicit self-joins in a correlated subquery

AP 13 - Avoid inner join after outer join in multi-join query

AP 14 - Avoid implicit cross join

AP 15 - Use an alias for derived columns

AP 16 - Beware of NULL in combination with not equal operator (!=, <>)

AP 17 - Use IN / NOT IN for multiple OR operators

AP 18 - Avoid ordinal numbers when using ORDER BY or GROUP BY

AP 19 - Split multi join queries into smaller chunks

AP 20 - Avoid unused Common Table Expressions (CTE)

AP 21 - Use window functions instead of self joins

AP 22 - Avoid UNION for multiple filter values

AP 23 - Avoid the natural join clause

AP 24 - Use LIKE instead of REGEX where LIKE is possible

AP 25 - Use UNION ALL instead of UNION

AP 26 - Avoid using WHERE to filter aggregate columns

AP 27 - Use an alias for inline views

AP 28 - Avoid using functions in the Join clause

AP 29 - Use parentheses when mixing ANDs with ORs

AP 30 - Avoid or delay ORDER BY in inline views

AP 33 - WHERE NOT IN without NOT NULL check in subquery

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

AP 36 - JOIN condition linking the compound key to multiple, different tables.

AP 38 - Avoid nested CTEs