Is it possible to get multiple values from a subquery?

Question

Is there any way to have a subquery return multiple columns in oracle db? (I know this specific sql will result in an error, but it sums up what I want pretty well)

select     a.x,     ( select b.y, b.z from b where b.v = a.v), from a 

I want a result like this:

a.x | b.y | b.z --------------- 1   | 2   | 3 

I know it is possible to solve this problem through joins, but that is not what I am asking for.

My Question is simply if there is any way, to get two or more values out of a subquery? Maybe some workaround using dual? So that there is NO actual join, but a new subquery for each row?

EDIT: This is a principle question. You can solve all these problems using join, I know. You do not need subqueries like this at all (not even for one column). But they are there. So can I use them in that way or is it simply impossible?

Answer 1

A Subquery in the Select clause, as in your case, is also known as a Scalar Subquery, which means that it's a form of expression. Meaning that it can only return one value.

I'm afraid you can't return multiple columns from a single Scalar Subquery, no.

Here's more about Oracle Scalar Subqueries:

http://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions010.htm#i1033549

Answer 2

It's incorrect, but you can try this instead:

select     a.x,     ( select b.y from b where b.v = a.v) as by,     ( select b.z from b where b.v = a.v) as bz from a 

you can also use subquery in join

 select         a.x,         b.y,         b.z     from a     left join (select y,z from b where ... ) b on b.v = a.v 

or

   select         a.x,         b.y,         b.z     from a     left join b on b.v = a.v