Rules Reference¶
This page is an index of available rules which are bundled with SQLFluff.
For information on how to configure which rules are enabled for your project see Enabling and Disabling Rules.
If you just want to turn rules on or off for specific files, or specific sections of files, see Ignoring Errors & Files.
For more information on how to configure the rules which you do enable see Rule Configuration.
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.
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¶
Aliasing of table to follow preference
(requiring an explicit AS
is the default).
Anti-pattern
In this example, the alias voo
is implicit.
SELECT
voo.a
FROM foo voo
Best practice
Add AS
to make it explicit.
SELECT
voo.a
FROM foo AS voo
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']``.
Aliasing of columns to follow preference
(explicit using an AS
clause is default).
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
**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, 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
**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]``.
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
**Name**: ``aliasing.unique.table``
**Aliases**: ``L020``
**Groups**: ``all``, ``core``, ``aliasing``, ``aliasing.unique``
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
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
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
**Name**: ``aliasing.length``
**Aliases**: ``L066``
**Groups**: ``all``, ``core``, ``aliasing``
**Configuration**
* ``max_alias_length``: The maximum length of an alias to allow without raising a violation. Must be one of ``range(0, 1000)``.
* ``min_alias_length``: The minimum length of an alias to allow without raising a violation. Must be one of ``range(0, 1000)``.
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, and c
is used for
customers
table.
SELECT
COUNT(o.customer_id) as order_amount,
c.name
FROM orders as o
JOIN customers as c on o.id = c.user_id
Best practice
Avoid aliases.
SELECT
COUNT(orders.customer_id) as order_amount,
customers.name
FROM orders
JOIN customers on orders.id = customers.user_id
-- Self-join will not raise issue
SELECT
table1.a,
table_alias.b,
FROM
table1
LEFT JOIN table1 AS table_alias ON
table1.foreign_key = table_alias.foreign_key
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]``.
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.
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;
**Name**: ``aliasing.unique.column``
**Groups**: ``all``, ``core``, ``aliasing``, ``aliasing.unique``
- 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. This rule only applies
when aliasing to an exact copy of the column reference (e.g.
foo as foo
or "BAR" as "BAR"
, see note below on
more complex examples). Aliases which effectively change the casing of
an identifier are still allowed.
Note
This rule works in conjunction with references.quoting
(RF06
) and capitalisation.identifiers
(CP02
) to handle self aliases with mixed quoting
and casing. In the situation that these two rules are not enabled
then this rule will only fix the strict case where the quoting
and casing of the alias and reference are the same.
If those two rules are enabled, the fixes applied may result in a situation where this rule can kick in as a secondary effect. For example this Snowflake query:
-- Original Query. AL09 will not trigger because casing and
-- quoting are different. RF06 will however fix the unnecessary
-- quoting of "COL".
SELECT "COL" AS col FROM table;
-- After RF06, the query will look like this, at which point
-- CP02 will see the inconsistent capitalisation. Depending
-- on the configuration it will change one of the identifiers.
-- Let's assume the default configuration of "consistent".
SELECT COL AS col FROM table;
-- After CP02, the alias and the reference will be the same
-- and at this point AL09 can take over and remove the alias.
SELECT COL AS COL FROM table;
-- ..resulting in:
SELECT COL FROM table;
This interdependence between the rules, and the configuration options offered by each one means a variety of outcomes can be achieved by enabling and disabling each one. See Enabling and Disabling Rules and Rule Configuration for more details.
Anti-pattern
Aliasing the column to itself, where not necessary for changing the case of an identifier.
SELECT
col AS col,
"Col" AS "Col",
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, unless used to effectively change the case of the identifier.
SELECT
col,
"Col"
COL,
FROM table;
-- Re-casing aliasing is still allowed where necessary, i.e.
SELECT
col as "Col",
"col" as "COL"
FROM table;
This rule is ``sqlfluff fix`` compatible.
**Name**: ``aliasing.self_alias.column``
**Groups**: ``all``, ``core``, ``aliasing``
Ambiguous bundle¶
When using GROUP BY
a DISTINCT` clause should not be necessary as every
non-distinct SELECT
clause must be included in the GROUP BY
clause.
Anti-pattern
DISTINCT
and GROUP BY
are conflicting.
SELECT DISTINCT
a
FROM foo
GROUP BY a
Best practice
Remove DISTINCT
or GROUP BY
. In our case, removing GROUP BY
is better.
SELECT DISTINCT
a
FROM foo
**Name**: ``ambiguous.distinct``
**Aliases**: ``L021``
**Groups**: ``all``, ``core``, ``ambiguous``
Note
This rule is only enabled for dialects that support UNION
and
UNION DISTINCT
(ansi
, bigquery
, clickhouse
,
databricks
, db2
, hive
, mysql
, redshift
,
snowflake
, and trino
).
Anti-pattern
In this example, UNION DISTINCT
should be preferred over UNION
, because
explicit is better than implicit.
SELECT a, b FROM table_1
UNION
SELECT a, b FROM table_2
Best practice
Specify DISTINCT
or ALL
after UNION
(note that DISTINCT
is the
default behavior).
SELECT a, b FROM table_1
UNION DISTINCT
SELECT a, b FROM table_2
This rule is ``sqlfluff fix`` compatible.
**Name**: ``ambiguous.union``
**Aliases**: ``L033``
**Groups**: ``all``, ``core``, ``ambiguous``
Anti-pattern
SELECT
a, b
FROM foo
ORDER BY a, b DESC
Best practice
If any columns in the ORDER BY
clause specify ASC
or DESC
, they should
all do so.
SELECT
a, b
FROM foo
ORDER BY a ASC, b DESC
This rule is ``sqlfluff fix`` compatible.
**Name**: ``ambiguous.order_by``
**Aliases**: ``L037``
**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
**Name**: ``ambiguous.column_count``
**Aliases**: ``L044``
**Groups**: ``all``, ``ambiguous``
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.
Anti-pattern
A join is used without specifying the kind of join.
SELECT
foo
FROM bar
JOIN baz;
Best practice
Use INNER JOIN
rather than JOIN
.
SELECT
foo
FROM bar
INNER JOIN baz;
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']``.
- Code AM06¶
- Rule ambiguous.column_references¶
Inconsistent column references in
GROUP BY/ORDER BY
clauses.¶
Note
ORDER BY
clauses from WINDOW
clauses are ignored by this rule.
Anti-pattern
A mix of implicit and explicit column references are used in a GROUP BY
clause.
SELECT
foo,
bar,
sum(baz) AS sum_value
FROM fake_table
GROUP BY
foo, 2;
-- The same also applies to column
-- references in ORDER BY clauses.
SELECT
foo,
bar
FROM fake_table
ORDER BY
1, bar;
Best practice
Reference all GROUP BY
/ORDER BY
columns either by name or by position.
-- GROUP BY: Explicit
SELECT
foo,
bar,
sum(baz) AS sum_value
FROM fake_table
GROUP BY
foo, bar;
-- ORDER BY: Explicit
SELECT
foo,
bar
FROM fake_table
ORDER BY
foo, bar;
-- GROUP BY: Implicit
SELECT
foo,
bar,
sum(baz) AS sum_value
FROM fake_table
GROUP BY
1, 2;
-- ORDER BY: Implicit
SELECT
foo,
bar
FROM fake_table
ORDER BY
1, 2;
**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']``.
- Code AM07¶
- Rule ambiguous.set_columns¶
Queries within set query produce different numbers of columns.¶
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
**Name**: ``ambiguous.set_columns``
**Aliases**: ``L068``
**Groups**: ``all``, ``ambiguous``
Anti-pattern
Cross joins are valid, but rare in the wild - and more often created by mistake than on purpose. This rule catches situations where a cross join has been specified, but not explicitly and so the risk of a mistaken cross join is highly likely.
SELECT
foo
FROM bar
JOIN baz;
Best practice
Use CROSS JOIN.
SELECT
foo
FROM bar
CROSS JOIN baz;
This rule is ``sqlfluff fix`` compatible.
**Name**: ``ambiguous.join_condition``
**Groups**: ``all``, ``ambiguous``
Capitalisation bundle¶
Anti-pattern
In this example, select
is in lower-case whereas FROM
is in upper-case.
select
a
FROM foo
Best practice
Make all keywords either in upper-case or in lower-case.
SELECT
a
FROM foo
-- Also good
select
a
from foo
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 ``$``.
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).
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, Trino 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 but
B
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
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 from ``capitalisation_policy`` as it should not be applied to keywords.Camel, Pascal, and Snake will never be inferred when the policy is set to consistent. This is because snake can cause destructive changes to the identifier, and unlinted code is too easily mistaken for camel and pascal. If, when set to consistent, no consistent case is found, it will default 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', 'table_aliases']``.
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
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 from ``capitalisation_policy`` as it should not be applied to keywords.Camel, Pascal, and Snake will never be inferred when the policy is set to consistent. This is because snake can cause destructive changes to the identifier, and unlinted code is too easily mistaken for camel and pascal. If, when set to consistent, no consistent case is found, it will default 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, null
and false
are in lower-case whereas TRUE
is in
upper-case.
select
a,
null,
TRUE,
false
from foo
Best practice
Ensure all literal null
/true
/false
literals are consistently
upper or lower case
select
a,
NULL,
TRUE,
FALSE
from foo
-- Also good
select
a,
null,
true,
false
from foo
This rule is ``sqlfluff fix`` compatible.
**Name**: ``capitalisation.literals``
**Aliases**: ``L040``
**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, int
and unsigned
are in lower-case whereas
VARCHAR
is in upper-case.
CREATE TABLE t (
a int unsigned,
b VARCHAR(15)
);
Best practice
Ensure all datatypes are consistently upper or lower case
CREATE TABLE t (
a INT UNSIGNED,
b VARCHAR(15)
);
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 from ``capitalisation_policy`` as it should not be applied to keywords.Camel, Pascal, and Snake will never be inferred when the policy is set to consistent. This is because snake can cause destructive changes to the identifier, and unlinted code is too easily mistaken for camel and pascal. If, when set to consistent, no consistent case is found, it will default 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 ``$``.
Convention bundle¶
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;
This rule is ``sqlfluff fix`` compatible.
**Name**: ``convention.not_equal``
**Aliases**: ``L061``
**Groups**: ``all``, ``convention``
**Configuration**
* ``preferred_not_equal_style``: The style for using not equal to operator. Defaults to ``consistent``. Must be one of ``['consistent', 'c_style', 'ansi']``.
Anti-pattern
IFNULL
or NVL
are used to fill NULL
values.
SELECT ifnull(foo, 0) AS bar,
FROM baz;
SELECT nvl(foo, 0) AS bar,
FROM baz;
Best practice
Use COALESCE
instead.
COALESCE
is universally supported,
whereas Redshift doesn’t support IFNULL
and BigQuery doesn’t support NVL
.
Additionally, COALESCE
is more flexible
and accepts an arbitrary number of arguments.
SELECT coalesce(foo, 0) AS bar,
FROM baz;
This rule is ``sqlfluff fix`` compatible.
**Name**: ``convention.coalesce``
**Aliases**: ``L060``
**Groups**: ``all``, ``convention``
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
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:
If both
prefer_count_1
andprefer_count_0
are set to true thenprefer_count_1
has precedence.
COUNT(*)
, COUNT(1)
, and even COUNT(0)
are equivalent syntaxes
in many SQL engines due to optimizers interpreting these instructions as
“count number of rows in result”.
The ANSI-92 spec mentions the COUNT(*)
syntax specifically as
having a special meaning:
If COUNT(*) is specified, then the result is the cardinality of T.
So by default, SQLFluff enforces the consistent use of COUNT(*)
.
If the SQL engine you work with, or your team, prefers COUNT(1)
or
COUNT(0)
over COUNT(*)
, you can configure this rule to consistently
enforce your preference.
Anti-pattern
select
count(1)
from table_a
Best practice
Use count(*)
unless specified otherwise by config prefer_count_1
,
or prefer_count_0
as preferred.
select
count(*)
from table_a
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
In this example, the =
operator is used to check for NULL
values.
SELECT
a
FROM foo
WHERE a = NULL
Best practice
Use IS
or IS NOT
to check for NULL
values.
SELECT
a
FROM foo
WHERE a IS NULL
This rule is ``sqlfluff fix`` compatible.
**Name**: ``convention.is_null``
**Aliases**: ``L049``
**Groups**: ``all``, ``core``, ``convention``
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;
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]``.
- Code CV07¶
- Rule convention.statement_brackets¶
Top-level statements should not be wrapped in brackets.¶
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)
This rule is ``sqlfluff fix`` compatible.
**Name**: ``convention.statement_brackets``
**Aliases**: ``L053``
**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;
**Name**: ``convention.left_join``
**Aliases**: ``L055``
**Groups**: ``all``, ``convention``
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.
Anti-pattern
If the blocked_words
config is set to deprecated_table,bool
then the
following will flag:
SELECT * FROM deprecated_table WHERE 1 = 1;
CREATE TABLE myschema.t1 (a BOOL);
Best practice
Do not used any blocked words:
SELECT * FROM another_table WHERE 1 = 1;
CREATE TABLE myschema.t1 (a BOOLEAN);
**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.
- 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.
Note
This rule only checks quoted literals and not quoted identifiers as they often cannot interchange single and double quotes
This rule is only enabled for dialects that allow single and double quotes for
quoted literals
(currently bigquery
, databricks
, hive
, mysql
, sparksql
).
It can be enabled for other dialects with the force_enable = True
flag.
Anti-pattern
select
"abc",
'abc',
"\"",
"abc" = 'abc'
from foo
Best practice
Ensure all quoted literals use preferred quotes, unless escaping can be reduced by using alternate quotes.
select
"abc",
"abc",
'"',
"abc" = "abc"
from foo
This rule is ``sqlfluff fix`` compatible.
**Name**: ``convention.quoted_literals``
**Aliases**: ``L064``
**Groups**: ``all``, ``convention``
**Configuration**
* ``force_enable``: Run this rule even for dialects where this rule is disabled by default. Must be one of ``[True, False]``.
* ``preferred_quoted_literal_style``: Preferred quoting style to use for the quoted literals. If set to ``consistent`` quoting style is derived from the first quoted literal in the file. Must be one of ``['consistent', 'single_quotes', 'double_quotes']``.
Note
This is only compatible with 2-arguments CONVERT as some dialects allow an optional 3rd argument e.g TSQL, which cannot be rewritten into CAST. This rule is disabled by default for Teradata because it supports different type casting apart from CONVERT and :: e.g DATE ‘2007-01-01’, ‘9999-12-31’ (DATE).
Anti-pattern
Using mixture of CONVERT, :: and CAST when preferred_type_casting_style
config is set to consistent
(default).
SELECT
CONVERT(int, 1) AS bar,
100::int::text,
CAST(10 AS text) AS coo
FROM foo;
Best practice
Use consistent type casting style.
SELECT
CAST(1 AS int) AS bar,
CAST(CAST(100 AS int) AS text),
CAST(10 AS text) AS coo
FROM foo;
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']``.
Anti-pattern
Using WHERE clause for join conditions.
SELECT
foo.a
, bar.b
FROM foo
JOIN bar
WHERE foo.x = bar.y;
Best practice
Use JOIN ON clause for join condition.
SELECT
foo.a
, bar.b
FROM foo
JOIN bar
ON foo.x = bar.y;
This rule is ``sqlfluff fix`` compatible.
**Name**: ``convention.join_condition``
**Groups**: ``all``, ``convention``
Jinja bundle¶
This rule is only active if the jinja
templater (or one of it’s
subclasses, like the dbt
templater) are used for the current file.
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')
}};
This rule is ``sqlfluff fix`` compatible.
**Name**: ``jinja.padding``
**Aliases**: ``L046``
**Groups**: ``all``, ``core``, ``jinja``
Layout bundle¶
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.
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)
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
The •
character represents a space and the →
character represents a tab.
In this example, the third line contains five spaces instead of four and
the second line contains two spaces and one tab.
SELECT
••→a,
•••••b
FROM foo
Best practice
Change the indentation to use a multiple of four spaces. This example also
assumes that the indent_unit
config value is set to space
. If it
had instead been set to tab
, then the indents would be tabs instead.
SELECT
••••a,
••••b
FROM foo
This rule is ``sqlfluff fix`` compatible.
**Name**: ``layout.indent``
**Aliases**: ``L002``, ``L003``, ``L004``
**Groups**: ``all``, ``core``, ``layout``
- Code LT03¶
- Rule layout.operators¶
Operators should follow a standard for being before/after newlines.¶
The configuration for whether operators should be trailing
or
leading
is part of Configuring Layout. The default configuration is:
[sqlfluff:layout:type:binary_operator]
line_position = leading
[sqlfluff:layout:type:comparison_operator]
line_position = leading
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
This rule is ``sqlfluff fix`` compatible.
**Name**: ``layout.operators``
**Aliases**: ``L007``
**Groups**: ``all``, ``layout``
The configuration for whether operators should be trailing
or
leading
is part of Configuring Layout. The default configuration is:
[sqlfluff:layout:type:comma]
line_position = trailing
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
This rule is ``sqlfluff fix`` compatible.
**Name**: ``layout.commas``
**Aliases**: ``L019``
**Groups**: ``all``, ``layout``
- 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]
.
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
This rule is ``sqlfluff fix`` compatible.
**Name**: ``layout.functions``
**Aliases**: ``L017``
**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
This rule is ``sqlfluff fix`` compatible.
**Name**: ``layout.cte_bracket``
**Aliases**: ``L018``
**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
This rule is ``sqlfluff fix`` compatible.
**Name**: ``layout.cte_newline``
**Aliases**: ``L022``
**Groups**: ``all``, ``core``, ``layout``
- Code LT09¶
- Rule layout.select_targets¶
Select targets should be on a new line unless there is only one select target.¶
Note
By default, a wildcard (e.g. SELECT *
) is considered a single select target.
If you want it to be treated as multiple select targets, configure
wildcard_policy = multiple
.
Anti-pattern
Multiple select targets on the same line.
select a, b
from foo;
-- Single select target on its own line.
SELECT
a
FROM foo;
Best practice
Multiple select targets each on their own line.
select
a,
b
from foo;
-- Single select target on the same line as the ``SELECT``
-- keyword.
SELECT a
FROM foo;
-- When select targets span multiple lines, however they
-- can still be on a new line.
SELECT
SUM(
1 + SUM(
2 + 3
)
) AS col
FROM test_table;
This rule is ``sqlfluff fix`` compatible.
**Name**: ``layout.select_targets``
**Aliases**: ``L036``
**Groups**: ``all``, ``layout``
**Configuration**
* ``wildcard_policy``: Treatment of wildcards. Defaults to ``single``. Must be one of ``['single', 'multiple']``.
- Code LT10¶
- Rule layout.select_modifiers¶
SELECT
modifiers (e.g.DISTINCT
) must be on the same line asSELECT
.¶
Anti-pattern
select
distinct a,
b
from x
Best practice
select distinct
a,
b
from x
This rule is ``sqlfluff fix`` compatible.
**Name**: ``layout.select_modifiers``
**Aliases**: ``L041``
**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
This rule is ``sqlfluff fix`` compatible.
**Name**: ``layout.set_operators``
**Aliases**: ``L065``
**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
;
$
This rule is ``sqlfluff fix`` compatible.
**Name**: ``layout.end_of_file``
**Aliases**: ``L009``, ``layout.end-of-file``
**Groups**: ``all``, ``core``, ``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
This rule is ``sqlfluff fix`` compatible.
**Name**: ``layout.start_of_file``
**Aliases**: ``L050``
**Groups**: ``all``, ``layout``
- Code LT14¶
- Rule layout.keyword_newline¶
Keyword clauses should follow a standard for being before/after newlines.¶
Anti-pattern
In this example, the keyword are not at the beginning of or alone on the line.
SELECT 'a' AS col FROM tab WHERE x = 4 ORDER BY y LIMIT 5
Best practice
SELECT 'a' AS col
FROM tab
WHERE x = 4
ORDER BY y
LIMIT 5
SELECT 'a' AS col
FROM
tab
WHERE
x = 4
ORDER BY
y
LIMIT
5
This rule is ``sqlfluff fix`` compatible.
**Name**: ``layout.keyword_newline``
**Groups**: ``all``, ``layout``
References bundle¶
Note
This rule is disabled by default for Athena, BigQuery, Databricks, DuckDB, 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
**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]``.
- Code RF02¶
- Rule references.qualification¶
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 variables a
and b
are potentially ambiguous.
SELECT a, b
FROM foo
LEFT JOIN vee ON vee.a = foo.a
Best practice
Add the references.
SELECT foo.a, vee.b
FROM foo
LEFT JOIN vee ON vee.a = foo.a
**Name**: ``references.qualification``
**Aliases**: ``L027``
**Groups**: ``all``, ``references``
- Code RF03¶
- Rule references.consistent¶
Column references should be qualified consistently in single table statements.¶
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
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']``.
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.
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
**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', 'table_aliases', 'none']``.
* ``unquoted_identifiers_policy``: Types of unquoted identifiers to flag violations for. Must be one of ``['all', 'aliases', 'column_aliases', 'table_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
)
**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', 'table_aliases', 'none']``.
* ``unquoted_identifiers_policy``: Types of unquoted identifiers to flag violations for. Must be one of ``['all', 'aliases', 'column_aliases', 'table_aliases']``.
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
to False
, 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 |
Identifiers which, without quotes, would resolve to the default
casing of |
Identifiers where the quotes are necessary to preserve case
(e.g. |
Natively |
Identifiers which, without quotes, would resolve to the default
casing of |
Identifiers where the quotes are necessary to preserve case
(e.g. |
Case insensitive dialects e.g. DuckDB or Apache Spark SQL |
Any identifiers which are valid without quotes: e.g. |
Identifiers which contain something invalid without the quotes
such as keywords or special characters e.g. |
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.
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, ...
This rule is ``sqlfluff fix`` compatible.
**Name**: ``references.quoting``
**Aliases**: ``L059``
**Groups**: ``all``, ``references``
**Configuration**
* ``case_sensitive``: If ``False``, comparison is done case in-sensitively. Defaults to ``True``. Must be one of ``[True, False]``.
* ``ignore_words``: Comma separated list of words to ignore from rule.
* ``ignore_words_regex``: Words to ignore from rule if they are a partial match for the regular expression. To ignore only full matches you can use ``^`` (beginning of text) and ``$`` (end of text). Due to regular expression operator precedence, it is good practice to use parentheses around everything between ``^`` and ``$``.
* ``prefer_quoted_identifiers``: If ``True``, requires every identifier to be quoted. Defaults to ``False``. Must be one of ``[True, False]``.
* ``prefer_quoted_keywords``: If ``True``, requires every keyword used as an identifier to be quoted. Defaults to ``False``. Must be one of ``[True, False]``.
Structure bundle¶
- Code ST01¶
- Rule structure.else_null¶
Do not specify
else null
in a case when statement (redundant).¶
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
This rule is ``sqlfluff fix`` compatible.
**Name**: ``structure.else_null``
**Aliases**: ``L035``
**Groups**: ``all``, ``structure``
Anti-pattern
CASE
statement returns booleans.
select
case
when fab > 0 then true
else false
end as is_fab
from fancy_table
-- This rule can also simplify CASE statements
-- that aim to fill NULL values.
select
case
when fab is null then 0
else fab
end as fab_clean
from fancy_table
-- This also covers where the case statement
-- replaces NULL values with NULL values.
select
case
when fab is null then null
else fab
end as fab_clean
from fancy_table
Best practice
Reduce to WHEN
condition within COALESCE
function.
select
coalesce(fab > 0, false) as is_fab
from fancy_table
-- To fill NULL values.
select
coalesce(fab, 0) as fab_clean
from fancy_table
-- NULL filling NULL.
select fab as fab_clean
from fancy_table
This rule is ``sqlfluff fix`` compatible.
**Name**: ``structure.simple_case``
**Aliases**: ``L043``
**Groups**: ``all``, ``structure``
- Code ST03¶
- Rule structure.unused_cte¶
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
**Name**: ``structure.unused_cte``
**Aliases**: ``L045``
**Groups**: ``all``, ``core``, ``structure``
Anti-pattern
In this example, the outer CASE
’s ELSE
is an unnecessary, nested CASE
.
SELECT
CASE
WHEN species = 'Cat' THEN 'Meow'
ELSE
CASE
WHEN species = 'Dog' THEN 'Woof'
END
END as sound
FROM mytable
Best practice
Move the body of the inner CASE
to the end of the outer one.
SELECT
CASE
WHEN species = 'Cat' THEN 'Meow'
WHEN species = 'Dog' THEN 'Woof'
END AS sound
FROM mytable
This rule is ``sqlfluff fix`` compatible.
**Name**: ``structure.nested_case``
**Aliases**: ``L058``
**Groups**: ``all``, ``structure``
- Code ST05¶
- Rule structure.subquery¶
Join/From clauses should not contain subqueries. Use CTEs instead.¶
By default this rule is configured to allow subqueries within FROM
clauses but not within JOIN
clauses. If you prefer a stricter lint
then this is configurable.
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)
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']``.
- Code ST06¶
- Rule structure.column_order¶
Select wildcards then simple targets before calculations and aggregates.¶
Anti-pattern
select
a,
*,
row_number() over (partition by id order by date) as y,
b
from x
Best practice
Order select
targets in ascending complexity
select
*,
a,
b,
row_number() over (partition by id order by date) as y
from x
This rule is ``sqlfluff fix`` compatible.
**Name**: ``structure.column_order``
**Aliases**: ``L034``
**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
This rule is ``sqlfluff fix`` compatible.
**Name**: ``structure.using``
**Aliases**: ``L032``
**Groups**: ``all``, ``structure``
Anti-pattern
In this example, parentheses are not needed and confuse
DISTINCT
with a function. The parentheses can also be misleading
about which columns are affected by the DISTINCT
(all the columns!).
SELECT DISTINCT(a), b FROM foo
Best practice
Remove parentheses to be clear that the DISTINCT
applies to
both columns.
SELECT DISTINCT a, b FROM foo
This rule is ``sqlfluff fix`` compatible.
**Name**: ``structure.distinct``
**Aliases**: ``L015``
**Groups**: ``all``, ``structure``, ``core``
- 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.
Note
Joins in WHERE
clauses are currently not supported by this rule.
Anti-pattern
In this example, the tables that were referenced later are listed first
and the preferred_first_table_in_join_clause
configuration
is set to earlier
.
select
foo.a,
foo.b,
bar.c
from foo
left join bar
-- This subcondition does not list
-- the table referenced earlier first:
on bar.a = foo.a
-- Neither does this subcondition:
and bar.b = foo.b
Best practice
List the tables that were referenced earlier first.
select
foo.a,
foo.b,
bar.c
from foo
left join bar
on foo.a = bar.a
and foo.b = bar.b
This rule is ``sqlfluff fix`` compatible.
**Name**: ``structure.join_condition_order``
**Groups**: ``all``, ``structure``
**Configuration**
* ``preferred_first_table_in_join_clause``: Which table to list first when joining two tables. Defaults to ``earlier``. Must be one of ``['earlier', 'later']``.
Including an expression that always evaluates to
either TRUE
or FALSE
regardless of the input columns is
unnecessary and makes statements harder to read and understand.
Constant conditions are sometimes mistakes (by mistyping the column name intended), and sometimes the result of incorrect information that they are necessary in some circumstances. In the former case, they can sometimes result in a cartesian join if it was supposed to be a join condition. Given the ambiguity of intent, this rule does not suggest an automatic fix, and instead invites the user to resolve the problem manually.
Anti-pattern
SELECT *
FROM my_table
-- This following WHERE clause is redundant.
WHERE my_table.col = my_table.col
Best practice
SELECT *
FROM my_table
-- Replace with a condition that includes meaningful logic,
-- or remove the condition entirely.
WHERE my_table.col > 3
**Name**: ``structure.constant_expression``
**Groups**: ``all``, ``structure``
This rule will check if there are any tables that are referenced in the
FROM
or JOIN
clause of a SELECT
statement, but where no
columns from that table are referenced in the any of the other clauses.
Because some types of join are often used as filters, or to otherwise
control granularity without being referenced (e.g. INNER
and CROSS
),
this rule only applies to explicit OUTER
joins (i.e. LEFT
, RIGHT
and FULL
joins).
This rule relies on all of the column references in the SELECT
statement being qualified with at least the table name, and so is
designed to work alongside references.qualification
(RF02
). This is because without the knowledge of what
columns exist in each upstream table, the rule is unable to resolve
which table an unqualified column reference is pulled from.
This rule does not propose a fix, because it assumes that it an unused table is a mistake, but doesn’t know whether the mistake was the join, or the mistake was not using it.
Anti-pattern
In this example, the table bar
is included in the JOIN
clause
but not columns from it are referenced in
SELECT
foo.a,
foo.b
FROM foo
LEFT JOIN bar ON foo.a = bar.a
Best practice
Remove the join, or use the table.
SELECT foo.a, vee.b
FROM foo;
SELECT
foo.a,
foo.b,
bar.c
FROM foo
LEFT JOIN bar ON foo.a = bar.a
In the (very rare) situations that it is logically necessary to include
a table in a join clause, but not otherwise refer to it (likely for
granularity reasons, or as a stepping stone to another table), we recommend
ignoring this rule for that specific line by using -- noqa: ST10
at
the end of the line.
TSQL bundle¶
- Code TQ01¶
- Rule tsql.sp_prefix¶
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
**Name**: ``tsql.sp_prefix``
**Aliases**: ``L056``
**Groups**: ``all``, ``tsql``