title: Is FULL OUTER JOIN really such a bad thing here?
body: <p>In general, I believe "FULL OUTER JOIN Considered Harmful", to turn the phrase.</p>
  
  <p>Background:</p>
  
  <p><a href="http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx" rel="nofollow noreferrer">http://weblogs.sqlteam.com/jeffs/archive/2007/04/19/Full-Outer-Joins.aspx</a></p>
  
  <p>But I do have a specific situation where it would be <em>really handy</em>:</p>
  
  <p>Given:</p>
  
  <pre><code>CREATE VIEW Calcs(c1, c2, c3, fordate, ...other columns) AS
     /* Complicated set of equations, etc. */
  </code></pre>
  
  <p>And:</p>
  
  <pre><code>CREATE TABLE Overrides(c1, c2, c3, fordate)
  </code></pre>
  
  <p>I need to <em>adjust the View</em> above to follow this logic:</p>
  
  <ol>
  <li>For any <strong>Calcs</strong> rows whose calculated date has no corresponding Override, select the calculated values.</li>
  <li>For any <strong>Calcs</strong> rows whose calculated date <em>matches</em> an Override date, select the override values.</li>
  <li>For any <strong>Override</strong> rows with no corresponding row in <strong>Calcs</strong>, select the override values.</li>
  </ol>
  
  <p>Now, usually I would just do a three part query:</p>
  
  <pre><code>CREATE VIEW Calcs AS ... (current logic)
  
  CREATE VIEW CalcsWithOverrides AS
  
     SELECT * FROM Calcs WHERE NOT EXISTS (match in Overrides)
  
     UNION ALL
  
     SELECT override.c1, override.c2, override.c3, (other non-overridden columns)
         FROM Calcs INNER JOIN Overrides
  
     UNION ALL
  
     SELECT *, (null placeholders for non-overridden columns) FROM Overrides WHERE
         NOT EXISTS (match in Calcs)
  </code></pre>
  
  <p>And yet, that seems a lot less straightforward than using an OUTER JOIN:</p>
  
  <pre><code>   SELECT
         COALESCE(Calcs.fordate, Overrides.fordate) AS fordate,
         -- Note that I am not using COALESCE() below on purpose: a null override should still override any calculated value
         CASE WHEN Overrides.fordate IS NULL THEN Calcs.c1 ELSE Overrides.c1 END AS C1,
         CASE WHEN Overrides.fordate IS NULL THEN Calcs.c2 ELSE Overrides.c2 END AS C2,
         CASE WHEN Overrides.fordate IS NULL THEN Calcs.c3 ELSE Overrides.c3 END AS C3,
         (....other columns from calcs that will be null for override-only rows...)
     FROM
         Calcs
         FULL OUTER JOIN Overrides ON Overrides.fordate = Calcs.fordate
  </code></pre>
  
  <p>So, is this a situation where an OUTER JOIN is justified, or is there a <em>better</em> alternative than the one above?</p>