Convention Rules

This section contains 12 rule(s) related to convention.

Rule Summary

Code	Name	Aliases	Fix Compatible
CV01	convention.not_equal	L061	✅
CV02	convention.coalesce	L060	✅
CV03	convention.select_trailing_comma	L038	✅
CV04	convention.count_rows	L047	✅
CV05	convention.is_null	L049	✅
CV06	convention.terminator	L052	✅
CV07	convention.statement_brackets	L053	✅
CV08	convention.left_join	L055	❌
CV09	convention.blocked_words	L062	❌
CV10	convention.quoted_literals	L064	✅
CV11	convention.casting_style	L067	✅
CV12	convention.join_condition	-	✅

---

CV01: convention.not_equal

Consistent usage of != or <> for "not equal to" operator.

Property	Value
Name	convention.not_equal
Aliases	L061
Groups	all, convention
Auto-fixable	Yes ✅

Anti-pattern

SELECT * FROM X WHERE 1 <> 2 AND 3 != 4;

Best practice

Ensure all "not equal to" comparisons are consistent, not mixing != and <>.

SELECT * FROM X WHERE 1 != 2 AND 3 != 4;

Configuration

Option	Description
preferred_not_equal_style	The style for using not equal to operator. Defaults to consistent. Must be one of ['consistent', 'c_style', 'ansi'].

---

CV02: convention.coalesce

Use COALESCE instead of IFNULL or NVL.

Property	Value
Name	convention.coalesce
Aliases	L060
Groups	all, convention
Auto-fixable	Yes ✅

Anti-pattern

IFNULL or NVL are used to fill NULL values.

SELECT ifnull(foo, 0) AS bar,
FROM baz;

SELECT nvl(foo, 0) AS bar,
FROM baz;

Best practice

Use COALESCE instead. COALESCE is universally supported, whereas Redshift doesn't support IFNULL and BigQuery doesn't support NVL. Additionally, COALESCE is more flexible and accepts an arbitrary number of arguments.

SELECT coalesce(foo, 0) AS bar,
FROM baz;

---

CV03: convention.select_trailing_comma

Trailing commas within select clause.

Property	Value
Name	convention.select_trailing_comma
Aliases	L038
Groups	all, core, convention
Auto-fixable	Yes ✅

NOTE

For many database backends this is allowed. For some users this may be something they wish to enforce (in line with Python best practice). Many database backends regard this as a syntax error, and as such the SQLFluff default is to forbid trailing commas in the select clause.

Anti-pattern

SELECT
    a,
    b,
FROM foo

Best practice

SELECT
    a,
    b
FROM foo

Configuration

Option	Description
select_clause_trailing_comma	Should trailing commas within select clauses be required or forbidden? Must be one of ['forbid', 'require'].

---

CV04: convention.count_rows

Use consistent syntax to express "count number of rows".

Property	Value
Name	convention.count_rows
Aliases	L047
Groups	all, core, convention
Auto-fixable	Yes ✅

Note: If both prefer_count_1 and prefer_count_0 are set to true then prefer_count_1 has precedence.

COUNT(*), COUNT(1), and even COUNT(0) are equivalent syntaxes in many SQL engines due to optimizers interpreting these instructions as "count number of rows in result".

The ANSI-92 spec mentions the COUNT(*) syntax specifically as having a special meaning:

If COUNT(*) is specified, then
the result is the cardinality of T.

So by default, SQLFluff enforces the consistent use of COUNT(*).

If the SQL engine you work with, or your team, prefers COUNT(1) or COUNT(0) over COUNT(*), you can configure this rule to consistently enforce your preference.

Anti-pattern

select
    count(1)
from table_a

Best practice

Use count(*) unless specified otherwise by config prefer_count_1, or prefer_count_0 as preferred.

select
    count(*)
from table_a

Configuration

Option	Description
prefer_count_0	Should count(0) be preferred over count(*) and count(1)? Must be one of [True, False].
prefer_count_1	Should count(1) be preferred over count(*) and count(0)? Must be one of [True, False].

---

CV05: convention.is_null

Comparisons with NULL should use "IS" or "IS NOT".

Property	Value
Name	convention.is_null
Aliases	L049
Groups	all, core, convention
Auto-fixable	Yes ✅

Anti-pattern

In this example, the = operator is used to check for NULL values.

SELECT
    a
FROM foo
WHERE a = NULL

Best practice

Use IS or IS NOT to check for NULL values.

SELECT
    a
FROM foo
WHERE a IS NULL

---

CV06: convention.terminator

Statements must end with a semi-colon.

Property	Value
Name	convention.terminator
Aliases	L052
Groups	all, convention
Auto-fixable	Yes ✅

Anti-pattern

A statement is not immediately terminated with a semi-colon. The ◦ represents space.

SELECT
    a
FROM foo

;

SELECT
    b
FROM bar◦◦;

Best practice

Immediately terminate the statement with a semi-colon.

SELECT
    a
FROM foo;

Configuration

Option	Description
multiline_newline	Should semi-colons be placed on a new line after multi-line statements? Must be one of [True, False].
require_final_semicolon	Should final semi-colons be required? (N.B. forcing trailing semi-colons is not recommended for dbt users as it can cause issues when wrapping the query within other SQL queries). Must be one of [True, False].

---

CV07: convention.statement_brackets

Top-level statements should not be wrapped in brackets.

Property	Value
Name	convention.statement_brackets
Aliases	L053
Groups	all, convention
Auto-fixable	Yes ✅

