Aliasing Rules

This section contains 10 rule(s) related to aliasing.

Rule Summary

Code	Name	Aliases	Fix Compatible
AL01	aliasing.table	L011	✅
AL02	aliasing.column	L012	✅
AL03	aliasing.expression	L013	❌
AL04	aliasing.unique.table	L020	❌
AL05	aliasing.unused	L025	✅
AL06	aliasing.length	L066	❌
AL07	aliasing.forbid	L031	✅
AL08	aliasing.unique.column	-	❌
AL09	aliasing.self_alias.column	-	✅
AL10	aliasing.required	-	❌

---

AL01: aliasing.table

Implicit/explicit aliasing of table.

Property	Value
Name	aliasing.table
Aliases	L011
Groups	all, aliasing
Auto-fixable	Yes ✅

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

Configuration

Option	Description
aliasing	Should alias have an explicit AS or is implicit aliasing required? Must be one of ['implicit', 'explicit'].

---

AL02: aliasing.column

Implicit/explicit aliasing of columns.

Property	Value
Name	aliasing.column
Aliases	L012
Groups	all, core, aliasing
Auto-fixable	Yes ✅

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

Configuration

Option	Description
aliasing	Should alias have an explicit AS or is implicit aliasing required? Must be one of ['implicit', 'explicit'].

---

AL03: aliasing.expression

Column expression without alias. Use explicit AS clause.

Property	Value
Name	aliasing.expression
Aliases	L013
Groups	all, core, aliasing
Auto-fixable	No ❌

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

Configuration

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

---

AL04: aliasing.unique.table

Table aliases should be unique within each clause.

Property	Value
Name	aliasing.unique.table
Aliases	L020
Groups	all, core, aliasing, aliasing.unique
Auto-fixable	No ❌

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

---

AL05: aliasing.unused

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

Property	Value
Name	aliasing.unused
Aliases	L025
Groups	all, core, aliasing
Auto-fixable	Yes ✅

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

Configuration

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

---

AL06: aliasing.length

Enforce table alias lengths in from clauses and join conditions.

Property	Value
Name	aliasing.length
Aliases	L066
Groups	all, core, aliasing
Auto-fixable	No ❌

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

Configuration

Option	Description
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).

---

AL07: aliasing.forbid

Avoid table aliases in from clauses and join conditions.

Property	Value
Name	aliasing.forbid
Aliases	L031
Groups	all, aliasing
Auto-fixable	Yes ✅

NOTE

This rule was taken from the dbt Style Guide https://github.com/dbt-labs/corp/blob/main/dbt_style_guide.md 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

Configuration

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

---

AL08: aliasing.unique.column

Column aliases should be unique within each clause.

Property	Value
Name	aliasing.unique.column
Groups	all, core, aliasing, aliasing.unique
Auto-fixable	No ❌

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;

---

AL09: aliasing.self_alias.column

Column aliases should not alias to itself, i.e. self-alias.

Property	Value
Name	aliasing.self_alias.column
Groups	all, core, aliasing
Auto-fixable	Yes ✅

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_dialect_ref 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 ruleselection and ruleconfig 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;

---

AL10: aliasing.required

Derived tables must have an alias.

Property	Value
Name	aliasing.required
Groups	all, core, aliasing
Auto-fixable	No ❌

A derived table (subquery in a FROM clause) without an alias will cause a syntax error in most SQL dialects including MySQL, PostgreSQL, and T-SQL.

Anti-pattern

A subquery in a FROM clause without an alias.

SELECT *
FROM (
    SELECT 1 AS a
)

Best practice

Add an alias to the derived table.

SELECT *
FROM (
    SELECT 1 AS a
) AS derived

---