Conditional Statements in SQL - CASE, COALESCE, CAST

In this article you will get the knowledge about different conditional statements which are appearing in SQL language.

You will know how to execute SQL query only when some conditions are met.

• CASE statement

CASE statement is just typical IF/ELSE statement, which you can find in other programming languages.

CASE statement can be used as:
- general CASE statement,
- CASE expression

• Regular CASE STATEMENT

Let's first talk about general CASE statement:

SELECT place,
  CASE WHEN place = '1' THEN 'first'
  WHEN place = '2' THEN 'second'
  WHEN place = '3' THEN 'thirt'
  ELSE 'other'
END
FROM contestants;

The code above will take contestants table and check if column place is '1', '2' or '3'.
If it is, then it will exchange this value into 'first', 'second' or third.
If column place will have different value, it will return 'other' instead.

• CASE expression

The same query we can write as a CASE expression:

SELECT place,
  CASE place WHEN '1' THEN 'first'
  WHEN '2' THEN 'second'
  WHEN '3' THEN 'third'
  ELSE 'other'
END
FROM contestants;

As you can see, in case expression you don't have to use 'place = ' when comparing values. Expression will be good when you
just check if something is equal something. 

Regular CASE statement will be better when you want to do something more complex like for example check if amount is bigger than 100.

• COALESCE function

COALESCE function is very useful when we are working with null values inside our table.
It returns first non-null value in a list.

So, if we have amounts table like this:

Thanks to the COALESCE function we can replace null into 0 in this way:

SELECT COALESCE(amount, 0) FROM amounts

If the value is not null, for example 100 - it will just return 100, but if it is null, COALESCE function will get to the second option from list,
and return 0 in this case.

• CAST operator

CAST operator will simply convert a data you get from one type to another.

There are two ways to use it in SQL:

# let's say, amount is f.e. '5'

# typical SQL use
SELECT CAST(amount AS INTEGER) FROM amounts

# PostgreSQL version
SELECT amount::INTEGER FROM amounts