Sqlite name and use temporary expression

Question

I'm trying to understand this code from: Standard Deviation for SQLite.

create table t (row int);
insert into t values (1),(2),(3);
SELECT AVG((t.row - sub.a) * (t.row - sub.a)) as var from t, 
    (SELECT AVG(row) AS a FROM t) AS sub;
0.666666666666667

It computes the variance of the given values (I'm aware it doesn't compute the unbiased estimator, this is not at issue).

I am having a hard time searching for "Sqlite AS" on search engines as you might imagine... please suggest better search terms if you know any. In any case, how is this expression parsed? I'm not familiar with putting commas between select statements like

SELECT ..., SELECT ...;

In order for the the expression to make sense, sub.a must be defined before the first select statement can be executed. Does this mean that Sqlite is parsing these statements right-to-left? Or does it parse left-to-right and then realize that the second one needs to be evaluated first? I'm also confused about why sub does not appear printed. Is the results of a

SELECT ..., SELECT ...;

only the result of the first select statement?

It's an old obsolete syntax for joining tables that just won't die. — Shawn, Feb 04 '19 at 17:34
`SELECT foo FROM a, b` is pretty much the same as `SELECT foo FROM a JOIN b`. b in your case is a sub query not a raw table. — Shawn, Feb 04 '19 at 17:36

score 1 · Accepted Answer · answered Feb 04 '19 at 17:52

Would it help to see the code written this way?

SELECT AVG((t.row - sub.a) * (t.row - sub.a)) as var
FROM t CROSS JOIN
     (SELECT AVG(row) AS a FROM t) sub;

The , is the CROSS JOIN. , is -- by SQL standards -- archaic syntax. The explicit JOIN has been around for decades and is highly recommended.

AS is used to define aliases, but is optional. I use it only for column aliases -- that is, giving an expression a name in a SELECT. It is also use for giving tables/subqueries names in the FROM clause.

Sqlite name and use temporary expression

1 Answers1