Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

share|improve this question

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

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

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

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

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

    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))


                       THEN colA
                     ELSE colB1 + colB2

but you have one more option:

WHERE (  colB = colA
      OR colA IS NULL  AND  colB = colB1 + colB2
share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.