Rules Reference

Rules in SQLFluff are implemented as crawlers. These are entities which work their way through the parsed structure of a query to evaluate a particular rule or set of rules. The intent is that the definition of each specific rule should be really streamlined and only contain the logic for the rule itself, with all the other mechanics abstracted away. To understand how rules are enabled and disabled see Enabling and Disabling Rules.

Core Rules

Certain rules belong to the core rule group. In order for a rule to be designated as core, it must meet the following criteria:

  • Stable

  • Applies to most dialects

  • Could detect a syntax issue

  • Isn’t too opinionated toward one style (e.g. the dbt style guide)

Core rules can also make it easier to roll out SQLFluff to a team by only needing to follow a ‘common sense’ subset of rules initially, rather than spending time understanding and configuring all the rules, some of which your team may not necessarily agree with.

We believe teams will eventually want to enforce more than just the core rules, and we encourage everyone to explore all the rules and customize a rule set that best suites their organization.

See the Configuration section for more information on how to enable only core rules by default.

Inline Ignoring Errors

SQLFluff features inline error ignoring. For example, the following will ignore the lack of whitespace surrounding the * operator.

a.a*a.b AS bad_1  -- noqa: LT01

Multiple rules can be ignored by placing them in a comma-delimited list.

a.a *  a.b AS bad_2,  -- noqa: LT01, LT03

It is also possible to ignore non-rule based errors, and instead opt to ignore templating (TMP) & parsing (PRS) errors.

WHERE
  col1 = 2 AND
  dt >= DATE_ADD(CURRENT_DATE(), INTERVAL -2 DAY) -- noqa: PRS

Note

It should be noted that ignoring TMP and PRS errors can lead to incorrect sqlfluff lint and sqfluff fix results as SQLFluff can misinterpret the SQL being analysed.

Should the need arise, not specifying specific rules to ignore will ignore all rules on the given line.

a.a*a.b AS bad_3  -- noqa

Ignoring line ranges

Similar to pylint’s “pylint” directive”, ranges of lines can be ignored by adding -- noqa:disable=<rule>[,...] | all to the line. Following this directive, specified rules (or all rules, if “all” was specified) will be ignored until a corresponding – noqa:enable=<rule>[,…] | all directive.

-- Ignore rule AL02 from this line forward
SELECT col_a a FROM foo -- noqa: disable=AL02

-- Ignore all rules from this line forward
SELECT col_a a FROM foo -- noqa: disable=all

-- Enforce all rules from this line forward
SELECT col_a a FROM foo -- noqa: enable=all

Rule Index

Bundle

Rule Name

Code

Aliases

Aliasing bundle

aliasing.table

AL01

L011

aliasing.column

AL02

L012

aliasing.expression

AL03

L013

aliasing.unique.table

AL04

L020

aliasing.unused

AL05

L025

aliasing.length

AL06

L066

aliasing.forbid

AL07

L031

aliasing.unique.column

AL08

aliasing.self_alias.column

AL09

Ambiguous bundle

ambiguous.distinct

AM01

L021

ambiguous.union

AM02

L033

ambiguous.order_by

AM03

L037

ambiguous.column_count

AM04

L044

ambiguous.join

AM05

L051

ambiguous.column_references

AM06

L054

ambiguous.set_columns

AM07

L068

Capitalisation bundle

capitalisation.keywords

CP01

L010

capitalisation.identifiers

CP02

L014

capitalisation.functions

CP03

L030

capitalisation.literals

CP04

L040

capitalisation.types

CP05

L063

Convention bundle

convention.not_equal

CV01

L061

convention.coalesce

CV02

L060

convention.select_trailing_comma

CV03

L038

convention.count_rows

CV04

L047

convention.is_null

CV05

L049

convention.terminator

CV06

L052

convention.statement_brackets

CV07

L053

convention.left_join

CV08

L055

convention.blocked_words

CV09

L062

convention.quoted_literals

CV10

L064

convention.casting_style

CV11

L067

Jinja bundle

jinja.padding

JJ01

L046

Layout bundle

layout.spacing

LT01

L001, L005, L006, L008, L023, L024, L039, L048, L071

layout.indent

LT02

L002, L003, L004

layout.operators

LT03

L007

layout.commas

LT04

L019

layout.long_lines

LT05

L016

layout.functions

LT06

L017

layout.cte_bracket

LT07

L018

layout.cte_newline

LT08

L022

layout.select_targets

LT09

L036

layout.select_modifiers

LT10

L041

layout.set_operators

LT11

L065

layout.end_of_file

LT12

L009, layout.end-of-file

layout.start_of_file

LT13

L050

References bundle

references.from

RF01

L026

references.qualification

RF02

L027

references.consistent

RF03

L028

references.keywords

RF04

L029

references.special_chars

RF05

L057

references.quoting

RF06

L059

Structure bundle

structure.else_null

ST01

L035

structure.simple_case

ST02

L043

structure.unused_cte

ST03

L045

structure.nested_case

ST04

L058

structure.subquery

ST05

L042

structure.column_order

ST06

L034

structure.using

ST07

L032

structure.distinct

ST08

L015

structure.join_condition_order

ST09

TSQL bundle

tsql.sp_prefix

TQ01

L056

Aliasing bundle

Code AL01
Rule aliasing.table

Implicit/explicit aliasing of table.

Aliasing of table to follow preference (requiring an explicit AS is the default).

This rule is sqlfluff fix compatible.

Name: aliasing.table

Aliases: L011

Groups: all, aliasing

Configuration

  • aliasing: Should alias have an explicit AS or is implicit aliasing required? Must be one of ['implicit', 'explicit'].

Anti-pattern

In this example, the alias voo is implicit.

SELECT
    voo.a
FROM foo voo

Best practice

Add AS to make it explicit.

SELECT
    voo.a
FROM foo AS voo
Code AL02
Rule aliasing.column

Implicit/explicit aliasing of columns.

Aliasing of columns to follow preference (explicit using an AS clause is default).

Name: aliasing.column

Aliases: L012

Groups: all, core, aliasing

Configuration

  • aliasing: Should alias have an explicit AS or is implicit aliasing required? Must be one of ['implicit', 'explicit'].

Anti-pattern

In this example, the alias for column a is implicit.

SELECT
    a alias_col
FROM foo

