SQL coalesce(): what type does the combined column have?

Question

Lets say I use coalesce() to combine two columns into one in select and subsequently a view constructed around such select.

Tables:

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

The primary keys between values_int and values_varchar are unique and that allows me to do:

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

This produces nice assembled view with ID column and combined value column that contains actual values from two other tables combined into single column.

• What type does this combined column have?
• 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. WHERE value > 10

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.

Answer 1

You query should be explicit to be clear, In this case mysql is using varchar.

I would write this query like this to be clear

coalesce(values_int.value,cast(values_varchar.value as integer), 0) 

or

coalesce(cast(values_int.value as varchar(20)),values_varchar.value,'0') 

you should put in that last value unless you want the column to be null if both columns are null.