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.

Specific Rules

Configuration and examples for individual rules.

class Rule_L001(code, description, **kwargs)

Unnecessary trailing whitespace.

sqlfluff fix compatible.

Anti-pattern

The • character represents a space.

 SELECT
     a
 FROM foo••

Best practice

Remove trailing spaces.

SELECT
    a
FROM foo

class Rule_L002(code, description, **kwargs)

Mixed Tabs and Spaces in single whitespace.

Configuration

tab_space_size: The number of spaces to consider equal to one tab. Used in the fixing step of this rule. Must be one of range(0, 100).

sqlfluff fix compatible.

This rule will fail if a single section of whitespace contains both tabs and spaces.

Anti-pattern

The • character represents a space and the → character represents a tab.

In this example, the second line contains two spaces and one tab.

 SELECT
 ••→a
 FROM foo

Best practice

Change the line to use spaces only.

 SELECT
 ••••a
 FROM foo

class Rule_L003(code, description, **kwargs)

Indentation not consistent with previous lines.

sqlfluff fix compatible.

Configuration

indent_unit: Whether to use tabs or spaces to add new indents. Must be one of ['space', 'tab'].

tab_space_size: The number of spaces to consider equal to one tab. Used in the fixing step of this rule. Must be one of range(0, 100).

Note

This rule used to be _”Indentation length is not a multiple of tab_space_size”_, but was changed to be much smarter.

Anti-pattern

The • character represents a space.

In this example, the third line contains five spaces instead of four.

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

Best practice

Change the indentation to use a multiple of four spaces.

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

class Rule_L004(code, description, **kwargs)

Incorrect indentation type.

sqlfluff fix compatible.

Configuration

indent_unit: Whether to use tabs or spaces to add new indents. Must be one of ['space', 'tab'].

tab_space_size: The number of spaces to consider equal to one tab. Used in the fixing step of this rule. Must be one of range(0, 100).

Note 1: spaces are only fixed to tabs if the number of spaces in the indent is an integer multiple of the tab_space_size config. Note 2: fixes are only applied to indents at the start of a line. Indents after other text on the same line are not fixed.

Anti-pattern

Using tabs instead of spaces when indent_unit config set to spaces (default).

 select
 ••••a,
 →   b
 from foo

Best practice

Change the line to use spaces only.

 select
 ••••a,
 ••••b
 from foo

class Rule_L005(code, description, **kwargs)

Commas should not have whitespace directly before them.

sqlfluff fix compatible.

Unless it’s an indent. Trailing/leading commas are dealt with in a different rule.

Anti-pattern

The • character represents a space.

There is an extra space in line two before the comma.

 SELECT
     a•,
     b
 FROM foo

Best practice

Remove the space before the comma.

SELECT
    a,
    b
FROM foo

class Rule_L006(code, description, **kwargs)

Operators should be surrounded by a single whitespace.

sqlfluff fix compatible.

Anti-pattern

In this example, there is a space missing space between the operator and ‘b’.

SELECT
    a +b
FROM foo

Best practice

Keep a single space.

SELECT
    a + b
FROM foo

class Rule_L007(code, description, **kwargs)

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

Anti-pattern

The • character represents a space.

If operator_new_lines = after (or unspecified, as this is the default)

In this example, the operator ‘+’ should not be at the end of the second line.

SELECT
    a +
    b
FROM foo

Best practice

If operator_new_lines = after (or unspecified, as this is the default)

Place the operator after the newline.

SELECT
    a
    + b
FROM foo

If operator_new_lines = before

Place the operator before the newline.

SELECT
    a +
    b
FROM foo

class Rule_L008(code, description, **kwargs)

Commas should be followed by a single whitespace unless followed by a comment.

sqlfluff fix compatible.

Anti-pattern

The • character represents a space.

In this example, there is no space between the comma and ‘zoo’.

SELECT
    *
FROM foo
WHERE a IN ('plop','zoo')

Best practice

Keep a single space after the comma.

 SELECT
     *
 FROM foo
 WHERE a IN ('plop',•'zoo')

class Rule_L009(code, description, **kwargs)

Files must end with a single trailing newline.

sqlfluff fix compatible.

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
 ;
 $

class Rule_L010(code, description, **kwargs)

Inconsistent capitalisation of keywords.

sqlfluff fix compatible.

Configuration

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

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

class Rule_L011(code, description, **kwargs)

Implicit/explicit aliasing of table.

sqlfluff fix compatible.

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

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

class Rule_L012(code, description, **kwargs)

Implicit/explicit aliasing of columns.

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

NB: This rule inherits its functionality from Rule_L011 but is separate so that they can be enabled and disabled separately.

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

class Rule_L013(code, description, **kwargs)

Column expression without alias. Use explicit AS clause.

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