Best practice

Add AS to make it explicit.

SELECT
    a AS alias_col
FROM foo
Code AL03
Rule aliasing.expression

Column expression without alias. Use explicit AS clause.

Name: aliasing.expression

Aliases: L013

Groups: all, core, aliasing

Configuration

  • allow_scalar: Whether or not to allow a single element in the select clause to be without an alias. Must be one of [True, False].

Anti-pattern

In this example, there is no alias for both sums.

SELECT
    sum(a),
    sum(b)
FROM foo

Best practice

Add aliases.

SELECT
    sum(a) AS a_sum,
    sum(b) AS b_sum
FROM foo
Code AL04
Rule aliasing.unique.table

Table aliases should be unique within each clause.

Reusing table aliases is very likely a coding error.

Name: aliasing.unique.table

Aliases: L020

Groups: all, core, aliasing, aliasing.unique

Anti-pattern

In this example, the alias t is reused for two different tables:

SELECT
    t.a,
    t.b
FROM foo AS t, bar AS t

-- This can also happen when using schemas where the
-- implicit alias is the table name:

SELECT
    a,
    b
FROM
    2020.foo,
    2021.foo

Best practice

Make all tables have a unique alias.

SELECT
    f.a,
    b.b
FROM foo AS f, bar AS b

-- Also use explicit aliases when referencing two tables
-- with the same name from two different schemas.

SELECT
    f1.a,
    f2.b
FROM
    2020.foo AS f1,
    2021.foo AS f2
Code AL05
Rule aliasing.unused

Tables should not be aliased if that alias is not used.

This rule is sqlfluff fix compatible.

Name: aliasing.unused

Aliases: L025

Groups: all, core, aliasing

Anti-pattern

SELECT
    a
FROM foo AS zoo

Best practice

Use the alias or remove it. An unused alias makes code harder to read without changing any functionality.

SELECT
    zoo.a
FROM foo AS zoo

-- Alternatively...

SELECT
    a
FROM foo
Code AL06
Rule aliasing.length

Enforce table alias lengths in from clauses and join conditions.

Name: aliasing.length

Aliases: L066

Groups: all, core, aliasing

Configuration

  • max_alias_length: The maximum length of an alias to allow without raising a violation. Must be one of range(0, 1000).

  • min_alias_length: The minimum length of an alias to allow without raising a violation. Must be one of range(0, 1000).

Anti-pattern

In this example, alias o is used for the orders table.

SELECT
    SUM(o.amount) as order_amount,
FROM orders as o

Best practice

Avoid aliases. Avoid short aliases when aliases are necessary.

See also: Rule_AL07.

SELECT
    SUM(orders.amount) as order_amount,
FROM orders

SELECT
    replacement_orders.amount,
    previous_orders.amount
FROM
    orders AS replacement_orders
JOIN
    orders AS previous_orders
    ON replacement_orders.id = previous_orders.replacement_id
Code AL07
Rule aliasing.forbid

Avoid table aliases in from clauses and join conditions.

This rule is sqlfluff fix compatible.

Name: aliasing.forbid

Aliases: L031

Groups: all, aliasing

Configuration

  • force_enable: Run this rule even for dialects where this rule is disabled by default. Must be one of [True, False].

Note

This rule was taken from the dbt Style Guide which notes that:

Avoid table aliases in join conditions (especially initialisms) - it’s harder to understand what the table called “c” is compared to “customers”.

This rule is controversial and for many larger databases avoiding alias is neither realistic nor desirable. In particular for BigQuery due to the complexity of backtick requirements and determining whether a name refers to a project or dataset so automated fixes can potentially break working SQL code. For most users Rule_AL06 is likely a more appropriate linting rule to drive a sensible behaviour around aliasing.

The stricter treatment of aliases in this rule may be useful for more focused projects, or temporarily as a refactoring tool because the fix routine of the rule can remove aliases.

This rule is disabled by default for all dialects it can be enabled with the force_enable = True flag.

Anti-pattern

In this example, alias o is used for the orders table, and c is used for customers table.

SELECT
    COUNT(o.customer_id) as order_amount,
    c.name
FROM orders as o
JOIN customers as c on o.id = c.user_id

Best practice

Avoid aliases.

SELECT
    COUNT(orders.customer_id) as order_amount,
    customers.name
FROM orders
JOIN customers on orders.id = customers.user_id

-- Self-join will not raise issue

SELECT
    table1.a,
    table_alias.b,
FROM
    table1
    LEFT JOIN table1 AS table_alias ON
        table1.foreign_key = table_alias.foreign_key
Code AL08
Rule aliasing.unique.column

Column aliases should be unique within each clause.

Reusing column aliases is very likely a coding error. Note that while in many dialects, quoting an identifier makes it case-sensitive this rule always compares in a case-insensitive way. This is because columns with the same name, but different case, are still confusing and potentially ambiguous to other readers.

In situations where it is necessary to have columns with the same name (whether they differ in case or not) we recommend disabling this rule for either just the line, or the whole file.

Name: aliasing.unique.column

Groups: all, core, aliasing, aliasing.unique

Anti-pattern

In this example, the alias foo is reused for two different columns:

SELECT
    a as foo,
    b as foo
FROM tbl;

-- This can also happen when referencing the same column
-- column twice, or aliasing an expression to the same
-- name as a column:

SELECT
    foo,
    foo,
    a as foo
FROM tbl;

Best practice

Make all columns have a unique alias.

SELECT
    a as foo,
    b as bar
FROM tbl;

-- Avoid also using the same column twice unless aliased:

SELECT
    foo as foo1,
    foo as foo2,
    a as foo3
FROM tbl;
Code AL09
Rule aliasing.self_alias.column

Column aliases should not alias to itself, i.e. self-alias.

Renaming the column to itself is a redundant piece of SQL, which doesn’t affect its functionality.

Note that this rule does allow self-alias to change case sensitivity.

This rule is sqlfluff fix compatible.

Name: aliasing.self_alias.column

Groups: all, core, aliasing

Anti-pattern

Aliasing the column to itself.

SELECT
    col AS col
FROM table;

Best practice

Not to use alias to rename the column to its original name. Self-aliasing leads to redundant code without changing any functionality.

SELECT
    col
FROM table;

Ambiguous bundle

Code AM01
Rule ambiguous.distinct

