title: Query to get all revisions of an object graph
body: <p>I'm implementing an audit log on a database, so everything has a CreatedAt and a RemovedAt column. Now I want to be able to list all revisions of an object graph but the best way I can think of for this is to use unions. I need to get every unique CreatedAt and RemovedAt id.</p>
  
  <p>If I'm getting a list of countries with provinces the union looks like this:</p>
  
  <pre><code>SELECT c.CreatedAt AS RevisionId from Countries as c where localId=@Country
  UNION
  SELECT p.CreatedAt AS RevisionId from Provinces as p 
  INNER JOIN Countries as c ON p.CountryId=c.LocalId AND c.LocalId = @Country
  UNION
  SELECT c.RemovedAt AS RevisionId from Countries as c where localId=@Country
  UNION
  SELECT p.RemovedAt AS RevisionId from Provinces as p 
  INNER JOIN Countries as c ON p.CountryId=c.LocalId AND c.LocalId = @Country
  </code></pre>
  
  <p>For more complicated queries this could get quite complicated and possibly perform very poorly so I wanted to see if anyone could think of a better approach. This is in MSSQL Server.</p>
  
  <p>I need them all in a single list because this is being used in a from clause and the real data comes from joining on this.</p>