Is it possible for a subquery to return two values?

Question

Is it possible for a subquery to return two values onto the outer query? Such as:

SELECT 1, 
       (SELECT COUNT(*), MAX(*) FROM test_table WHERE test=123) 
FROM another_table

Or is there a better way to do this?

Ferne

If Another_Table has 20 rows, you'd get 20 rows of output containing the same data, unless the column test is present only in Another_Table and not in Test_Table (in which case, it is a funny way of writing the restriction (which is a fairly extreme example of 'meiosis'). – Lulululuabourg

Answer 1

If you use the subquery in the FROM clause rather than the field list, then you can treat the output as a table and refer to the separate columns.

Copybook

I'm a bit confused by that. Can you explain a bit further, please? – Ferne

SELECT A.a, A.b FROM (SELECT a, b FROM MyTable) AS A; – Keilakeily

@kevin Your subquery, can go as an additional table in the FROM clause with an alias (eg. AS test_t, name the fields in the subquery too), and refer to the values in the field list: SELECT 1, test_t.count, test_t.max FROM (SELECT ... – Copybook

Only issue here is that without join criteria, this suggestion will produce a cartesian product. – Wiebmer

@OMG Ponies: Quite right, it needs restricting. If the queries are disjoint, then it might be better as two subqueries in the field list. OP only asked if it was "possible for a subquery to return two values onto the outer query" - answer, yes. Suitability was not accounted for. – Copybook

Answer 2

You are just selecting numbers as results so couldn't you just do:

SELECT 1, COUNT(*), MAX(*) FROM test_table WHERE test=123

Gravimetric

They're two different tables :\ – Ferne

Answer 3

Not possible

mysql> select 1, (select 2, 3) from dual;
ERROR 1241 (21000): Operand should contain 1 column(s)

If you are dealing with two tables and you what the results in one line, you should preform a join.

Condition

Answer 4

Hmm, it depends on what exactly you want to do with the data, you can join two tables using JOIN syntax, and one of the tables can actually be a subquery. I think that is probably what you want.

I'm not even user what your current query will do..

Documentation: http://dev.mysql.com/doc/refman/5.0/en/join.html

Photograph