Ambiguous use of DISTINCT in a SELECT statement with GROUP BY.

When using GROUP BY a DISTINCT` clause should not be necessary as every non-distinct SELECT clause must be included in the GROUP BY clause.

Name: ambiguous.distinct

Aliases: L021

Groups: all, core, ambiguous

Anti-pattern

DISTINCT and GROUP BY are conflicting.

SELECT DISTINCT
    a
FROM foo
GROUP BY a

Best practice

Remove DISTINCT or GROUP BY. In our case, removing GROUP BY is better.

SELECT DISTINCT
    a
FROM foo
Code AM02
Rule ambiguous.union

UNION [DISTINCT|ALL] is preferred over just UNION.

This rule is sqlfluff fix compatible.

Name: ambiguous.union

Aliases: L033

Groups: all, core, ambiguous

Note

This rule is only enabled for dialects that support UNION and UNION DISTINCT (ansi, hive, mysql, and redshift).

Anti-pattern

In this example, UNION DISTINCT should be preferred over UNION, because explicit is better than implicit.

SELECT a, b FROM table_1
UNION
SELECT a, b FROM table_2

Best practice

Specify DISTINCT or ALL after UNION (note that DISTINCT is the default behavior).

SELECT a, b FROM table_1
UNION DISTINCT
SELECT a, b FROM table_2
Code AM03
Rule ambiguous.order_by

Ambiguous ordering directions for columns in order by clause.

This rule is sqlfluff fix compatible.

Name: ambiguous.order_by

Aliases: L037

Groups: all, ambiguous

Anti-pattern

SELECT
    a, b
FROM foo
ORDER BY a, b DESC

Best practice

If any columns in the ORDER BY clause specify ASC or DESC, they should all do so.

SELECT
    a, b
FROM foo
ORDER BY a ASC, b DESC
Code AM04
Rule ambiguous.column_count

Query produces an unknown number of result columns.

Name: ambiguous.column_count

Aliases: L044

Groups: all, ambiguous

Anti-pattern

Querying all columns using * produces a query result where the number or ordering of columns changes if the upstream table’s schema changes. This should generally be avoided because it can cause slow performance, cause important schema changes to go undetected, or break production code. For example:

  • If a query does SELECT t.* and is expected to return columns a, b, and c, the actual columns returned will be wrong/different if columns are added to or deleted from the input table.

  • UNION and DIFFERENCE clauses require the inputs have the same number of columns (and compatible types).

  • JOIN queries may break due to new column name conflicts, e.g. the query references a column c which initially existed in only one input table but a column of the same name is added to another table.

  • CREATE TABLE (<<column schema>>) AS SELECT *

WITH cte AS (
    SELECT * FROM foo
)

SELECT * FROM cte
UNION
SELECT a, b FROM t

Best practice

Somewhere along the “path” to the source data, specify columns explicitly.

WITH cte AS (
    SELECT * FROM foo
)

SELECT a, b FROM cte
UNION
SELECT a, b FROM t
Code AM05
Rule ambiguous.join

Join clauses should be fully qualified.

By default this rule is configured to enforce fully qualified INNER JOIN clauses, but not [LEFT/RIGHT/FULL] OUTER JOIN. If you prefer a stricter lint then this is configurable.

This rule is sqlfluff fix compatible.

Name: ambiguous.join

Aliases: L051

Groups: all, ambiguous

Configuration

  • fully_qualify_join_types: Which types of JOIN clauses should be fully qualified? Must be one of ['inner', 'outer', 'both'].

Anti-pattern

A join is used without specifying the kind of join.

 SELECT
     foo
 FROM bar
 JOIN baz;

Best practice

Use INNER JOIN rather than JOIN.

 SELECT
     foo
 FROM bar
 INNER JOIN baz;
Code AM06
Rule ambiguous.column_references

Inconsistent column references in GROUP BY/ORDER BY clauses.

Name: ambiguous.column_references

Aliases: L054

Groups: all, core, ambiguous

Configuration

  • group_by_and_order_by_style: The expectation for using explicit column name references or implicit positional references. Must be one of ['consistent', 'implicit', 'explicit'].

Note

ORDER BY clauses from WINDOW clauses are ignored by this rule.

Anti-pattern

A mix of implicit and explicit column references are used in a GROUP BY clause.

 SELECT
     foo,
     bar,
     sum(baz) AS sum_value
 FROM fake_table
 GROUP BY
     foo, 2;

 -- The same also applies to column
 -- references in ORDER BY clauses.

 SELECT
     foo,
     bar
 FROM fake_table
 ORDER BY
     1, bar;

Best practice

Reference all GROUP BY/ORDER BY columns either by name or by position.

 -- GROUP BY: Explicit
 SELECT
     foo,
     bar,
     sum(baz) AS sum_value
 FROM fake_table
 GROUP BY
     foo, bar;

 -- ORDER BY: Explicit
 SELECT
     foo,
     bar
 FROM fake_table
 ORDER BY
     foo, bar;

 -- GROUP BY: Implicit
 SELECT
     foo,
     bar,
     sum(baz) AS sum_value
 FROM fake_table
 GROUP BY
     1, 2;

 -- ORDER BY: Implicit
 SELECT
     foo,
     bar
 FROM fake_table
 ORDER BY
     1, 2;
Code AM07
Rule ambiguous.set_columns

Queries within set query produce different numbers of columns.

Name: ambiguous.set_columns

Aliases: L068

Groups: all, ambiguous

Anti-pattern

When writing set expressions, all queries must return the same number of columns.

WITH cte AS (
    SELECT
        a,
        b
    FROM foo
)
SELECT * FROM cte
UNION
SELECT
    c,
    d,
    e
 FROM t

Best practice

Always specify columns when writing set queries and ensure that they all seleect same number of columns

WITH cte AS (
    SELECT a, b FROM foo
)
SELECT
    a,
    b
FROM cte
UNION
SELECT
    c,
    d
FROM t

Capitalisation bundle

Code CP01
Rule capitalisation.keywords

Inconsistent capitalisation of keywords.

This rule is sqlfluff fix compatible.

Name: capitalisation.keywords

Aliases: L010

Groups: all, core, capitalisation

Configuration

  • capitalisation_policy: The capitalisation policy to enforce. Must be one of ['consistent', 'upper', 'lower', 'capitalise'].

  • ignore_words: Comma separated list of words to ignore from rule.

  • ignore_words_regex: Words to ignore from rule if they are a partial match for the regular expression. To ignore only full matches you can use ^ (beginning of text) and $ (end of text). Due to regular expression operator precedence, it is good practice to use parentheses around everything between ^ and $.

Anti-pattern

In this example, select is in lower-case whereas FROM is in upper-case.

select
    a
FROM foo

Best practice

Make all keywords either in upper-case or in lower-case.

SELECT
    a
FROM foo

-- Also good

select
    a
from foo
Code CP02
Rule capitalisation.identifiers

Inconsistent capitalisation of unquoted identifiers.

This rule is sqlfluff fix compatible.

Name: capitalisation.identifiers

Aliases: L014

Groups: all, core, capitalisation

Configuration

  • extended_capitalisation_policy: The capitalisation policy to enforce, extended with PascalCase. This is separate from capitalisation_policy as it should not be applied to keywords. Must be one of ['consistent', 'upper', 'lower', 'pascal', 'capitalise'].

  • ignore_words: Comma separated list of words to ignore from rule.

  • ignore_words_regex: Words to ignore from rule if they are a partial match for the regular expression. To ignore only full matches you can use ^ (beginning of text) and $ (end of text). Due to regular expression operator precedence, it is good practice to use parentheses around everything between ^ and $.

  • unquoted_identifiers_policy: Types of unquoted identifiers to flag violations for. Must be one of ['all', 'aliases', 'column_aliases'].

Anti-pattern

In this example, unquoted identifier a is in lower-case but B is in upper-case.

select
    a,
    B
from foo

Best practice

Ensure all unquoted identifiers are either in upper-case or in lower-case.

select
    a,
    b
from foo

-- Also good

select
    A,
    B
from foo
Code CP03
Rule capitalisation.functions

Inconsistent capitalisation of function names.

This rule is sqlfluff fix compatible.

Name: capitalisation.functions

Aliases: L030

Groups: all, core, capitalisation

Configuration

  • extended_capitalisation_policy: The capitalisation policy to enforce, extended with PascalCase. This is separate from capitalisation_policy as it should not be applied to keywords. Must be one of ['consistent', 'upper', 'lower', 'pascal', 'capitalise'].

  • ignore_words: Comma separated list of words to ignore from rule.

  • ignore_words_regex: Words to ignore from rule if they are a partial match for the regular expression. To ignore only full matches you can use ^ (beginning of text) and $ (end of text). Due to regular expression operator precedence, it is good practice to use parentheses around everything between ^ and $.

Anti-pattern

In this example, the two SUM functions don’t have the same capitalisation.

SELECT
    sum(a) AS aa,
    SUM(b) AS bb
FROM foo

Best practice

Make the case consistent.

SELECT
    sum(a) AS aa,
    sum(b) AS bb
FROM foo
Code CP04
Rule capitalisation.literals

Inconsistent capitalisation of boolean/null literal.

This rule is sqlfluff fix compatible.

Name: capitalisation.literals

Aliases: L040

Groups: all, core, capitalisation

Anti-pattern

In this example, null and false are in lower-case whereas TRUE is in upper-case.

select
    a,
    null,
    TRUE,
    false
from foo

Best practice

Ensure all literal null/true/false literals are consistently upper or lower case

select
    a,
    NULL,
    TRUE,
    FALSE
from foo

-- Also good

select
    a,
    null,
    true,
    false
from foo
Code CP05
Rule capitalisation.types

Inconsistent capitalisation of datatypes.

This rule is sqlfluff fix compatible.

Name: capitalisation.types

Aliases: L063

Groups: all, core, capitalisation

Configuration

  • extended_capitalisation_policy: The capitalisation policy to enforce, extended with PascalCase. This is separate from capitalisation_policy as it should not be applied to keywords. Must be one of ['consistent', 'upper', 'lower', 'pascal', 'capitalise'].

  • ignore_words: Comma separated list of words to ignore from rule.

  • ignore_words_regex: Words to ignore from rule if they are a partial match for the regular expression. To ignore only full matches you can use ^ (beginning of text) and $ (end of text). Due to regular expression operator precedence, it is good practice to use parentheses around everything between ^ and $.

Anti-pattern

In this example, int and unsigned are in lower-case whereas VARCHAR is in upper-case.

CREATE TABLE t (
    a int unsigned,
    b VARCHAR(15)
);

Best practice

Ensure all datatypes are consistently upper or lower case

CREATE TABLE t (
    a INT UNSIGNED,
    b VARCHAR(15)
);

Convention bundle

Code CV01
Rule convention.not_equal

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

This rule is sqlfluff fix compatible.

Name: convention.not_equal

Aliases: L061

Groups: all, convention

Configuration

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

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;
Code CV02
Rule convention.coalesce

Use COALESCE instead of IFNULL or NVL.

This rule is sqlfluff fix compatible.

Name: convention.coalesce

Aliases: L060

Groups: all, convention

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;
Code CV03
Rule convention.select_trailing_comma

Trailing commas within select clause.

This rule is sqlfluff fix compatible.

Name: convention.select_trailing_comma

Aliases: L038

Groups: all, core, convention

Configuration

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

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
Code CV04
Rule convention.count_rows

Use consistent syntax to express “count number of rows”.

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.

This rule is sqlfluff fix compatible.

Name: convention.count_rows

Aliases: L047

Groups: all, core, convention

Configuration

  • 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].

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
Code CV05
Rule convention.is_null

Comparisons with NULL should use “IS” or “IS NOT”.

This rule is sqlfluff fix compatible.

Name: convention.is_null

Aliases: L049

Groups: all, core, convention

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
Code CV06
Rule convention.terminator

Statements must end with a semi-colon.

This rule is sqlfluff fix compatible.

Name: convention.terminator

Aliases: L052

Groups: all, convention

Configuration

  • 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].

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;
Code CV07
Rule convention.statement_brackets

Top-level statements should not be wrapped in brackets.

This rule is sqlfluff fix compatible.

Name: convention.statement_brackets

Aliases: L053

Groups: all, convention

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)
Code CV08
Rule convention.left_join

Use LEFT JOIN instead of RIGHT JOIN.

Name: convention.left_join

Aliases: L055

Groups: all, convention

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;
Code CV09
Rule convention.blocked_words

Block a list of configurable words from being used.

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.

Name: convention.blocked_words

Aliases: L062

Groups: all, convention

Configuration

  • 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.

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);
Code CV10
Rule convention.quoted_literals

Consistent usage of preferred quotes for quoted literals.

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.

This rule is sqlfluff fix compatible.

Name: convention.quoted_literals

Aliases: L064

Groups: all, convention

Configuration

  • 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 literalin the file. Must be one of ['consistent', 'single_quotes', 'double_quotes'].

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
Code CV11
Rule convention.casting_style

Enforce consistent type casting style.

This rule is sqlfluff fix compatible.

Name: convention.casting_style

Aliases: L067

Groups: all, convention

Configuration

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

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;

Jinja bundle

Code JJ01
Rule jinja.padding

Jinja tags should have a single whitespace on either side.

This rule is only active if the jinja templater (or one of it’s subclasses, like the dbt templater) are used for the current file.

This rule is sqlfluff fix compatible.

Name: jinja.padding

Aliases: L046

Groups: all, core, jinja

Anti-pattern

Jinja tags with either no whitespace or very long whitespace are hard to read.

 SELECT {{    a     }} from {{ref('foo')}}

Best practice

A single whitespace surrounding Jinja tags, alternatively longer gaps containing newlines are acceptable.

 SELECT {{ a }} from {{ ref('foo') }};
 SELECT {{ a }} from {{
     ref('foo')
 }};

Layout bundle

Code LT01
Rule layout.spacing

Inappropriate Spacing.

This rule checks for an enforces the spacing as configured in Configuring Layout. This includes excessive whitespace, trailing whitespace at the end of a line and also the wrong spacing between elements on the line. Because of this wide reach you may find that you wish to add specific configuration in your project to tweak how specific elements are treated. Rather than configuration on this specific rule, use the sqlfluff.layout section of your configuration file to customise how this rule operates.

The character represents a space in the examples below.

This rule is sqlfluff fix compatible.

Name: layout.spacing

Aliases: L001, L005, L006, L008, L023, L024, L039, L048, L071

Groups: all, core, layout

Anti-pattern

SELECT
    a,        b(c) as d••
FROM foo••••
JOIN bar USING(a)

Best practice

  • Unless an indent or preceding a comment, whitespace should be a single space.

  • There should also be no trailing whitespace at the ends of lines.

  • There should be a space after USING so that it’s not confused for a function.

SELECT
    a, b(c) as d
FROM foo
JOIN bar USING (a)
Code LT02
Rule layout.indent

Incorrect Indentation.

This rule is sqlfluff fix compatible.

Name: layout.indent

Aliases: L002, L003, L004

Groups: all, core, layout

Anti-pattern

The character represents a space and the character represents a tab. In this example, the third line contains five spaces instead of four and the second line contains two spaces and one tab.

 SELECT
 ••→a,
 •••••b
 FROM foo

Best practice

Change the indentation to use a multiple of four spaces. This example also assumes that the indent_unit config value is set to space. If it had instead been set to tab, then the indents would be tabs instead.

 SELECT
 ••••a,
 ••••b
 FROM foo
Code LT03
Rule layout.operators

Operators should follow a standard for being before/after newlines.

The configuration for whether operators should be trailing or leading is part of Configuring Layout. The default configuration is:

[sqlfluff:layout:type:binary_operator]
line_position = leading

[sqlfluff:layout:type:comparison_operator]
line_position = leading

This rule is sqlfluff fix compatible.

Name: layout.operators

Aliases: L007

Groups: all, layout

Anti-pattern

In this example, if line_position = leading (or unspecified, as is the default), then the operator + should not be at the end of the second line.

SELECT
    a +
    b
FROM foo

Best practice

If line_position = leading (or unspecified, as this is the default), place the operator after the newline.

SELECT
    a
    + b
FROM foo

If line_position = trailing, place the operator before the newline.

SELECT
    a +
    b
FROM foo
Code LT04
Rule layout.commas

Leading/Trailing comma enforcement.

The configuration for whether operators should be trailing or leading is part of Configuring Layout. The default configuration is:

[sqlfluff:layout:type:comma]
line_position = trailing

This rule is sqlfluff fix compatible.

Name: layout.commas

Aliases: L019

Groups: all, layout

Anti-pattern

There is a mixture of leading and trailing commas.

SELECT
    a
    , b,
    c
FROM foo

Best practice

By default, SQLFluff prefers trailing commas. However it is configurable for leading commas. The chosen style must be used consistently throughout your SQL.

SELECT
    a,
    b,
    c
FROM foo

-- Alternatively, set the configuration file to 'leading'
-- and then the following would be acceptable:

SELECT
    a
    , b
    , c
FROM foo
Code LT05
Rule layout.long_lines

Line is too long.

This rule is sqlfluff fix compatible.

Name: layout.long_lines

Aliases: L016

Groups: all, core, layout

Configuration

  • ignore_comment_clauses: Should comment clauses (e.g. column comments) be ignored when linting line lengths? Must be one of [True, False].

  • ignore_comment_lines: Should lines that contain only whitespace and comments be ignored when linting line lengths? Must be one of [True, False].

Code LT06
Rule layout.functions

Function name not immediately followed by parenthesis.

This rule is sqlfluff fix compatible.

Name: layout.functions

Aliases: L017

Groups: all, core, layout

Anti-pattern

In this example, there is a space between the function and the parenthesis.

SELECT
    sum (a)
FROM foo

Best practice

Remove the space between the function and the parenthesis.

SELECT
    sum(a)
FROM foo
Code LT07
Rule layout.cte_bracket

WITH clause closing bracket should be on a new line.

This rule is sqlfluff fix compatible.

Name: layout.cte_bracket

Aliases: L018

Groups: all, core, layout

Anti-pattern

In this example, the closing bracket is on the same line as CTE.

 WITH zoo AS (
     SELECT a FROM foo)

 SELECT * FROM zoo

Best practice

Move the closing bracket on a new line.

WITH zoo AS (
    SELECT a FROM foo
)

SELECT * FROM zoo
Code LT08
Rule layout.cte_newline

Blank line expected but not found after CTE closing bracket.

This rule is sqlfluff fix compatible.

Name: layout.cte_newline

Aliases: L022

Groups: all, core, layout

Anti-pattern

There is no blank line after the CTE closing bracket. In queries with many CTEs, this hinders readability.

WITH plop AS (
    SELECT * FROM foo
)
SELECT a FROM plop

Best practice

Add a blank line.

WITH plop AS (
    SELECT * FROM foo
)

SELECT a FROM plop
Code LT09
Rule layout.select_targets

Select targets should be on a new line unless there is only one select target.

This rule is sqlfluff fix compatible.

Name: layout.select_targets

Aliases: L036

Groups: all, layout

Configuration

  • wildcard_policy: Treatment of wildcards. Defaults to single. Must be one of ['single', 'multiple'].

Note

By default, a wildcard (e.g. SELECT *) is considered a single select target. If you want it to be treated as multiple select targets, configure wildcard_policy = multiple.

Anti-pattern

Multiple select targets on the same line.

select a, b
from foo;

-- Single select target on its own line.

SELECT
    a
FROM foo;

Best practice

Multiple select targets each on their own line.

select
    a,
    b
from foo;

-- Single select target on the same line as the ``SELECT``
-- keyword.

SELECT a
FROM foo;

-- When select targets span multiple lines, however they
-- can still be on a new line.

SELECT
    SUM(
        1 + SUM(
            2 + 3
        )
    ) AS col
FROM test_table;
Code LT10
Rule layout.select_modifiers

SELECT modifiers (e.g. DISTINCT) must be on the same line as SELECT.

This rule is sqlfluff fix compatible.

Name: layout.select_modifiers

Aliases: L041

Groups: all, core, layout

Anti-pattern

select
    distinct a,
    b
from x

Best practice

select distinct
    a,
    b
from x
Code LT11
Rule layout.set_operators

Set operators should be surrounded by newlines.

This rule is sqlfluff fix compatible.

Name: layout.set_operators

Aliases: L065

Groups: all, core, layout

Anti-pattern

In this example, UNION ALL is not on a line itself.

SELECT 'a' AS col UNION ALL
SELECT 'b' AS col

Best practice

SELECT 'a' AS col
UNION ALL
SELECT 'b' AS col
Code LT12
Rule layout.end_of_file

Files must end with a single trailing newline.

This rule is sqlfluff fix compatible.

Name: layout.end_of_file

Aliases: L009, layout.end-of-file

Groups: all, core, layout

Anti-pattern

The content in file does not end with a single trailing newline. The $ represents end of file.

 SELECT
     a
 FROM foo$

 -- Ending on an indented line means there is no newline
 -- at the end of the file, the • represents space.

 SELECT
 ••••a
 FROM
 ••••foo
 ••••$

 -- Ending on a semi-colon means the last line is not a
 -- newline.

 SELECT
     a
 FROM foo
 ;$

 -- Ending with multiple newlines.

 SELECT
     a
 FROM foo

 $

Best practice

Add trailing newline to the end. The $ character represents end of file.

 SELECT
     a
 FROM foo
 $

 -- Ensuring the last line is not indented so is just a
 -- newline.

 SELECT
 ••••a
 FROM
 ••••foo
 $

 -- Even when ending on a semi-colon, ensure there is a
 -- newline after.

 SELECT
     a
 FROM foo
 ;
 $
Code LT13
Rule layout.start_of_file

Files must not begin with newlines or whitespace.

This rule is sqlfluff fix compatible.

Name: layout.start_of_file

Aliases: L050

Groups: all, layout

Anti-pattern

The file begins with newlines or whitespace. The ^ represents the beginning of the file.

 ^

 SELECT
     a
 FROM foo

 -- Beginning on an indented line is also forbidden,
 -- (the • represents space).

 ••••SELECT
 ••••a
 FROM
 ••••foo

Best practice

Start file on either code or comment. (The ^ represents the beginning of the file.)

 ^SELECT
     a
 FROM foo

 -- Including an initial block comment.

 ^/*
 This is a description of my SQL code.
 */
 SELECT
     a
 FROM
     foo

 -- Including an initial inline comment.

 ^--This is a description of my SQL code.
 SELECT
     a
 FROM
     foo

References bundle

Code RF01
Rule references.from

References cannot reference objects not present in FROM clause.

Name: references.from

Aliases: L026

Groups: all, core, references

Configuration

  • force_enable: Run this rule even for dialects where this rule is disabled by default. Must be one of [True, False].

Note

This rule is disabled by default for BigQuery, Databricks, Hive, Redshift, SOQL and SparkSQL due to the support of things like structs and lateral views which trigger false positives. It can be enabled with the force_enable = True flag.

Anti-pattern

In this example, the reference vee has not been declared.

SELECT
    vee.a
FROM foo

Best practice

Remove the reference.

SELECT
    a
FROM foo
Code RF02
Rule references.qualification

References should be qualified if select has more than one referenced table/view.

Name: references.qualification

Aliases: L027

Groups: all, references

Note

Except if they’re present in a USING clause.

Anti-pattern

In this example, the reference vee has not been declared, and the variables a and b are potentially ambiguous.

SELECT a, b
FROM foo
LEFT JOIN vee ON vee.a = foo.a

Best practice

Add the references.

SELECT foo.a, vee.b
FROM foo
LEFT JOIN vee ON vee.a = foo.a
Code RF03
Rule references.consistent

References should be consistent in statements with a single table.

This rule is sqlfluff fix compatible.

Name: references.consistent

Aliases: L028

Groups: all, references

Configuration

  • force_enable: Run this rule even for dialects where this rule is disabled by default. Must be one of [True, False].

  • single_table_references: The expectation for references in single-table select. Must be one of ['consistent', 'qualified', 'unqualified'].

Note

For BigQuery, Hive and Redshift this rule is disabled by default. This is due to historical false positives associated with STRUCT data types. This default behaviour may be changed in the future. The rule can be enabled with the force_enable = True flag.

“consistent” will be fixed to “qualified” if inconsistency is found.

Anti-pattern

In this example, only the field b is referenced.

SELECT
    a,
    foo.b
FROM foo

Best practice

Add or remove references to all fields.

SELECT
    a,
    b
FROM foo

-- Also good

SELECT
    foo.a,
    foo.b
FROM foo
Code RF04
Rule references.keywords

Keywords should not be used as identifiers.

Although unreserved keywords can be used as identifiers, and reserved words can be used as quoted identifiers, best practice is to avoid where possible, to avoid any misunderstandings as to what the alias represents.

Name: references.keywords

Aliases: L029

Groups: all, references

Configuration

  • ignore_words: Comma separated list of words to ignore from rule.

  • ignore_words_regex: Words to ignore from rule if they are a partial match for the regular expression. To ignore only full matches you can use ^ (beginning of text) and $ (end of text). Due to regular expression operator precedence, it is good practice to use parentheses around everything between ^ and $.

  • quoted_identifiers_policy: Types of quoted identifiers to flag violations for. Must be one of ['all', 'aliases', 'column_aliases', 'none'].

  • unquoted_identifiers_policy: Types of unquoted identifiers to flag violations for. Must be one of ['all', 'aliases', 'column_aliases'].

Note

Note that reserved keywords cannot be used as unquoted identifiers and will cause parsing errors and so are not covered by this rule.

Anti-pattern

In this example, SUM (built-in function) is used as an alias.

SELECT
    sum.a
FROM foo AS sum

Best practice

Avoid keywords as the name of an alias.

SELECT
    vee.a
FROM foo AS vee
Code RF05
Rule references.special_chars

Do not use special characters in identifiers.

Name: references.special_chars

Aliases: L057

Groups: all, references

Configuration

  • additional_allowed_characters: Optional list of extra allowed characters, in addition to alphanumerics (A-Z, a-z, 0-9) and underscores.

  • allow_space_in_identifier: Should spaces in identifiers be allowed? Must be one of [True, False].

  • ignore_words: Comma separated list of words to ignore from rule.

  • ignore_words_regex: Words to ignore from rule if they are a partial match for the regular expression. To ignore only full matches you can use ^ (beginning of text) and $ (end of text). Due to regular expression operator precedence, it is good practice to use parentheses around everything between ^ and $.

  • quoted_identifiers_policy: Types of quoted identifiers to flag violations for. Must be one of ['all', 'aliases', 'column_aliases', 'none'].

  • unquoted_identifiers_policy: Types of unquoted identifiers to flag violations for. Must be one of ['all', 'aliases', 'column_aliases'].

Anti-pattern

Using special characters within identifiers when creating or aliasing objects.

CREATE TABLE DBO.ColumnNames
(
    [Internal Space] INT,
    [Greater>Than] INT,
    [Less<Than] INT,
    Number# INT
)

Best practice

Identifiers should include only alphanumerics and underscores.

CREATE TABLE DBO.ColumnNames
(
    [Internal_Space] INT,
    [GreaterThan] INT,
    [LessThan] INT,
    NumberVal INT
)
Code RF06
Rule references.quoting

Unnecessary quoted identifier.

This rule will fail if the quotes used to quote an identifier are (un)necessary depending on the force_quote_identifier configuration.

When prefer_quoted_identifiers = False (default behaviour), the quotes are unnecessary, except for reserved keywords and special characters in identifiers.

This rule is sqlfluff fix compatible.

Name: references.quoting

Aliases: L059

Groups: all, references

Configuration

  • force_enable: Run this rule even for dialects where this rule is disabled by default. Must be one of [True, False].

  • ignore_words: Comma separated list of words to ignore from rule.

  • ignore_words_regex: Words to ignore from rule if they are a partial match for the regular expression. To ignore only full matches you can use ^ (beginning of text) and $ (end of text). Due to regular expression operator precedence, it is good practice to use parentheses around everything between ^ and $.

  • prefer_quoted_identifiers: If True, requires every identifier to be quoted. Defaults to False. Must be one of [True, False].

  • prefer_quoted_keywords: If True, requires every keyword used as an identifier to be quoted. Defaults to False. Must be one of [True, False].

Note

This rule is disabled by default for Postgres and Snowflake because they allow quotes as part of the column name. In other words, date and "date" are two different columns.

It can be enabled with the force_enable = True flag.

Anti-pattern

In this example, a valid unquoted identifier, that is also not a reserved keyword, is needlessly quoted.

SELECT 123 as "foo"

Best practice

Use unquoted identifiers where possible.

SELECT 123 as foo

When prefer_quoted_identifiers = True, the quotes are always necessary, no matter if the identifier is valid, a reserved keyword, or contains special characters.

Note

Note due to different quotes being used by different dialects supported by SQLFluff, and those quotes meaning different things in different contexts, this mode is not sqlfluff fix compatible.

Anti-pattern

In this example, a valid unquoted identifier, that is also not a reserved keyword, is required to be quoted.

SELECT 123 as foo

Best practice Use quoted identifiers.

SELECT 123 as "foo" -- For ANSI, ...
-- or
SELECT 123 as `foo` -- For BigQuery, MySql, ...

Structure bundle

Code ST01
Rule structure.else_null

Do not specify else null in a case when statement (redundant).

This rule is sqlfluff fix compatible.

Name: structure.else_null

Aliases: L035

Groups: all, structure

Anti-pattern

select
    case
        when name like '%cat%' then 'meow'
        when name like '%dog%' then 'woof'
        else null
    end
from x

Best practice

Omit else null

select
    case
        when name like '%cat%' then 'meow'
        when name like '%dog%' then 'woof'
    end
from x
Code ST02
Rule structure.simple_case

Unnecessary CASE statement.

This rule is sqlfluff fix compatible.

Name: structure.simple_case

Aliases: L043

Groups: all, structure

Anti-pattern

CASE statement returns booleans.

select
    case
        when fab > 0 then true
        else false
    end as is_fab
from fancy_table

-- This rule can also simplify CASE statements
-- that aim to fill NULL values.

select
    case
        when fab is null then 0
        else fab
    end as fab_clean
from fancy_table

-- This also covers where the case statement
-- replaces NULL values with NULL values.

select
    case
        when fab is null then null
        else fab
    end as fab_clean
from fancy_table

Best practice

Reduce to WHEN condition within COALESCE function.

select
    coalesce(fab > 0, false) as is_fab
from fancy_table

-- To fill NULL values.

select
    coalesce(fab, 0) as fab_clean
from fancy_table

-- NULL filling NULL.

select fab as fab_clean
from fancy_table
Code ST03
Rule structure.unused_cte

Query defines a CTE (common-table expression) but does not use it.

Name: structure.unused_cte

Aliases: L045

Groups: all, core, structure

Anti-pattern

Defining a CTE that is not used by the query is harmless, but it means the code is unnecessary and could be removed.

WITH cte1 AS (
  SELECT a
  FROM t
),
cte2 AS (
  SELECT b
  FROM u
)

SELECT *
FROM cte1

Best practice

Remove unused CTEs.

WITH cte1 AS (
  SELECT a
  FROM t
)

SELECT *
FROM cte1
Code ST04
Rule structure.nested_case

Nested CASE statement in ELSE clause could be flattened.

This rule is sqlfluff fix compatible.

Name: structure.nested_case

Aliases: L058

Groups: all, structure

Anti-pattern

In this example, the outer CASE’s ELSE is an unnecessary, nested CASE.

SELECT
  CASE
    WHEN species = 'Cat' THEN 'Meow'
    ELSE
    CASE
       WHEN species = 'Dog' THEN 'Woof'
    END
  END as sound
FROM mytable

Best practice

Move the body of the inner CASE to the end of the outer one.

SELECT
  CASE
    WHEN species = 'Cat' THEN 'Meow'
    WHEN species = 'Dog' THEN 'Woof'
  END AS sound
FROM mytable
Code ST05
Rule structure.subquery

Join/From clauses should not contain subqueries. Use CTEs instead.

By default this rule is configured to allow subqueries within FROM clauses but not within JOIN clauses. If you prefer a stricter lint then this is configurable.

This rule is sqlfluff fix compatible.

Name: structure.subquery

Aliases: L042

Groups: all, structure

Configuration

  • forbid_subquery_in: Which clauses should be linted for subqueries? Must be one of ['join', 'from', 'both'].

Note

Some dialects don’t allow CTEs, and for those dialects this rule makes no sense and should be disabled.

Anti-pattern

select
    a.x, a.y, b.z
from a
join (
    select x, z from b
) using(x)

Best practice

with c as (
    select x, z from b
)
select
    a.x, a.y, c.z
from a
join c using(x)
Code ST06
Rule structure.column_order

Select wildcards then simple targets before calculations and aggregates.

This rule is sqlfluff fix compatible.

Name: structure.column_order

Aliases: L034

Groups: all, structure

Anti-pattern

select
    a,
    *,
    row_number() over (partition by id order by date) as y,
    b
from x

Best practice

Order select targets in ascending complexity

select
    *,
    a,
    b,
    row_number() over (partition by id order by date) as y
from x
Code ST07
Rule structure.using

Prefer specifying join keys instead of using USING.

This rule is sqlfluff fix compatible.

Name: structure.using

Aliases: L032

Groups: all, structure

Note

This rule was originally taken from the dbt Style Guide which notes that:

Certain warehouses have inconsistencies in USING results (specifically Snowflake).

In fact dbt removed it from their style guide in February 2022. However, some like the rule, so for now we will keep it in SQLFluff, but encourage those that do not find value in the rule, to turn it off.

Note

This rule is disabled for ClickHouse as it supports USING without brackets which this rule does not support.

Anti-pattern

SELECT
    table_a.field_1,
    table_b.field_2
FROM
    table_a
INNER JOIN table_b USING (id)

Best practice

Specify the keys directly

SELECT
    table_a.field_1,
    table_b.field_2
FROM
    table_a
INNER JOIN table_b
    ON table_a.id = table_b.id
Code ST08
Rule structure.distinct

DISTINCT used with parentheses.

This rule is sqlfluff fix compatible.

Name: structure.distinct

Aliases: L015

Groups: all, structure, core

Anti-pattern

In this example, parentheses are not needed and confuse DISTINCT with a function. The parentheses can also be misleading about which columns are affected by the DISTINCT (all the columns!).

SELECT DISTINCT(a), b FROM foo

Best practice

Remove parentheses to be clear that the DISTINCT applies to both columns.

SELECT DISTINCT a, b FROM foo
Code ST09
Rule structure.join_condition_order

Joins should list the table referenced earlier/later first.

This rule will break conditions from join clauses down into subconditions using the “and” and “or” binary operators.

Subconditions that are made up of a qualified column reference, a comparison operator and another qualified column reference are then evaluated to check whether they list the table that was referenced earlier - or later, depending on the preferred_first_table_in_join_clause configuration.

Subconditions that do not follow that pattern are ignored by this rule.

This rule is sqlfluff fix compatible.

Name: structure.join_condition_order

Groups: all, structure

Configuration

  • preferred_first_table_in_join_clause: Which table to list first when joining two tables. Defaults to earlier. Must be one of ['earlier', 'later'].

Note

Joins in WHERE clauses are currently not supported by this rule.

Anti-pattern

In this example, the tables that were referenced later are listed first and the preferred_first_table_in_join_clause configuration is set to earlier.

select
    foo.a,
    foo.b,
    bar.c
from foo
left join bar
    -- This subcondition does not list
    -- the table referenced earlier first:
    on bar.a = foo.a
    -- Neither does this subcondition:
    and bar.b = foo.b

Best practice

List the tables that were referenced earlier first.

select
    foo.a,
    foo.b,
    bar.c
from foo
left join bar
    on foo.a = bar.a
    and foo.b = bar.b

TSQL bundle

Code TQ01
Rule tsql.sp_prefix

SP_ prefix should not be used for user-defined stored procedures in T-SQL.

Name: tsql.sp_prefix

Aliases: L056

Groups: all, tsql

Anti-pattern

The SP_ prefix is used to identify system procedures and can adversely affect performance of the user-defined stored procedure. It can also break system procedures if there is a naming conflict.

 CREATE PROCEDURE dbo.sp_pull_data
 AS
 SELECT
     ID,
     DataDate,
     CaseOutput
 FROM table1

Best practice

Use a different name for the stored procedure.

 CREATE PROCEDURE dbo.pull_data
 AS
 SELECT
     ID,
     DataDate,
     CaseOutput
 FROM table1

 -- Alternatively prefix with USP_ to
 -- indicate a user-defined stored procedure.

 CREATE PROCEDURE dbo.usp_pull_data
 AS
 SELECT
     ID,
     DataDate,
     CaseOutput
 FROM table1