John Robertson John Robertson - 2 months ago 5
MySQL Question

The claim that subqueries can only return single columns

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).

Answer

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.

Comments