How to order alphabetically in SQL with two columns, but where either column may be empty-CodePudding

I'm using SQL Server, and I have a table with two columns, both varchar, column A and column B. I need to produce a list in alphabetical order, however only one of these columns will ever have a value in it (ie, if column A has a value, then column B will be NULL and vice versa).

How can I write an ORDER BY clause in my T-SQL query to produce a list that checks both columns to see which one has the value present, then order the rows alphabetically?

CodePudding user response：

Use COALESCE which takes the first non-null argument

order by coalesce(columnA, columnB) asc

CodePudding user response：

There are some standard options to do this. What you choose is mostly personal "taste". The most "explicit" way is using CASE WHEN:

ORDER BY CASE WHEN columnA IS NULL THEN columnB ELSE columnA END;

By explicit, I mean you clearly understand it without knowing about specific functions that check this. The standard function to do this which works on every DB is COALESCE:

ORDER BY COALESCE(columnA,columnB);

This has the advantage it's much shorter, especially when you have more columns that should replace each other when null. SQL Server DB furthermore provides the function ISNULL that expects exact two arguments:

ORDER BY ISNULL(columnA,columnB);

The advantage of this is the name tells a bit more than "COALESCE", also it is faster than the other two options according to some performance articles and tests. The disadvantage is this function will not work on other DB's. Overall, as I said, it's mainly kind of personal taste which option you should take.