2

Traditionally in SQL, the main clauses are evaluated as:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

Among other things, it explains why you can’t use alias from the SELECT clause in other clauses such as WHERE and GROUP BY, since they aren’t processed till later; it also explains why you can use them in the ORDER BY clause.

Except for SQLite. Aliases can be used in the rest of the clauses, which suggests the the above evaluation doesn’t hold true in SQLite. Instead it appears to be:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

I can’t find any mention, let alone confirmation, anywhere in the documentation.

Is the alternative evaluation correct, and where is this documented?

2 Answers 2

1

As specified in the docs:

Generating the results of a simple SELECT statement is presented as a four step process in the description below:

FROM clause processing: The input data for the simple SELECT is determined. The input data is either implicitly a single row with 0 columns (if there is no FROM clause) or is determined by the FROM clause.

WHERE clause processing: The input data is filtered using the WHERE clause expression.

GROUP BY, HAVING and result-column expression processing: The set of result rows is computed by aggregating the data according to any GROUP BY clause and calculating the result-set expressions for the rows of the filtered input dataset.

DISTINCT/ALL keyword processing: If the query is a "SELECT DISTINCT" query, duplicate rows are removed from the set of result rows.

So the actual order seems to be:

FROM
WHERE
GROUP BY / HAVING / SELECT (all evaluated in the same step)
DISTINCT
ORDER BY

Nevertheless, apparently Sqlite parses the SELECT expressions earlier, before evaluating the expressions, probably to allow optimizations of the query plan, and allows referencing the aliases also in the WHERE conditions.

1

This is a complexity of some SQL implementations. It's not actually unique to SQLite, although as you say it can be difficult to find documentation.

The relational-algebraic evaluation is always in the standard order.

However, scalar expressions or column aliases can be defined in the select-clause, and then those aliases used in the clauses which precede the select-clause in the standard evaluation order (but which follow the select-clause textually).

It is possible to do this by pre-processing the select-clause, and substituting the aliased expression/column into the other clauses wherever the alias is found, before proceeding to actually evaluate anything.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.