title: SQL merging result sets on a unique column value
body: <p>I have 2 similar queries which both work on the same table, and I essentially want to combine their results such that the second query supplies default values for what the first query doesn't return. I've simplified the problem as much as possible here. I'm using Oracle btw.</p>
  
  <p>The table has account information in it for a number of accounts, and there are multiple entries for each account with a commit_date to tell when the account information was inserted. I need get the account info which was current for a certain date.</p>
  
  <p>The queries take a list of account ids and a date.</p>
  
  <p>Here is the query:</p>
  
  <pre><code>-- Select the row which was current for the accounts for the given date. (won't return anything for an account which didn't exist for the given date)
  SELECT actr.*
  FROM Account_Information actr 
  WHERE actr.account_id in (30000316, 30000350, 30000351) 
  AND actr.commit_date &lt;= to_date( '2010-DEC-30','YYYY-MON-DD ')
  AND actr.commit_date = 
  (
      SELECT MAX(actrInner.commit_date) 
      FROM Account_Information actrInner 
      WHERE actrInner.account_id = actr.account_id
      AND actrInner.commit_date &lt;= to_date( '2010-DEC-30','YYYY-MON-DD ')
  ) 
  </code></pre>
  
  <p>This looks a little ugly, but it returns a single row for each account which was current for the given date. The problem is that it doesn't return anything if the account didn't exist until after the given date.</p>
  
  <p>Selecting the earliest account info for each account is trival - I don't need to supply a date for this one:</p>
  
  <pre><code>-- Select the earliest row for the accounts.
  SELECT actr.*
  FROM Account_Information actr 
  WHERE actr.account_id in (30000316, 30000350, 30000351) 
  AND actr.commit_date = 
  (
      SELECT MAX(actrInner .commit_date) 
      FROM Account_Information actrInner 
      WHERE actrInner .account_id = actr.account_id
  )  
  </code></pre>
  
  <p>But I want to merge the result sets in such a way that: </p>
  
  <p>For each account, if there is account info for it in the first result set - use that.
  Otherwise, use the account info from the second result set.</p>
  
  <p>I've researched all of the joins I can use without success. Unions almost do it but they will only merge for unique rows. I want to merge based on the account id in each row.</p>
  
  <p><a href="https://stackoverflow.com/questions/6803652/sql-merging-two-result-sets">Sql Merging two result sets</a> - my case is obviously more complicated than that</p>
  
  <p><a href="https://stackoverflow.com/questions/10485971/sql-to-return-a-merged-set-of-results">SQL to return a merged set of results</a> - I might be able to adapt that technique? I'm a programmer being forced to write SQL and I can't quite follow that example well enough to see how I could modify it for what I need.</p>