I was just reading about scalar queries, and I have some questions:

Can a scalar query return more than one column, albeit in only one record?

Must a scalar query return one record, or can it potentially return 0? (i.e. A join returns nothing.)

Where else, apart from scalar subqueries, is the distinction between a scalar query and 'regular' query (what's the opposite of a scalar query) necessary or useful?

Thanks.

I've personally always assumed Scalar queries were ones that returned a SELECT statement with a { MAX, COUNT, MIN, SUM, etc... }. That way a result is always returned.

I don't know how universal the term is, but in Oracle a scalar [sub]query returns 0 or 1 rows and 1 column, resulting in a null or a scalar value. It is used in the SELECT part of another query like this:

select dept.name, (select count(*) from emp where emp.deptno = dept.deptno) as num_emps
from dept;

the term is quite universal (how's that for a definitive response, eh)

i've only ever seen it used for scalar subqueries, though, never for scalar queries

I always thought a scalar function could be used in a query and did something to each row like a substring or a math function like cosine. The functions that operated on a group of rows were referred to as column functions like count or average. I don't know if the terminoligy applies similarly when talking about queries.