class Rule_L014(code, description, **kwargs)

Inconsistent capitalisation of unquoted identifiers.

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

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

sqlfluff fix compatible.

The functionality for this rule is inherited from Rule_L010.

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

class Rule_L015(code, description, **kwargs)

DISTINCT used with parentheses.

sqlfluff fix compatible.

Anti-pattern

In this example, parenthesis are not needed and confuse

DISTINCT with a function. The parenthesis can also be misleading

in which columns they apply to.

SELECT DISTINCT(a), b FROM foo

Best practice

Remove parenthesis to be clear that the DISTINCT applies to

both columns.

SELECT DISTINCT a, b FROM foo

class Rule_L016(code, description, **kwargs)

Line is too long

sqlfluff fix compatible.

Configuration

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

indent_unit: Whether to use tabs or spaces to add new indents. Must be one of ['space', 'tab'].

max_line_length: The maximum length of a line to allow without raising a violation. Must be one of range(0, 1000).

tab_space_size: The number of spaces to consider equal to one tab. Used in the fixing step of this rule. Must be one of range(0, 100).

class Rule_L017(code, description, **kwargs)

Function name not immediately followed by bracket.

sqlfluff fix compatible.

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

class Rule_L018(code, description, **kwargs)

WITH clause closing bracket should be aligned with WITH keyword.

sqlfluff fix compatible.

Anti-pattern

The • character represents a space.

In this example, the closing bracket is not aligned with WITH keyword.

 WITH zoo AS (
     SELECT a FROM foo
 ••••)

 SELECT * FROM zoo

Best practice

Remove the spaces to align the WITH keyword with the closing bracket.

WITH zoo AS (
    SELECT a FROM foo
)

SELECT * FROM zoo

class Rule_L019(code, description, **kwargs)

Leading/Trailing comma enforcement.

sqlfluff fix compatible.

Configuration

comma_style: The comma style to enforce. Must be one of ['leading', 'trailing'].

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. Whichever option you chose

it does expect you to be consistent.

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

class Rule_L020(code, description, **kwargs)

Table aliases should be unique within each clause.

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 alias's when referencing two tables with same name from two
-- different schemas

SELECT
    f1.a,
    f2.b
FROM
    2020.foo AS f1,
    2021.foo AS f2

class Rule_L021(code, description, **kwargs)

Ambiguous use of DISTINCT in select statement with GROUP BY.

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

class Rule_L022(code, description, **kwargs)

Blank line expected but not found after CTE closing bracket.

sqlfluff fix compatible.

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

class Rule_L023(code, description, **kwargs)

Single whitespace expected after AS in WITH clause.

sqlfluff fix compatible.

Anti-pattern

WITH plop AS(
    SELECT * FROM foo
)

SELECT a FROM plop

Best practice

The • character represents a space.

Add a space after AS, to avoid confusing

it for a function.

 WITH plop AS•(
     SELECT * FROM foo
 )

 SELECT a FROM plop

class Rule_L024(code, description, **kwargs)

Single whitespace expected after USING in JOIN clause.

sqlfluff fix compatible.

Anti-pattern

SELECT b
FROM foo
LEFT JOIN zoo USING(a)

Best practice

The • character represents a space.

Add a space after USING, to avoid confusing it

for a function.

 SELECT b
 FROM foo
 LEFT JOIN zoo USING•(a)

expand_children: Optional[List[str]] = None

class Rule_L025(code, description, **kwargs)

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

sqlfluff fix compatible.

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

class Rule_L026(code, description, **kwargs)

References cannot reference objects not present in FROM clause.

Configuration

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

NB: This rule is disabled by default for BigQuery due to its use of structs 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

class Rule_L027(code, description, **kwargs)

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

NB: 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

class Rule_L028(code, description, **kwargs)

References should be consistent in statements with a single table.

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

NB: This rule is disabled by default for BigQuery due to its use of structs which trigger false positives. It can be enabled with the force_enable = True flag.

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

class Rule_L029(code, description, **kwargs)

Keywords should not be used as identifiers.

Configuration

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

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

class Rule_L030(code, description, **kwargs)

Inconsistent capitalisation of function names.

Configuration

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

sqlfluff fix compatible.

The functionality for this rule is inherited from Rule_L010.

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

class Rule_L031(code, description, **kwargs)

Avoid table aliases in from clauses and join conditions.

sqlfluff fix compatible.

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
    table.a,
    table_alias.b,
FROM
    table
    LEFT JOIN table AS table_alias ON
        table.foreign_key = table_alias.foreign_key

class Rule_L032(code, description, **kwargs)

Prefer specifying join keys instead of using USING.

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

class Rule_L033(code, description, **kwargs)

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

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

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

