How to check for null in column? and use other column instead?

Question

How would I write a query that involves the following check:

if colA is null then colB = colB1 + colB2

if colA is not null then colB = colA

Answer 1

According to SQL Standards (works with multiple flavors of SQL), you can use the COALESCE() function:

SELECT 
    colB = COALESCE (colA, (colB1 + colB2))
FROM theTable;

or a CASE expression (which is not so elegant but can be used for more complex conditions):

SELECT 
    colB = CASE WHEN colA IS NOT NULL 
                  THEN colA
                ELSE colB1 + colB2
           END
FROM theTable;

Using T-SQL (works with Sybase and Microsoft SQL Server)

SELECT 
    colB = ISNULL (colA, (colB1 + colB2))
FROM theTable;

However, you've not told us about any guarantees that either colB1 or colB2 will always have a value. If either of those are NULL then everything will be NULL. If that's the case then you can chain along conditions in COALESCE which would look like

colB = COALESCE(colA, (colB1 + colB2),0)

or using ISNULL

colB = ISNULL(ISNULL(colA, (colB1 + colB2)),0)

---

If you don't want to use it in the result but in a WHERE or HAVING condition, no change is needed. Either:

WHERE    colB = COALESCE (colA, (colB1 + colB2))

or:

WHERE    colB = CASE WHEN colA IS NOT NULL 
                       THEN colA
                     ELSE colB1 + colB2
                END

but you have one more option:

WHERE (  colB = colA
      OR colA IS NULL  AND  colB = colB1 + colB2
      )