1

Postgres and some online SQL engines return just one row for the SELECT:

CREATE TABLE mytable(val INTEGER);
INSERT INTO mytable VALUES (1), (2), (3), (NULL);

SELECT (SELECT SUM(t.val)) FROM mytable t;

The result is:

6

Does it correspond to the SQL spec? Which section on the spec do I need?

Should the subquery be invoked for every table record? Why the result is not several rows like:

1
2
3
null
VladS24
  • 11
  • 1
  • SQL Server also returns one row. – topsail Dec 12 '22 at 14:40
  • MySQL and SQLite too. – lemon Dec 12 '22 at 14:40
  • `SUM(t.val)` would give one value for each record only if there's a partitioning on the aggregation you're doing, why would you expect having multiple rows with no partitioning? – lemon Dec 12 '22 at 14:42
  • 1
    I imagine since SUM(t.Val) means sum [Val] in table t and then you select that - well, its one row. Where have you looked in the sql spec that would lead you to think otherwise? – topsail Dec 12 '22 at 14:42
  • 1
    The inner SELECT is useless and thus it's run as `select sum(t.val) from mytable t` –  Dec 12 '22 at 14:45
  • This is interesting. You have a nested inner query enclosed in parentheses `(SELECT SUM(t.val))` . One might think this query needs to be able to stand on it's own... that is, there is no `t` in this context, and so you should see an error. However, the database can also treat this as a correlated derived table. The "correlated" part of the name means the inner query can use (correlate to) context from the outer query. But in that situation, I'd expect the database to rerun the inner query for each row provided by the outer query. So I'm not sure what's happening here. – Joel Coehoorn Dec 12 '22 at 14:54
  • @JoelCoehoorn: you can access the alias in the FROM clause inside a (scalar) subquery in the SELECT list. So the alias `t` is absolutely available in that context. –  Dec 12 '22 at 15:47
  • @a_horse_with_no_name Yes, as a correlated derived table, as I explained. But when you do that, it's correlating per row from the parent. – Joel Coehoorn Dec 12 '22 at 16:12

0 Answers0