Structure Rules

This section contains 12 rule(s) related to structure.

Rule Summary

Code	Name	Aliases	Fix Compatible
ST01	structure.else_null	L035	✅
ST02	structure.simple_case	L043	✅
ST03	structure.unused_cte	L045	❌
ST04	structure.nested_case	L058	✅
ST05	structure.subquery	L042	✅
ST06	structure.column_order	L034	✅
ST07	structure.using	L032	✅
ST08	structure.distinct	L015	✅
ST09	structure.join_condition_order	-	✅
ST10	structure.constant_expression	-	❌
ST11	structure.unused_join	-	❌
ST12	structure.consecutive_semicolons	-	✅

---

ST01: structure.else_null

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

Property	Value
Name	structure.else_null
Aliases	L035
Groups	all, structure
Auto-fixable	Yes ✅

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

---

ST02: structure.simple_case

Unnecessary CASE statement.

Property	Value
Name	structure.simple_case
Aliases	L043
Groups	all, structure
Auto-fixable	Yes ✅

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

---

ST03: structure.unused_cte

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

Property	Value
Name	structure.unused_cte
Aliases	L045
Groups	all, core, structure
Auto-fixable	No ❌

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

---

ST04: structure.nested_case

Nested CASE statement in ELSE clause could be flattened.

Property	Value
Name	structure.nested_case
Aliases	L058
Groups	all, structure
Auto-fixable	Yes ✅

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

---

ST05: structure.subquery

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

Property	Value
Name	structure.subquery
Aliases	L042
Groups	all, structure
Auto-fixable	Yes ✅

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)

Configuration

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

---

ST06: structure.column_order

Select wildcards then simple targets before calculations and aggregates.

Property	Value
Name	structure.column_order
Aliases	L034
Groups	all, structure
Auto-fixable	Yes ✅

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

---

ST07: structure.using

Prefer specifying join keys instead of using USING.

Property	Value
Name	structure.using
Aliases	L032
Groups	all, structure
Auto-fixable	Yes ✅

NOTE

This rule was originally taken from the dbt Style Guide <https://github.com/dbt-labs/corp/blob/ main/dbt_style_guide.md> which notes that:

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

In fact dbt removed it from their style guide in February 2022 https://github.com/dbt-labs/corp/pull/58. 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

---

ST08: structure.distinct

DISTINCT used with parentheses.

Property	Value
Name	structure.distinct
Aliases	L015
Groups	all, structure, core
Auto-fixable	Yes ✅

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

---

ST09: structure.join_condition_order

Joins should list the table referenced earlier/later first.

Property	Value
Name	structure.join_condition_order
Groups	all, structure
Auto-fixable	Yes ✅

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

Configuration

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

---

ST10: structure.constant_expression

Redundant constant expression.

Property	Value
Name	structure.constant_expression
Groups	all, structure
Auto-fixable	No ❌

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

---

ST11: structure.unused_join

Joined table not referenced in query.

Property	Value
Name	structure.unused_join
Groups	all, structure
Auto-fixable	No ❌

This rule will check if any tables are referenced in the FROM or JOIN clause of a SELECT statement where no columns from that table are referenced in any of the other clauses. Because some types of joins 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 an unused table is a mistake, but it doesn't know whether the mistake was due to the join or the failure to use the table.

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: ST11 at the end of the line.

.. note:

To avoid sticky situations with casing and quoting in different dialects this rule uses case-insensitive comparison. That means if you have two tables with the same name, but different cases (and you're really sure that's a good idea!), then this rule may not detect if one of them is unused.

    **Groups**: `all`, `structure`

---

ST12: structure.consecutive_semicolons

Consecutive semicolons detected.

Property	Value
Name	structure.consecutive_semicolons
Groups	all, structure
Auto-fixable	Yes ✅

This rule flags runs of two or more semicolons (optionally with intervening whitespace/newlines) which usually indicate an empty statement or an accidental duplicate terminator.

Anti-pattern

SELECT 1;;
;;SELECT 2;

Best practice

Collapse duplicate semicolons unless intentionally separating batches.

SELECT 1;
SELECT 2;

---