class Rule_L034(code, description, **kwargs)

Select wildcards then simple targets before calculations and aggregates.

sqlfluff fix compatible.

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

class Rule_L035(code, description, **kwargs)

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

sqlfluff fix compatible.

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

class Rule_L036(code, description, **kwargs)

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

sqlfluff fix compatible.

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

class Rule_L037(code, description, **kwargs)

Ambiguous ordering directions for columns in order by clause.

sqlfluff fix compatible.

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

class Rule_L038(code, description, **kwargs)

Trailing commas within select clause.

Configuration

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

sqlfluff fix compatible.

For some 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

class Rule_L039(code, description, **kwargs)

Unnecessary whitespace found.

sqlfluff fix compatible.

Anti-pattern

SELECT
    a,        b
FROM foo

Best practice

Unless an indent or preceding a comment, whitespace should

be a single space.

SELECT
    a, b
FROM foo

class Rule_L040(code, description, **kwargs)

Inconsistent capitalisation of boolean/null literal.

Configuration

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

sqlfluff fix compatible.

The functionality for this rule is inherited from Rule_L010.

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 cases are used consistently

select
    a,
    NULL,
    TRUE,
    FALSE
from foo

-- Also good

select
    a,
    null,
    true,
    false
from foo

class Rule_L041(code, description, **kwargs)

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

sqlfluff fix compatible.

Anti-pattern

select
    distinct a,
    b
from x

Best practice

select distinct
    a,
    b
from x

class Rule_L042(code, description, **kwargs)

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

Configuration

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

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.

NB: 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)

class Rule_L043(code, description, **kwargs)

Unnecessary CASE statement.

sqlfluff fix compatible.

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

class Rule_L044(code, description, **kwargs)

Query produces an unknown number of result columns.

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

class Rule_L045(code, description, **kwargs)

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

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

class Rule_L046(code, description, **kwargs)

Jinja tags should have a single whitespace on either side.

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')
 }};

class Rule_L047(code, description, **kwargs)

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

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

sqlfluff fix compatible.

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

class Rule_L048(code, description, **kwargs)

Quoted literals should be surrounded by a single whitespace.

sqlfluff fix compatible.

Anti-pattern

In this example, there is a space missing space between the string

'foo' and the keyword AS.

SELECT
    'foo'AS bar
FROM foo

Best practice

Keep a single space.

SELECT
    'foo' AS bar
FROM foo

class Rule_L049(code, description, **kwargs)

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

sqlfluff fix compatible.

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

class Rule_L050(code, description, **kwargs)

Files must not begin with newlines or whitespace.

sqlfluff fix compatible.

Anti-pattern

The content in 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

class Rule_L051(code, description, **kwargs)

INNER JOIN must be fully qualified.

sqlfluff fix compatible.

Anti-pattern

Lone JOIN is used.

 SELECT
     foo
 FROM bar
 JOIN baz;

Best practice

Use INNER JOIN rather than JOIN.

 SELECT
     foo
 FROM bar
 INNER JOIN baz;

class Rule_L052(code, description, **kwargs)

Statements must end with a semi-colon.

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

sqlfluff fix compatible.

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;

class Rule_L053(code, description, **kwargs)

Top-level statements should not be wrapped in brackets.

sqlfluff fix compatible.

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)

class Rule_L054(code, description, **kwargs)

Inconsistent column references in GROUP BY/ORDER BY clauses.

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

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;

class Rule_L055(code, description, **kwargs)

Use LEFT JOIN instead of RIGHT JOIN.

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;

class Rule_L056(code, description, **kwargs)

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

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

class Rule_L057(code, description, **kwargs)

Do not use special characters in identifiers.

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

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
)

class Rule_L058(code, description, **kwargs)

Nested CASE statement in ELSE clause could be flattened.

sqlfluff fix compatible.

Anti-pattern

In this example, the outer CASE’s ELSE is an unnecessary other 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

class Rule_L059(code, description, **kwargs)

Unnecessary quoted identifier.

sqlfluff fix compatible.

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

class Rule_L060(code, description, **kwargs)

Use COALESCE instead of IFNULL or NVL.

sqlfluff fix compatible.

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;

class Rule_L061(code, description, **kwargs)

Use != instead of <> for “not equal to” comparisons.

sqlfluff fix compatible.

Anti-pattern

<> means not equal but doesn’t sound like this when we say it out loud.

SELECT * FROM X WHERE 1 <> 2;

Best practice

Use != instead because it’s sounds more natural and is more common in other

programming languages.

SELECT * FROM X WHERE 1 != 2;

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: L006

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

a.a *  a.b AS bad_2,  -- noqa: L007, L006

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

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

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 L012 from this line forward
SELECT col_a a FROM foo -- noqa: disable=L012

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