title: SQL: Is UNION the same as SELECT DISTINCT with UNION ALL subquery
body: <p>Are these two SQL statements identical in all scenarios?</p>
  
  <pre><code>SELECT DISTINCT * FROM 
  (SELECT * FROM tablename UNION ALL SELECT * FROM tablename2) dummyname
  </code></pre>
  
  <p>and</p>
  
  <pre><code>SELECT * FROM tablename UNION SELECT * FROM tablename2
  </code></pre>
  
  <p>The thing I always find peculiar about UNION is that it removes all non-distinct rows even if those non-distinct rows appear within a single part of the UNION clause. For example, lets say tablename has one column: amt INT. Let's say amt INT is NOT a primary key and does not have any unique constraint on it. Then let's say I have two rows where amt = 20. Regardless of the UNION that I do, only one of those two rows will appear in the resulting set.</p>
  
  <p>I was explaining this behavior to someone else, and then came up with above examples to demonstrate the behavior of UNION... I believe those two statements above are the same (always), but I just want to make sure that what I said is correct. Are there any edge cases where the two SQL statements above will not generate a totally identical result set?</p>