title: SQL coalesce(): what type does the combined column have?
body: <p>Lets say I use <code>coalesce()</code> to combine two columns into one in select and subsequently a view constructed around such select.</p>
  
  <p>Tables:</p>
  
  <pre><code>values_int
  id INTEGER(11) PRIMARY KEY
  value INTEGER(11)
  
  values_varchar
  id INTEGER(11) PRIMARY KEY
  value VARCHAR(255)
  
  vals
  id INTEGER(11) PRIMARY KEY
  value INTEGER(11) //foreign key to both values_int and values_varchar
  </code></pre>
  
  <p>The primary keys between <code>values_int</code> and <code>values_varchar</code> are unique and that allows me to do:</p>
  
  <pre><code>SELECT vals.id, coalesce(values_int.value, values_varchar.value) AS value
  FROM vals
  JOIN values_int ON values_int.id = vals.value
  JOIN values_varchar ON values_varchar.id = vals.value
  </code></pre>
  
  <p>This produces nice assembled view with ID column and combined value column that contains actual values from two other tables combined into single column.</p>
  
  <ul>
  <li>What type does this combined column have?</li>
  <li>When turned into view and then queried with a WHERE clause using this combined "value" column, how is that actually handled type-wise? I.e. <code>WHERE value &gt; 10</code></li>
  </ul>
  
  <p>Som rambling thoughts in the need (most likely wrong):
  The reason I am asking this is that the alternative to this design have all three tables merged into one with INT values in one column and VARCHAR in the other. That would of course produce a lots of NULL values in both columns but saved me the JOINs. For some reason I do not like that solution because it would require additional type checking to choose the right column and deal with the NULL values but maybe this presented design would require the same too (if the resulting column is actually VARCHAR). I would hope that it actually passes the WHERE clause down the view to the source (so that the column does NOT have a type per se) but I am likely wrong about that.</p>