title: Oracle FULL OUTER JOIN three tables with two conditions
body: <h2>Background</h2>
  
  <p>Oracle DB version:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT * FROM v$version
  WHERE banner LIKE 'Oracle%';
  -- OUTPUT
  Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
  </code></pre>
  
  <h2>Goal</h2>
  
  <p>I'm trying to outer join <strong>three tables</strong> with <strong>two conditions</strong> so that missing values simply appear as <code>NULL</code>. See the details below.</p>
  
  <h2>Tables</h2>
  
  <p>The following tables are abstractions, so please don't try to improve on the data model itself.</p>
  
  <h3>MEASUREMENT</h3>
  
  <p>Primary Key = <code>ID</code></p>
  
  <pre><code>|  ID  |    MEAS_NAME    |
  |------|-----------------|
  | 1000 | "Measurement 1" |
  </code></pre>
  
  <h3>MEASUREMENT_AREA</h3>
  
  <p>Primary Key = (<code>ID</code>, <code>NAME</code>)<br>
  Foreign Key <code>ID</code> = <code>MEASUREMENT.ID</code></p>
  
  <pre><code>|  ID  |    NAME   | AREA |
  |------|-----------|------|
  | 1000 | "Point 1" |   10 |
  | 1000 | "Point 2" |   20 |
  </code></pre>
  
  <h3>MEASUREMENT_VOLUME</h3>
  
  <p>Primary Key = (<code>ID</code>, <code>NAME</code>)<br>
  Foreign Key <code>ID</code> = <code>MEASUREMENT.ID</code></p>
  
  <pre><code>|  ID  |    NAME   | VOLUME |
  |------|-----------|--------|
  | 1000 | "Point 1" |    100 |
  | 1000 | "Point 3" |    200 |
  </code></pre>
  
  <h2>Expected Result</h2>
  
  <p>What I want is the following output:</p>
  
  <pre><code>|  ID  |    MEAS_NAME    |    NAME   | AREA | VOLUME |
  |------|-----------------|-----------|------|--------|
  | 1000 | "Measurement 1" | "Point 1" | 10   | 100    |
  | 1000 | "Measurement 1" | "Point 2" | 20   | NULL   |
  | 1000 | "Measurement 1" | "Point 3" | NULL | 200    |
  </code></pre>
  
  <p>This means, if for a specific <code>MEASUREMENT.ID</code> and a specific <code>NAME</code> there is both data in <code>AREA</code> and <code>VOLUME</code> then put them in the same line. Otherwise, just leave the <code>AREA</code> or <code>VOLUME</code> field empty.</p>
  
  <h3>Query 1</h3>
  
  <p>I came up with the following SQL statement, which <strong>doesn't work</strong>, it discards the results from <code>MEASUREMENT_VOLUME</code>:</p>
  
  <pre><code>SELECT meas.ID AS "ID",
      meas.MEAS_NAME AS "MEAS_NAME",
      COALESCE (area.NAME, vol.NAME) as "NAME",
      area.AREA, vol.VOLUME
  FROM MEASUREMENT meas
    LEFT JOIN MEASUREMENT_AREA area
      ON meas.ID = area.ID
    FULL JOIN MEASUREMENT_VOLUME vol
      ON meas.ID = vol.ID AND area.NAME = vol.NAME
  WHERE meas.ID = 1000;
  </code></pre>
  
  <h3>Query 2</h3>
  
  <p>If I put <code>MEASUREMENT</code> last it works but the query is <strong>very slow</strong>:</p>
  
  <pre><code>SELECT meas.ID AS "ID",
      meas.MEAS_NAME AS "MEAS_NAME",
      COALESCE (area.NAME, vol.NAME) as "NAME",
      area.AREA, vol.VOLUME
  FROM MEASUREMENT_AREA area
      FULL JOIN MEASUREMENT_VOLUME vol
          ON area.ID = vol.ID AND area.NAME = vol.NAME
      JOIN MEASUREMENT meas
          ON meas.ID = vol.ID OR meas.ID = area.ID
  WHERE meas.ID = 1000;
  </code></pre>
  
  <h2>Questions</h2>
  
  <ul>
  <li>Why does query 1 not work?</li>
  <li>Why does query 2 work?</li>
  <li>What's the most efficient way to achieve my output?</li>
  </ul>
  
  <p>Your help is much appreciated, I'm not an SQL expert.</p>
  
  <h2>Additional Information</h2>
  
  <ul>
  <li>One row in <code>MEASUREMENT</code> contains meta data for exactly one measurement</li>
  <li>One measurement can contain hundreds of measurement points, which are distinguished by their `NAME.</li>
  <li><code>MEASUREMENT_AREA</code> and <code>MEASUREMENT_VOLUME</code> are much bigger than <code>MEASUREMENT</code>, each of them contains more than 10 million rows</li>
  </ul>