Inconsistent column references in GROUP BY/ORDER BY SQL-L054
Style
Minor
This error occurs when some of the columns in GROUP BY/ORDER BY clauses are prefixed with table names or aliases, while others are not.
SELECT t1.col1, t2.col2
FROM table1 AS t1
JOIN table2 AS t2 ON t1.id = t2.id
GROUP BY col1, t2.col2;
In this query, col1 is unqualified, while t2.col2 is qualified with an alias. This can make the query unclear and inconsistent. SQL lint will suggest using either fully qualified or unqualified column names throughout the query.
Bad practice
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;
Recommended
-- 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;