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 |
---|---|---|---|
L011 |
|||
L012 |
|||
L013 |
|||
L020 |
|||
L025 |
|||
L066 |
|||
L031 |
|||
L021 |
|||
L033 |
|||
L037 |
|||
L044 |
|||
L051 |
|||
L054 |
|||
L068 |
|||
L010 |
|||
L014 |
|||
L030 |
|||
L040 |
|||
L063 |
|||
L061 |
|||
L060 |
|||
L038 |
|||
L047 |
|||
L049 |
|||
L052 |
|||
L053 |
|||
L055 |
|||
L062 |
|||
L064 |
|||
L067 |
|||
L046 |
|||
L001, L005, L006, L008, L023, L024, L039, L048, L071 |
|||
L002, L003, L004 |
|||
L007 |
|||
L019 |
|||
L016 |
|||
L017 |
|||
L018 |
|||
L022 |
|||
L036 |
|||
L041 |
|||
L065 |
|||
L009, layout.end-of-file |
|||
L050 |
|||
L026 |
|||
L027 |
|||
L028 |
|||
L029 |
|||
L057 |
|||
L059 |
|||
L035 |
|||
L043 |
|||
L045 |
|||
L058 |
|||
L042 |
|||
L034 |
|||
L032 |
|||
L015 |
|||
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
Configuration
alias_case_check
: How to handle comparison casefolding in an alias. Must be one of['dialect', 'case_insensitive', 'quoted_cs_naked_upper', 'quoted_cs_naked_lower', 'case_sensitive']
.
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 ofrange(0, 1000)
.min_alias_length
: The minimum length of an alias to allow without raising a violation. Must be one ofrange(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:
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
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, andc
is used forcustomers
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 aSELECT
statement withGROUP BY
.¶When using
GROUP BY
a DISTINCT` clause should not be necessary as every non-distinctSELECT
clause must be included in theGROUP BY
clause.Name:
ambiguous.distinct
Aliases:
L021
Groups:
all
,core
,ambiguous
Anti-pattern
DISTINCT
andGROUP BY
are conflicting.SELECT DISTINCT a FROM foo GROUP BY a
Best practice
Remove
DISTINCT
orGROUP BY
. In our case, removingGROUP BY
is better.SELECT DISTINCT a FROM foo
- Code AM02¶
- Rule ambiguous.union¶
UNION [DISTINCT|ALL]
is preferred over justUNION
.¶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
andUNION DISTINCT
(ansi
,hive
,mysql
, andredshift
).Anti-pattern
In this example,
UNION DISTINCT
should be preferred overUNION
, because explicit is better than implicit.SELECT a, b FROM table_1 UNION SELECT a, b FROM table_2
Best practice
Specify
DISTINCT
orALL
afterUNION
(note thatDISTINCT
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 specifyASC
orDESC
, 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 columnsa
,b
, andc
, the actual columns returned will be wrong/different if columns are added to or deleted from the input table.UNION
andDIFFERENCE
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 columnc
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 thanJOIN
.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 fromWINDOW
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 whereasFROM
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 applies to all unquoted identifiers, whether references or aliases, and whether they refer to columns or other objects (such as tables or schemas).
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, snake_case, and camelCase. This is separate fromcapitalisation_policy
as it should not be applied to keywords.Camel, Pascal, and Snake will never be inferred when the policy is setto consistent. This is because snake can cause destructive changes tothe identifier, and unlinted code is too easily mistaken for camel and pascal. If, when set to consistent, no consistent case is found, it willdefault to upper. Must be one of['consistent', 'upper', 'lower', 'pascal', 'capitalise', 'snake', 'camel']
.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']
.
Note
In most dialects, unquoted identifiers are treated as case-insensitive and so the fixes proposed by this rule do not change the interpretation of the query. HOWEVER, some databases (notably Google BigQuery and ClickHouse) do take the casing of unquoted identifiers into account when determining the casing of the column heading in the result.
As this feature is only present in a few dialects, and not widely understood by users, we regard it as an antipattern. It is more widely understood that if the case of an identifier matters, then it should be quoted. If you, or your organisation, do wish to rely on this feature, we recommend that you disabled this rule (see Enabling and Disabling Rules).
Anti-pattern
In this example, unquoted identifier
a
is in lower-case butB
is in upper-case.select a, B from foo
In this more complicated example, there are a mix of capitalisations in both reference and aliases of columns and tables. That inconsistency is acceptable when those identifiers are quoted, but not when unquoted.
select col_1 + Col_2 as COL_3, "COL_4" as Col_5 from Foo as BAR
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; --- ...or for comparison with our more complex example, this too: select col_1 + col_2 as col_3, "COL_4" as col_5 from foo as bar
- 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, snake_case, and camelCase. This is separate fromcapitalisation_policy
as it should not be applied to keywords.Camel, Pascal, and Snake will never be inferred when the policy is setto consistent. This is because snake can cause destructive changes tothe identifier, and unlinted code is too easily mistaken for camel and pascal. If, when set to consistent, no consistent case is found, it willdefault to upper. Must be one of['consistent', 'upper', 'lower', 'pascal', 'capitalise', 'snake', 'camel']
.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
andfalse
are in lower-case whereasTRUE
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 caseselect 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, snake_case, and camelCase. This is separate fromcapitalisation_policy
as it should not be applied to keywords.Camel, Pascal, and Snake will never be inferred when the policy is setto consistent. This is because snake can cause destructive changes tothe identifier, and unlinted code is too easily mistaken for camel and pascal. If, when set to consistent, no consistent case is found, it willdefault to upper. Must be one of['consistent', 'upper', 'lower', 'pascal', 'capitalise', 'snake', 'camel']
.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
andunsigned
are in lower-case whereasVARCHAR
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 toconsistent
. 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 ofIFNULL
orNVL
.¶This rule is
sqlfluff fix
compatible.Name:
convention.coalesce
Aliases:
L060
Groups:
all
,convention
Anti-pattern
IFNULL
orNVL
are used to fillNULL
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 supportIFNULL
and BigQuery doesn’t supportNVL
. 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
andprefer_count_0
are set to true thenprefer_count_1
has precedence.
COUNT(*)
,COUNT(1)
, and evenCOUNT(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)
orCOUNT(0)
overCOUNT(*)
, 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 configprefer_count_1
, orprefer_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 forNULL
values.SELECT a FROM foo WHERE a = NULL
Best practice
Use
IS
orIS NOT
to check forNULL
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 ofRIGHT 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
overBOOLEAN
and there is no existing rule to enforce this. Until such a rule is written, we can addBOOLEAN
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 todeprecated_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 toconsistent
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 theforce_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 toconsistent
(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¶
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 tospace
. If it had instead been set totab
, 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
orleading
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
orleading
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 tosingle
. 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, configurewildcard_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 asSELECT
.¶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 variablesa
andb
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¶
Column references should be qualified consistently in single table statements.¶
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 reference to
b
is qualified.SELECT a, foo.b FROM foo
Best practice
Either all column references should be qualified, or all unqualified.
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. This rule applies to both column references and their aliases. The default (safe) behaviour is designed not to unexpectedly corrupt SQL. That means the circumstances in which quotes can be safely removed depends on the current dialect would resolve the unquoted variant of the identifier (see below for examples).Additionally this rule may be configured to a more aggressive setting by setting
case_sensitive
toFalse
, in which case quotes will be removed regardless of the casing of the contained identifier. Any identifiers which contain special characters, spaces or keywords will still be left quoted. This setting is more appropriate for projects or teams where there is more control over the inputs and outputs of queries, and where it’s more viable to institute rules such as enforcing that all identifiers are the default casing (and therefore meaning that using quotes to change the case of identifiers is unnecessary).Dialect group
✅ Example where quotes are safe to remove.
⚠️ Examples where quotes are not safe to remove.
Natively
UPPERCASE
dialects e.g. Snowflake, BigQuery, TSQL & Oracle.Identifiers which, without quotes, would resolve to the default casing of
FOO
i.e."FOO"
.Identifiers where the quotes are necessary to preserve case (e.g.
"Foo"
or"foo"
), or where the identifier contains something invalid without the quotes such as keywords or special characters e.g."SELECT"
,"With Space"
or"Special&Characters"
.Natively
lowercase
dialects e.g. Athena, Hive & PostgresIdentifiers which, without quotes, would resolve to the default casing of
foo
i.e."foo"
.Identifiers where the quotes are necessary to preserve case (e.g.
"Foo"
or"foo"
), or where the identifier contains something invalid without the quotes such as keywords or special characters e.g."SELECT"
,"With Space"
or"Special&Characters"
.Case insensitive dialects e.g. DuckDB or Apache Spark SQL
Any identifiers which are valid without quotes: e.g.
"FOO"
,"foo"
,"Foo"
,"fOo"
,FOO
andfoo
would all resolve to the same object.Identifiers which contain something invalid without the quotes such as keywords or special characters e.g.
"SELECT"
,"With Space"
or"Special&Characters"
.This rule is closely associated with (and constrained by the same above factors) as
aliasing.self_alias.column
(AL09
).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
case_sensitive
: IfFalse
, comparison is done case in-sensitively. Defaults toTrue
. 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
: IfTrue
, requires every identifier to be quoted. Defaults toFalse
. Must be one of[True, False]
.prefer_quoted_keywords
: IfTrue
, requires every keyword used as an identifier to be quoted. Defaults toFalse
. Must be one of[True, False]
.
Anti-pattern
In this example, valid unquoted identifiers, that are not also reserved keywords, are needlessly quoted.
SELECT "foo" as "bar"; -- For lowercase dialects like Postgres SELECT "FOO" as "BAR"; -- For uppercase dialects like Snowflake
Best practice
Use unquoted identifiers where possible.
SELECT foo as bar; -- For lowercase dialects like Postgres SELECT FOO as BAR; -- For uppercase dialects like Snowflake -- Note that where the case of the quoted identifier requires -- the quotes to remain, or where the identifier cannot be -- unquoted because it would be invalid to do so, the quotes -- may remain. For example: SELECT "Case_Sensitive_Identifier" as is_allowed, "Identifier with spaces or speci@l characters" as this_too, "SELECT" as also_reserved_words FROM "My Table With Spaces"
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 withinCOALESCE
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 inELSE
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
’sELSE
is an unnecessary, nestedCASE
.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 withinJOIN
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 complexityselect *, 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 theDISTINCT
(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 toearlier
. 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 toearlier
.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