Oracle Cursor expression vs standard join

Question

I am trying to understand what is so Special about cursor expressions, when effectively you could do this with a straight sub query?

https://docs.oracle.com/cd/B19306_01/server.102/b14200/expressions005.htm

Answer 1

A CURSOR is effectively a pointer to an area of memory on the database server so, instead of passing a result set containing all the data contained in a collection (nested table) or VARRAY or of performing a JOIN and returning multiple rows, you can return a single pointer and then the client application has the option of retrieving that cursor or not as required.

SELECT id,
       name,
       CURSOR( SELECT child FROM child_table c WHERE p.id = c.parent_id )
         AS children
FROM   parent_table p

Compared to:

SELECT id,
       name,
       ( SELECT CAST( COLLECT( child ) AS some_collection_type )
         FROM   child_table c
         WHERE  p.id = c.parent_id
       ) AS children
FROM   parent_table p

Or:

SELECT id,
       name,
       child
FROM   parent_table p
       LEFT OUTER JOIN child_table c
       ON (p.id = c.parent_id)

With the CURSOR expression, you can are initially passed the pointers (CURSOR) and not the data and can chose to retrieve those cursor(s) you are interested and do not need to retrieve the values for the uninteresting rows; with the other options, you have to retrieve all the child data with the rest of the row.