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.
This rule is
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.
This rule will fail if a single section of whitespace contains both tabs and spaces.
This rule is
sqlfluff fix
compatible.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 ofrange(0, 100)
.
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.
This rule is
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 ofrange(0, 100)
.
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.
This rule is
sqlfluff fix
compatible.Note
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.
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 ofrange(0, 100)
.
Anti-pattern
Using tabs instead of spaces when
indent_unit
config set tospace
(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.
Unless it’s an indent. Trailing/leading commas are dealt with in a different rule.
This rule is
sqlfluff fix
compatible.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.
This rule is
sqlfluff fix
compatible.Anti-pattern
In this example, there is a space missing 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.
This rule is
sqlfluff fix
compatible.Configuration
operator_new_lines
: Should operator be placed before or after newlines? Must be one of['before', 'after']
.
Anti-pattern
In this example, if
operator_new_lines = after
(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
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.
This rule is
sqlfluff fix
compatible.Anti-pattern
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. The
•
character represents a space.SELECT * FROM foo WHERE a IN ('plop',•'zoo')
- class Rule_L009(code, description, **kwargs)¶
Files must end with a single trailing newline.
This rule is
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.
This rule is
sqlfluff fix
compatible.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.
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
- class Rule_L011(code, description, **kwargs)¶
Implicit/explicit aliasing of table.
Aliasing of table to follow preference (requiring an explicit
AS
is the default).This rule is
sqlfluff fix
compatible.Configuration
aliasing
: Should alias have an explict 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
- class Rule_L012(code, description, **kwargs)¶
Implicit/explicit aliasing of columns.
Aliasing of columns to follow preference (explicit using an
AS
clause is default).Configuration
aliasing
: Should alias have an explict 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
- 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.
This rule is
sqlfluff fix
compatible.Configuration
extended_capitalisation_policy
: The capitalisation policy to enforce, extended with PascalCase. This is separate fromcapitalisation_policy
as it should not be applied to keywords. Must be one of['consistent', 'upper', 'lower', 'pascal', 'capitalise']
.ignore_words
: Comma separated list of words to ignore from rule.unquoted_identifiers_policy
: Types of unquoted identifiers to flag violations for. Must be one of['all', 'aliases', 'column_aliases']
.
Anti-pattern
In this example, unquoted identifier
a
is in lower-case butB
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.This rule is
sqlfluff fix
compatible.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
- class Rule_L016(code, description, **kwargs)¶
Line is too long.
This rule is
sqlfluff fix
compatible.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]
.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 ofrange(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 ofrange(0, 100)
.
- class Rule_L017(code, description, **kwargs)¶
Function name not immediately followed by parenthesis.
This rule is
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 withWITH
keyword.This rule is
sqlfluff fix
compatible.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 ofrange(0, 100)
.
Anti-pattern
The
•
character represents a space. In this example, the closing bracket is not aligned withWITH
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.
This rule is
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. 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
- class Rule_L020(code, description, **kwargs)¶
Table aliases should be unique within each clause.
Reusing table aliases is very likely a coding error.
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
- class Rule_L021(code, description, **kwargs)¶
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.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
- class Rule_L022(code, description, **kwargs)¶
Blank line expected but not found after CTE closing bracket.
This rule is
sqlfluff fix
compatible.Configuration
comma_style
: The comma style to enforce. Must be one of['leading', 'trailing']
.
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
inWITH
clause.This rule is
sqlfluff fix
compatible.Anti-pattern
WITH plop AS( SELECT * FROM foo ) SELECT a FROM plop
Best practice
Add a space after
AS
, to avoid confusing it for a function. The•
character represents a space.WITH plop AS•( SELECT * FROM foo ) SELECT a FROM plop
- class Rule_L024(code, description, **kwargs)¶
Single whitespace expected after
USING
inJOIN
clause.This rule is
sqlfluff fix
compatible.Anti-pattern
SELECT b FROM foo LEFT JOIN zoo USING(a)
Best practice
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.
This rule is
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.Note
This rule is disabled by default for BigQuery, Hive, Redshift, and Spark3 due to the use of structs and lateral views which trigger false positives. It can be enabled with the
force_enable = True
flag.Configuration
force_enable
: Run this rule even for dialects where this rule is disabled by default. Must be one of[True, False]
.
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.
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
- class Rule_L028(code, description, **kwargs)¶
References should be consistent in statements with a single table.
This rule is
sqlfluff fix
compatible.Note
For BigQuery, Hive and Redshift this rule is disabled by default. This is due to historical false positives assocaited 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.
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']
.
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.
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.
Note
Note that reserved keywords cannot be used as unquoted identifiers and will cause parsing errors and so are not covered by this rule.
Configuration
ignore_words
: Comma separated list of words to ignore from rule.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.
This rule is
sqlfluff fix
compatible.Configuration
extended_capitalisation_policy
: The capitalisation policy to enforce, extended with PascalCase. This is separate fromcapitalisation_policy
as it should not be applied to keywords. Must be one of['consistent', 'upper', 'lower', 'pascal', 'capitalise']
.ignore_words
: Comma separated list of words to ignore from rule.
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.
This rule is
sqlfluff fix
compatible.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 this case this rule should be disabled.
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
- class Rule_L032(code, description, **kwargs)¶
Prefer specifying join keys instead of using
USING
.This rule is
sqlfluff fix
compatible.Note
This rule was taken from the dbt Style Guide which notes that:
Certain warehouses have inconsistencies in
USING
results (specifically Snowflake).Other users may prefer to disable this rule.
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 justUNION
.Note
This rule is only enabled for dialects that support
UNION DISTINCT
(ansi
,bigquery
,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
- class Rule_L034(code, description, **kwargs)¶
Select wildcards then simple targets before calculations and aggregates.
This rule is
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 complexityselect *, 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).This rule is
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.
This rule is
sqlfluff fix
compatible.Note
A wildcard is (
SELECT *
) is not considered a single select target so always requires a new line.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.
This rule is
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 specifyASC
orDESC
, 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.
This rule is
sqlfluff fix
compatible.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.
Configuration
select_clause_trailing_comma
: Should trailing commas within select clauses be required or forbidden? Must be one of['forbid', 'require']
.
Anti-pattern
SELECT a, b, FROM foo
Best practice
SELECT a, b FROM foo
- class Rule_L039(code, description, **kwargs)¶
Unnecessary whitespace found.
This rule is
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.
This rule is
sqlfluff fix
compatible.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.
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
- class Rule_L041(code, description, **kwargs)¶
SELECT
modifiers (e.g.DISTINCT
) must be on the same line asSELECT
.This rule is
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.
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.Note
Some dialects don’t allow CTEs, and for those dialects this rule makes no sense and should be disabled.
Configuration
forbid_subquery_in
: Which clauses should be linted for subqueries? Must be one of['join', 'from', 'both']
.
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.This rule is
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 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
- 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 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
- 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”.
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.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
- class Rule_L048(code, description, **kwargs)¶
Quoted literals should be surrounded by a single whitespace.
This rule is
sqlfluff fix
compatible.Anti-pattern
In this example, there is a space missing between the string
'foo'
and the keywordAS
.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”.
This rule is
sqlfluff fix
compatible.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
- class Rule_L050(code, description, **kwargs)¶
Files must not begin with newlines or whitespace.
This rule is
sqlfluff fix
compatible.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
- class Rule_L051(code, description, **kwargs)¶
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.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 specified without expliciting the kind of join.
SELECT foo FROM bar JOIN baz;
Best practice
Use
INNER JOIN
rather thanJOIN
.SELECT foo FROM bar INNER JOIN baz;
- class Rule_L052(code, description, **kwargs)¶
Statements must end with a semi-colon.
This rule is
sqlfluff fix
compatible.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;
- class Rule_L053(code, description, **kwargs)¶
Top-level statements should not be wrapped in brackets.
This rule is
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.Note
ORDER BY
clauses fromWINDOW
clauses are ignored by this rule.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 ofRIGHT 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]
.ignore_words
: Comma separated list of words to ignore from rule.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 inELSE
clause could be flattened.This rule is
sqlfluff fix
compatible.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
- class Rule_L059(code, description, **kwargs)¶
Unnecessary quoted identifier.
This rule will fail if the quotes used to quote an identifier are (un)necessary depending on the
force_quote_identifier
configuration.When
prefer_quoted_identifiers = False
(default behaviour), the quotes are unnecessary, except for reserved keywords and special characters in identifiers.This rule is
sqlfluff fix
compatible.Configuration
ignore_words
: Comma separated list of words to ignore from rule.prefer_quoted_identifiers
: IfTrue
, requires every identifier to be quoted. Defaults toFalse
. Must be one of[True, False]
.
Anti-pattern
In this example, a valid unquoted identifier, that is also not a reserved keyword, is needlessly quoted.
SELECT 123 as "foo"
Best practice
Use unquoted identifiers where possible.
SELECT 123 as foo
When
prefer_quoted_identifiers = True
, the quotes are always necessary, no matter if the identifier is valid, a reserved keyword, or contains special characters.Note
Note due to different quotes being used by different dialects supported by SQLFluff, and those quotes meaning different things in different contexts, this mode is not
sqlfluff fix
compatible.Anti-pattern
In this example, a valid unquoted identifier, that is also not a reserved keyword, is required to be quoted.
SELECT 123 as foo
Best practice Use quoted identifiers.
SELECT 123 as "foo" -- For ANSI, ... -- or SELECT 123 as `foo` -- For BigQuery, MySql, ...
- class Rule_L060(code, description, **kwargs)¶
Use
COALESCE
instead ofIFNULL
orNVL
.This rule is
sqlfluff fix
compatible.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;
- class Rule_L061(code, description, **kwargs)¶
Use
!=
instead of<>
for “not equal to” comparisons.This rule is
sqlfluff fix
compatible.Anti-pattern
<>
meansnot equal
but doesn’t sound like this when we say it out loud.SELECT * FROM X WHERE 1 <> 2;
Best practice
Use
!=
instead because its sounds more natural and is more common in other programming languages.SELECT * FROM X WHERE 1 != 2;
- class Rule_L062(code, description, **kwargs)¶
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: L062
for the few exceptions that still need to be in the code base for now.
Configuration
blocked_words
: Optional, comma-separated list of blocked words which should not be used in statements.
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);
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
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 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