Anti-pattern

A top-level statement is wrapped in brackets.

(SELECT
    foo
FROM bar)

-- This also applies to statements containing a sub-query.

(SELECT
    foo
FROM (SELECT * FROM bar))

Best practice

Don't wrap top-level statements in brackets.

SELECT
    foo
FROM bar

-- Likewise for statements containing a sub-query.

SELECT
    foo
FROM (SELECT * FROM bar)

---

CV08: convention.left_join

Use LEFT JOIN instead of RIGHT JOIN.

Property	Value
Name	convention.left_join
Aliases	L055
Groups	all, convention
Auto-fixable	No ❌

Anti-pattern

RIGHT JOIN is used.

SELECT
    foo.col1,
    bar.col2
FROM foo
RIGHT JOIN bar
    ON foo.bar_id = bar.id;

Best practice

Refactor and use LEFT JOIN instead.

SELECT
    foo.col1,
    bar.col2
FROM bar
LEFT JOIN foo
    ON foo.bar_id = bar.id;

---

CV09: convention.blocked_words

Block a list of configurable words from being used.

Property	Value
Name	convention.blocked_words
Aliases	L062
Groups	all, convention
Auto-fixable	No ❌

This generic rule can be useful to prevent certain keywords, functions, or objects from being used. Only whole words can be blocked, not phrases, nor parts of words.

This block list is case insensitive.

Example use cases

• We prefer BOOL over BOOLEAN and there is no existing rule to enforce this. Until such a rule is written, we can add BOOLEAN to the deny list to cause a linting error to flag this.
• We have deprecated a schema/table/function and want to prevent it being used in future. We can add that to the denylist and then add a -- noqa: CV09 for the few exceptions that still need to be in the code base for now.

Anti-pattern

If the blocked_words config is set to deprecated_table,bool then the following will flag:

SELECT * FROM deprecated_table WHERE 1 = 1;
CREATE TABLE myschema.t1 (a BOOL);

Best practice

Do not used any blocked words:

SELECT * FROM another_table WHERE 1 = 1;
CREATE TABLE myschema.t1 (a BOOLEAN);

Configuration

Option	Description
blocked_regex	Optional, regex of blocked pattern which should not be used in statements.
blocked_words	Optional, comma-separated list of blocked words which should not be used in statements.
match_source	Optional, also match regex of blocked pattern before applying templating.

---

CV10: convention.quoted_literals

Consistent usage of preferred quotes for quoted literals.

Property	Value
Name	convention.quoted_literals
Aliases	L064
Groups	all, convention
Auto-fixable	Yes ✅

Some databases allow quoted literals to use either single or double quotes. Prefer one type of quotes as specified in rule setting, falling back to alternate quotes to reduce the need for escapes.

Dollar-quoted raw strings are excluded from this rule, as they are mostly used for literal UDF Body definitions.

NOTE

This rule only checks quoted literals and not quoted identifiers as they often cannot interchange single and double quotes

This rule is only enabled for dialects that allow single and double quotes for quoted literals (currently bigquery, databricks, hive, mysql, sparksql). It can be enabled for other dialects with the force_enable = True flag.

Anti-pattern

select
    "abc",
    'abc',
    "\"",
    "abc" = 'abc'
from foo

Best practice

Ensure all quoted literals use preferred quotes, unless escaping can be reduced by using alternate quotes.

select
    "abc",
    "abc",
    '"',
    "abc" = "abc"
from foo

Configuration

Option	Description
force_enable	Run this rule even for dialects where this rule is disabled by default. Must be one of [True, False].
preferred_quoted_literal_style	Preferred quoting style to use for the quoted literals. If set to consistent quoting style is derived from the first quoted literal in the file. Must be one of ['consistent', 'single_quotes', 'double_quotes'].

---

CV11: convention.casting_style

Enforce consistent type casting style.

Property	Value
Name	convention.casting_style
Aliases	L067
Groups	all, convention
Auto-fixable	Yes ✅

NOTE

This is only compatible with 2-arguments CONVERT as some dialects allow an optional 3rd argument e.g TSQL, which cannot be rewritten into CAST. This rule is disabled by default for Teradata because it supports different type casting apart from CONVERT and :: e.g DATE '2007-01-01', '9999-12-31' (DATE).

Anti-pattern

Using mixture of CONVERT, :: and CAST when preferred_type_casting_style config is set to consistent (default).

SELECT
    CONVERT(int, 1) AS bar,
    100::int::text,
    CAST(10 AS text) AS coo
FROM foo;

Best practice

Use consistent type casting style.

SELECT
    CAST(1 AS int) AS bar,
    CAST(CAST(100 AS int) AS text),
    CAST(10 AS text) AS coo
FROM foo;

Configuration

Option	Description
preferred_type_casting_style	The expectation for using sql type casting. Must be one of ['consistent', 'shorthand', 'convert', 'cast'].

---

CV12: convention.join_condition

Use JOIN ... ON ... instead of WHERE ... for join conditions.

Property	Value
Name	convention.join_condition
Groups	all, convention
Auto-fixable	Yes ✅

Anti-pattern

Using WHERE clause for join conditions.

SELECT
    foo.a
    , bar.b
FROM foo
JOIN bar
WHERE foo.x = bar.y;

Best practice

Use JOIN ON clause for join condition.

SELECT
    foo.a
    , bar.b
FROM foo
JOIN bar
ON foo.x = bar.y;

---