Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

1 Answer

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
      )       
share|improve this answer
add comment

Your Answer

 
discard

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.