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

User · Answer

Can t you use JOIN like this one   SELECT a x   b y  b z  FROM a  LEFT OUTER JOIN b ON b v   a v    I don t know Oracle Syntax  So I wrote SQL syntax

User · Answer

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

User · Answer

Here are two methods to get more than 1 column in a scalar subquery  or inline subquery   and querying the lookup table only once  This is a bit convoluted but can be the very efficient in some special cases    You can use concatenation to get several columns at once   SELECT x          regexp substr yz           1  1  y         regexp substr yz           1  2  z   FROM  SELECT a x                  SELECT b y           b z yz                   FROM b                  WHERE b v   a v                    yz           FROM a    You would need to make sure that no column in the list contain the separator character  You could also use SQL objects   CREATE OR REPLACE TYPE b obj AS OBJECT  y number  z number    SELECT x          v yz y y         v yz z z   FROM  SELECT a x                  SELECT b obj y  z  yz                   FROM b                  WHERE b v   a v                    yz           FROM a  v

User · Answer

In Oracle query   select a x               select b y           b z                 from   b                 where  b v   a v                 and    rownum   1  as multple columns from   a   can be transformed to   select a x  b1 y  b1 z from   a  b b1 where  b1 rowid            select b rowid        from   b        where  b v   a v        and    rownum   1     Is useful when we want to prevent duplication for  table A  Similarly  we can increase the number of tables        where  b1 rowid c1 rowid     select b rowid c rowid

User · Answer

View this web  http   www w3resource com sql subqueries multiplee-row-column-subqueries php  Use example  select ord num  agent code  ord date  ord amount   from orders   where agent code  ord amount  IN    SELECT agent code  MIN ord amount    FROM orders    GROUP BY agent code

User · Answer

you can use cross apply   select     a x      bb y      bb z from     a     cross apply         select b y  b z         from b         where b v   a v       bb   If there will be no row from b to mach row from a then cross apply wont return row  If you need such a rows then use outer apply  If you need to find only one specific row for each of row from a  try       cross apply         select top 1 b y  b z         from b         where b v   a v         order by b order       bb

User · Answer

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

[sql] Is it possible to get multiple values from a subquery?

Examples related to sql

Examples related to oracle