The claim that subqueries can only return single columns

Question

I am going through Ben Fortas "Teach yourself SQL in 10 minutes" book and it has grey box warning: "Subquery SELECT statements can only retrieve a single column. Attempting to return multiple columns will return an error."

Is this, in fact, commonly true for an RDMS? (Note that if this answer is correct then it is not true for all databases).

And why in the world would it ever be true? It seems like such a weird language restriction. Queries are expensive to compute, and the work to retrieve 3 columns is not particularly computationally different than the work to retrieve 1 (unless your RDMS stores your tables grouped by columns instead of grouped by rows).

It is generally not true *except* when using subquery results in SELECT fields or non-set comparisons (such as `=`, `<`, etc...). — Uueerdo, Sep 21 '16 at 16:35
From your description it seems he is talking about a sub query in a Column definition NOT a subselect/query as a derived table in the FROM statement as in the link you posted. A Column can't hold multiple columns. And a Column cannot hold multiple rows so it has to be a single scalar result when used in column definition. Similar restriction is true when used in a where or order by clause unless using IN — Matt, Sep 21 '16 at 16:35
I don't own that book and I have no idea of the context. If you do something like `WHERE foo IN (SELECT ...)` then it's obvious why. Otherwise, that restriction does not exist. — Álvaro González, Sep 21 '16 at 16:36
@ÁlvaroGonzález actually, in a similar situation, multiple columns are allowed; `WHERE (foo1, foo2) IN (SELECT A, B ....)` _...though I am not sure if that is only a MySQL thing, I haven't tried it in MSSQL yet._ — Uueerdo, Sep 21 '16 at 16:37
It's not true. There are lots of subqueries that return multiple columns. It depends on the context. — Missy, Sep 21 '16 at 16:39
@Missy sorry my intention was to get you to expand upon your answer not necessarily delete it, by all means answer just add to it to accommodate the "depends" portion. — Matt, Sep 21 '16 at 16:40
The context can be checked on google books: https://books.google.co.uk/books?id=IkBxyCMqwI8C&pg=PA107&lpg=PA107&dq=ben+fortas+Subquery+SELECT+statements+can+only+retrieve+a+single+column.+Attempting+to+return+multiple+columns+will+return+an+error&source=bl&ots=99Pu7x-QRv&sig=7OUZ3xFrPb2mU3SwUCJJx21gdN8&hl=en&sa=X&ved=0ahUKEwj8tMXh9aDPAhXESRoKHc-NDA8Q6AEIHjAA#v=onepage&q=ben%20fortas%20Subquery%20SELECT%20statements%20can%20only%20retrieve%20a%20single%20column.%20Attempting%20to%20return%20multiple%20columns%20will%20return%20an%20error&f=false — Shadow, Sep 21 '16 at 17:00
It describes a standard `in (...)` structure, so the general statement is obviously wrong. It is true for that particular sql statement though. It is misleading. — Shadow, Sep 21 '16 at 17:01
@Shadow Thanks for the link to the book but its apparently not publicly available. — Álvaro González, Sep 21 '16 at 18:55

score 5 · Accepted Answer · answered Sep 21 '16 at 16:41

5

In the answer that you link to, I would classify that as an "inline view" or "inline query", rather than a subquery.

That raises the question of what exactly a subquery is, of course.

Here's an example where you can indeed only return a single column.

select (select name from table where id = main_query.id),
       id
from   table main_query

Here's an example where you can return multiple columns. This seems to be to be unequivocally a subquery, of the "correlated" type.

select id
from   table main_query
where  (col1, col2) in (select a,b
                        from   c
                        where  c.x = main_query.y);

Here's an example where it doesn't matter how many columns are returned, and in fact any values are ignored:

select id
from   table main_query
where  exists (select a,b
               from   c
               where  c.x = main_query.y);

I think on balance I'd say that it is not true, but it depends on what your definition of a subquery is.

answered Sep 21 '16 at 16:41

David Aldridge

51,479
8
68
96

2

might want to add derived table example for multiple columns. Also EXISTS that's kind of true but the reality is EXISTS ignores the column definitions so it sees 5 columns, *, SELECT 1 all as the same thing – Matt Sep 21 '16 at 16:43
In the 1st example the subquery must return 1 column **and** 1 record (a single value). I'm not really aware of any general restrictions on the number of columns only. – Shadow Sep 21 '16 at 16:51
And don't forget that `SELECT * FROM (SELECT * FROM foo) bar` is a subquery as well. – Álvaro González Sep 21 '16 at 18:56
@Matt yes -- I usually SELECT NULL just to support the semantics that the selected values are irrelevant. It's a funny SQL quirk, really. – David Aldridge Sep 21 '16 at 19:38
@Shadow - yes, one value only, as you say. – David Aldridge Sep 21 '16 at 19:38
@ÁlvaroGonzález That's what I'd think of as an inline view -- maybe it's just an Oracle term though. – David Aldridge Sep 21 '16 at 19:39

score 5 · Answer 2 · answered Sep 21 '16 at 16:41

Scalar subqueries can return only one column. These are subqueries that are used wherever a single value is expected. This can be in almost any clause. For instance, as this non-sensical query uses them:

select (select count(*) from information_schema.tables), table_name
from information_schema.columns
where table_name = (select table_name from information_schema.columns order by rand() limit 1);

However, many subqueries are not scalar subqueries. These include:

Subqueries in the FROM clause.
Subqueries for a EXISTS and NOT EXISTS.
Subqueries that operator on a tuple when used in a comparison such as = and IN.

The claim that subqueries can only return single columns

2 Answers2