title: Oracle SQL - return multiple columns from subquery
body: <p>Let's take a simple query in Oracle:</p>
  
  <pre><code>SELECT
      CASE.ID,
      CASE.TYPE,
      CASE.DATE_RAISED
  FROM
      CASE
  WHERE
      CASE.DATE_RAISED &gt; '2019-01-01'
  </code></pre>
  
  <p>Now let's say another table, EVENT, contains multiple events which may be associated with each case (linked via EVENT.CASE_ID). OR not exist at all. I want to report on the earliest-dated future event per case - or if nothing exists, return NULL. I can do this with a subquery in the SELECT clause, as follows:</p>
  
  <pre><code>SELECT
      CASE.ID,
      CASE.TYPE,
      CASE.DATE_RAISED,
      (
      SELECT
          MIN(EVENT.DATE)
      FROM
          EVENT
      WHERE
          EVENT.CASE_ID = CASE.ID
          AND EVENT.DATE &gt;= CURRENT_DATE
      ) AS MIN_EVENT_DATE
  FROM
      CASE
  WHERE
      CASE.DATE_RAISED &gt; '2019-01-01'
  </code></pre>
  
  <p>This will return a table like this:</p>
  
  <pre><code>Case ID Case Type   Date Raised Min Event Date
  76      A           03/01/2019  10/05/2019
  43      B           02/02/2019  [NULL]
  89      A           29/01/2019  08/07/2019
  90      A           04/03/2019  [NULL]
  102     C           15/04/2019  20/05/2019
  </code></pre>
  
  <p>Note that if there do not exist any Events which match the criteria, the line is still returned but without a value. This is because the subquery is in the SELECT clause. This works just fine.</p>
  
  <p>My problem, however, is if I want to return more than one column from the EVENT table - while still at the same time preserving the possibility that there are no matching rows from the EVENT table. The above code only returns EVENT.DATE as the single subquery result, to ONE column of the main query. But what if I also want to return EVENT.ID, or EVENT.TYPE? While still allowing for them to be NULL (if no matching records from CASE are found)?</p>
  
  <p>I suppose I could use multiple subqueries in the SELECT clause: each returning just ONE column. But this seems horribly inefficient, given that each subquery would be based on the same criteria (the minimum-dated EVENT whose CASE ID matches that of the main query; or NULL if no such events found).</p>
  
  <p>I suspect some nifty joins would be the answer - although I'm struggling to understand which ones exactly.</p>
  
  <p>Please note that the above examples are vastly simplified versions of my actual code, which already contains multiple joins in the "old style" Oracle format, eg:</p>
  
  <pre><code>WHERE
      CASE.ID(+) = EVENT.CASE_ID
  </code></pre>
  
  <p>There are reasons why this is so - therefore a request to anyone answering this, please would you demonstrate any solutions in this style of coding, as my SQL isn't far enough advanced to be able to re-factor the "newer" style joins into existing code.</p>