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