>Thank you, Svein. I think I will implement the "Default" solution since my >application >allows the end user to create ad-hoc queries (of sorts). I think setting the >default will >produce fewer questions about why records are omitted. You are correct in >assuming that <unknown> is the same as empty. > >Would there be a problem in using Coalesce(...) in addition to setting the >default >values? I can't think of any, other than it may be overkill.
Well, using SELECT Coalesce(FirstName, '') FROM WorldTelephoneDirectory WHERE Coalesce(FirstName, 'Homer') = 'Homer' would do a natural scan, whereas SELECT Coalesce(FirstName, '') FROM WorldTelephoneDirectory WHERE FirstName = 'Homer' or FirstName is NULL might use an index. Other than that, the only thing I can think of would be to name your column, e.g. SELECT Coalesce(FirstName, '') as FirstName So, no real pitfalls, COALESCE is quite safe and straight forward to use, I even use it in JOIN clauses sometimes like FROM MyMainTable MMT LEFT JOIN MyFirstOptionalTable MFOT ... LEFT JOIN MySecondOptionalTable MSOT ... JOIN RequiredTable RT on RT.MyField = coalesce(MFOT.PrimaryKeyField, MSOT.PrimaryKeyField) HTH, Set
