Does oracle support nested result sets?

Forgive me if this doesn’t make sense. I haven’t done SQL in years and years. Getting my feet wet again so please correct me if my assumptions here are wrong.

From what I recall, SQL Server (or perhaps it was an ADO.NET thing) had a neat feature where you could nest a subset of ‘child’ rows in a parent-child relation along with their corresponding parent row, as a nested result set for that parent row.

For instance, if you had the following three tables…

• Orders (ID, Name)
• Products (ID, Name)
• OrderDetails (Order_ID, Product_ID)

So as an example, say you had ten orders, each with say five items. A standard join would return fifty rows.

What I’m referring to is a feature that would give me back ten rows–one per order–then as you were cursoring through those results, you would ask for the nested results for that row (i.e. essentially a second cursor over just the products in that order.)

Does Oracle have any such feature/capability?

Answer

You can use CURSOR expressions:

SELECT id AS orderid,
       CURSOR(
         SELECT p.id,
                p.name
         FROM   OrderedProducts op
                INNER JOIN Products p
                ON ( op.products_id = p.id )
         WHERE  op.orders_id = o.id
       )
FROM   orders o

Cursor expressions are not supported by many interfaces but you should be able to make it work using Java through JDBC and maybe some others.

---

Another alternative is to use Collections and Object types:

CREATE TYPE product_type AS OBJECT(
  id   NUMBER,
  name VARCHAR2(200)
);
/

CREATE TYPE product_table AS TABLE OF product_type;
/

Then:

SELECT id AS Order_Id,
       CAST(
         MULTISET (
           SELECT  product_type( P.id, P.name )
           FROM    OrderDetails OD
           JOIN    Products P
           ON      OD.Product_Id = P.Id
           WHERE   OD.Order_Id   = O.Id
         )
         AS product_table
       ) AS products
FROM   Orders O;

db<>fiddle (db<>fiddle successfully runs the query; although it doesn’t know how to display the collection in final result set so it doesn’t show any rows)

---

Or you could use XML:

SELECT id AS order_id,
       ( SELECT XMLELEMENT(
                  "Products",
                  XMLAGG(
                    XMLElement(
                      "Product",
                      XMLFOREST(
                        p.id AS "ProductID",
                        p.name AS "ProductName"
                      )
                    )
                  )
                )
         FROM   OrderDetails OD
         JOIN   Products P
         ON     OD.Product_Id = P.Id
         WHERE  OD.Order_Id   = O.Id
       ) AS products
FROM   Orders o

Which outputs:

ORDER_ID | PRODUCTS                                                                                                                                                                                                                                                      
-------: | :-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
       1 | <Products><Product><ProductID>202</ProductID><ProductName>car</ProductName></Product></Products>                                                                                                                                                              
       2 | <Products><Product><ProductID>201</ProductID><ProductName>orange</ProductName></Product><Product><ProductID>202</ProductID><ProductName>car</ProductName></Product><Product><ProductID>203</ProductID><ProductName>airplane</ProductName></Product></Products>

Or JSON:

SELECT id AS order_id,
       ( SELECT JSON_ARRAYAGG(
                  JSON_OBJECT(
                    'id' VALUE p.id,
                    'name' VALUE p.name
                  )
                )
         FROM   OrderDetails OD
         JOIN   Products P
         ON     OD.Product_Id = P.Id
         WHERE  OD.Order_Id   = O.Id
       ) AS products
FROM   Orders o

Which outputs:

ORDER_ID | PRODUCTS                                                                         
-------: | :--------------------------------------------------------------------------------
       1 | [{"id":202,"name":"car"}]                                                        
       2 | [{"id":201,"name":"orange"},{"id":202,"name":"car"},{"id":203,"name":"airplane"}]

db<>fiddle here